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 —…

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…

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 ———-…

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…

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…