Example shows how “DISABLE TABLE LOCK” works

Following example shows what happens when locks are disabled on the TABLE.

-- disable lock on a table
SQL> alter table t disable table lock;
Table altered.

-- shows one can’t drop table as table locks are disable
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

Lists foreign key(s) on a table

Using the following anonymous store procedure one can list the foreign key on a table and it’s column. It also recursively traverses tables to see if there are other tables dependent on it’s primary key.

set serveroutput on format wrapped
DECLARE
l_vc2_table_owner VARCHAR2(30) := upper('&1');
l_vc2_table_name VARCHAR2(30) := upper('&2');

PROCEDURE get_ind_columns(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2, l_vc2_pad IN VARCHAR2
, l_vc2_out_table_key OUT VARCHAR2)
IS
CURSOR l_cur_ind_columns IS
SELECT column_name FROM dba_ind_columns
WHERE index_owner = l_vc2_owner AND index_name = l_vc2_index_name
ORDER BY column_position;
l_vc2_columns VARCHAR2(100);
BEGIN
FOR l_rec IN l_cur_ind_columns
LOOP
IF l_vc2_columns IS NULL THEN
l_vc2_columns := l_rec.column_name;
ELSE
l_vc2_columns := l_vc2_columns || ' ' || l_rec.column_name;
END IF;
END LOOP;
l_vc2_out_table_key := ' (' || l_vc2_columns || ')';
END;

PROCEDURE print_pk(l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2, l_vc2_pad IN VARCHAR2) IS
CURSOR l_cur_main IS
SELECT di.owner, di.index_name
FROM dba_indexes di, dba_constraints dc
WHERE di.table_owner = l_vc2_table_owner and di.table_name = l_vc2_table_name
AND dc.owner = di.owner and dc.constraint_name = di.index_name
AND di.uniqueness = 'UNIQUE';
l_rec_main l_cur_main%ROWTYPE;
l_vc2_ind_columns VARCHAR2(1000);

FUNCTION check_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2)
RETURN BOOLEAN
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
l_b_status BOOLEAN;
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
l_b_status := TRUE;
ELSE
l_b_status := FALSE;
END IF;
CLOSE l_cur_fk;
RETURN l_b_status;
END;

PROCEDURE print_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2,
l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2,
l_vc2_pad IN VARCHAR2)
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;

CURSOR l_cur_dcc(l_vc2_owner IN VARCHAR2, l_vc2_constraint_name IN VARCHAR2) IS
SELECT column_name FROM dba_cons_columns
WHERE owner = l_vc2_owner AND constraint_name = l_vc2_constraint_name
ORDER BY position;

l_vc2_cons_columns VARCHAR(500);
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
WHILE l_cur_fk%FOUND
LOOP
l_vc2_cons_columns := '';
FOR l_rec_dcc IN l_cur_dcc(l_rec.owner, l_rec.constraint_name)
LOOP
IF l_vc2_cons_columns IS NULL THEN
l_vc2_cons_columns := l_rec_dcc.column_name;
ELSE
l_vc2_cons_columns := l_vc2_cons_columns || ' ' || l_rec_dcc.column_name;
END IF;
END LOOP;
dbms_output.put_line(l_vc2_pad || ' Table FK: ' || l_rec.owner || '.' || l_rec.table_name
— || ' ' || l_rec.constraint_name
|| ' (' || l_vc2_cons_columns || ')');
print_pk(l_rec.owner, l_rec.table_name, l_vc2_pad || ' ');

FETCH l_cur_fk INTO l_rec;
END LOOP;
END IF;
CLOSE l_cur_fk;
END;

