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 … Continue reading Example shows how “DISABLE TABLE LOCK” works

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 … Continue reading Lists foreign key(s) on a table

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; … Continue reading Find locks on a 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 … Continue reading How to find average row length for a table?

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. Continue reading How to find sizes of all tables in SQLServer?