Tag: table

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 -- … 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 … 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 ---------- … Continue reading Find locks on a table →

SQLServer create as (CTAS)

In Oracle there is CREATE TABLE...AS to backup table into a new table and the syntax on SQL Server is a bit different to copy table. So the syntax on SQL Server is SELECT * INTO newtable FROM sourcetable Additional documentation from Microsoft http://msdn.microsoft.com/en-us/library/ms190750.aspx

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

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 … Continue reading How to view table of contents of a compress tar file? →

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 … Continue reading PCTFree →

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 … Continue reading Information on flashback feature in 10g →

How to resolve user locking problems

Method 1: Check and remove session locking other from Database Find the USER locking others Select a.serial#, a.sid, a.username, b.id1, c.sql_text from v$session a, v$lock b, v$sqltext c where b.id1 in (select distinct e.id1 from v$session d, v$lock e where d.lockwait = e.kaddr) and a.sid = b.sid and c.hash_value = a.sql_hash_value and b.request = 0; … Continue reading How to resolve user locking problems →