BEGIN
OPEN l_cur_main;
FETCH l_cur_main INTO l_rec_main;
IF l_cur_main%FOUND THEN
WHILE l_cur_main%FOUND
LOOP
IF check_fk(l_rec_main.owner, l_rec_main.index_name) THEN
l_vc2_ind_columns := '';
get_ind_columns(l_rec_main.owner, l_rec_main.index_name, l_vc2_pad, l_vc2_ind_columns);
dbms_output.put_line(l_vc2_pad || 'Table: ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' ' || l_vc2_ind_columns);
print_fk(l_rec_main.owner, l_rec_main.index_name, l_vc2_table_owner, l_vc2_table_name, l_vc2_pad);
END IF;
FETCH l_cur_main INTO l_rec_main;
END LOOP;
END IF;
END;
BEGIN
print_pk(l_vc2_table_owner, l_vc2_table_name, '');
END;
/

Output:
Table: SCOTT.TABLE1 (TABLE1_KEY)
Table FK: SCOTT.TABLE2 (TABLE2_FKEY)
Table: SCOTT.TABLE2 (TABLE2_KEY)
Table FK: SCOTT.TABLE3 (TABLE3_FK)

Find locks on a table

Using the step below one can find the locks on the table by looking for object id.

Example:
Window 1:
SQL> update scott.test1 set x = 200;

16 rows updated.

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
19

Window 2:
SQL> select object_id from dba_objects where object_name = ‘TEST1’ and owner = ‘SCOTT’;

OBJECT_ID
———-
13846

SQL> select * FROM V$lock where id1 = 13846;

ADDR KADDR SID TY ID1 ID2 LMODE
—————- —————- ———- — ———- ———- ———-
REQUEST CTIME BLOCK
———- ———- ———-
000000006934A5B0 000000006934A5D8 19 TM 13846 0 3
0 14775 0

Shows SID=19 has lock on the table.

How to find average row length for a table?

Using the following PL/SQL code one can find average size of a row in a table, the following code samples the first 100 rows. It expects 2 parameters table owner and table_name.

DECLARE
l_vc2_table_owner VARCHAR2(30) := '&table_owner';
l_vc2_table_name VARCHAR2(30) := '&table_name';
/* sample number of rows */
l_nu_sample_rows NUMBER := 100;

/* loop through columns in the table */
CURSOR l_cur_columns IS
SELECT column_name, data_type FROM dba_tab_columns
WHERE owner = l_vc2_table_owner
AND table_name = l_vc2_table_name;
l_rec_columns l_cur_columns%ROWTYPE;
l_vc2_sql VARCHAR2(10000);
l_avg_row_size NUMBER(10,2);
BEGIN
l_vc2_sql := '';
OPEN l_cur_columns;
FETCH l_cur_columns INTO l_rec_columns;
/* loop through columns */
WHILE l_cur_columns%FOUND
LOOP
/* if LOB datatype use dbms_log.get_length to find length */
IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
ELSE
l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
END IF;
FETCH l_cur_columns INTO l_rec_columns;
IF l_cur_columns%FOUND THEN
l_vc2_sql := l_vc2_sql || ' + ';
END IF;
END LOOP;
IF l_vc2_sql IS NOT NULL THEN
l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' WHERE rownum < ' || l_nu_sample_rows;
EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
ELSE
dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
END IF;
END;
/
Output
Enter value for table_name: TEST_OBJECTS
old 3: l_vc2_table_name VARCHAR2(30) := ‘&table_name’;
new 3: l_vc2_table_name VARCHAR2(30) := ‘TEST_OBJECTS’;
SCOTT.TEST_OBJECTS average row length: 76.88

PL/SQL procedure successfully completed.

How to find sizes of all tables in SQLServer?

By executing the store procedure sp_spaceused ‘<table>’ one can find the space used by a table. The store procedure returns amount of space used by data, index and unused space. Here is the documentation on sp_spaceused from Microsoft.

Using the following link you can use the store proc below to return for all tables in a database http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx.

How to view table of contents of a compress tar file?

Using the example below one can view the contents a compress tar file without un-compressing the file.
# if the file is compressed using compress
$ uncompress -c file.Z | tar -tvf –
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

$ uncompress < file.tar.Z | tar -tvf –
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

# if the file is compressed using gzip
$ cat file.tar.gz | gunzip | tar -tvf –
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

PCTFree

PCTFREE – tells what percentage of space is reserved in each block of updates, if the value is too low and if for example of there is VARCHAR2 datatype and update is replacing the column with larger value it will cause the row not to fit in the block causing row fragmentation which causes unnecessary I/O when the row is retrieved. So for tables that doesn’t change i.e. static data one can set PCTFREE to a very low value, and for tables in which only inserts occur one can set the value to be 0 to maximize the space in the block.

One can find the value of PCTFREE by running by querying user_tables or all_tables.
SELECT pct_free FROM user_tables WHERE table_name = ‘EMP’;

PCT_FREE
———
20

Information on flashback feature in 10g

One of the features in 10g is flashback which allows to restores tables that were dropped. Note: If an index is dropped but not a table it can’t be restored from flashback but if a table is dropped that has an index you can restore the table and the index from flashback.

The example below shows a case where table is dropped that has an index.
— create table
SQL> create table test ( x number(1) constraint test not null);

Table created.

— create index on the table test
SQL> create index test_idx on test(x);

Index created.

— show recyclebin it’s empty
SQL> show recyclebin;

SQL> column object_name format a30

— show objects
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST_IDX
TEST

— drop table
SQL> drop table test;

Table dropped.

— show recyclebin which only shows the table
SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$WLAh2JvbQDDgQ6wbCh1AMA==$0 TABLE 2008-10-07:13:58:17

— flashback the table
SQL> flashback table test to before drop;

Flashback complete.

— shows the object is restored and index too but it has name from the recycle-bin
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— show indexes it still has the name from the recyle-bin
SQL> select index_name from user_indexes;

INDEX_NAME
——————————
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— shows the index is linked to the table
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— restore the index name back
SQL> ALTER INDEX “BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0” RENAME TO test_idx;

Index altered.

— shows the updated index name
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST TEST_IDX

Here is second example showing index is not part of recyclebin when dropped.
SQL> create table test ( x number(1) constraint test not null);

Table created.

SQL> create index test_idx on test(x);

Index created.

SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————

TEST
TEST_IDX

SQL> show recyclebin;
SQL> select * FROM user_recyclebin;

no rows selected

SQL> drop index test_idx;

Index dropped.

SQL> select * FROM user_recyclebin;

no rows selected

SQL> show recyclebin;
SQL> select index_name from user_indexes;

no rows selected