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.

Advertisements

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.