Finding Foreign Key Constraints in Oracle

What a nice tutorial related constraints, just want to make a note :)

http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm

DDL_LOCK_TIMEOUT — new 11g DDL wait parameter

Hello lads,

need to do DDL but bunch of process accessing that objects, below parameter very much use full for such case

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

 here is the example, recreate seq which accessing by lot's of processes

SQL> ALTER SESSION SET ddl_lock_timeout=5;

Session altered.

Elapsed: 00:00:00.00
SQL> DROP SEQUENCE "USER"."SEQUENCE_NAME";

Sequence dropped.

Elapsed: 00:00:00.17
SQL> CREATE SEQUENCE  "USER"."SEQUENCE_NAME"  MINVALUE 1 MAXVALUE 999999999;

Sequence created.

Elapsed: 00:00:00.07

Enjoy sharing.. :)

Determine the Actual Size of the table

HI Guys,

If you have tables with a lot of DML especially delete and update, you need to consider to reorge that table as for sure the performance will be degradated, below is the command to determine the actual table size, if the difference between fragmented and actual size more than 10 persen ( overhead, ex : header, table directory in the block) then better to rebuild tables and its index also


note : we have to gather the statistic first
exec dbms_stats.gather_table_stats('SYSTEM','T');


** ACTUAL DATA SIZE **
select round((num_rows * avg_row_len /(1024)),2)||'KB' "size"
from dba_tables
where table_name = 'FRAGMENTED_TABLE';


** TABLE SIZE WITH FRAGMENTATION **
select table_name,round((blocks*db_block_size),2)||' KB' "size"
from dba_tables
where table_name = 'FRAGMENTED_TABLE';



#####   merge it into one single query ######

select table_name, round((blocks*8),2) tblsz_frgmnted_KB, round((num_rows*avg_row_len/1024),2) actualsize_KB from dba_tables
where table_name='FRAGMENTED_TABLE';

Enjoy :)

How to reset sequence

Hello folks, long time no see.. :)

simple thing but very important, how to reset sequence

testcase is to reset to 1

1. Check current sequnce detail
select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER, CYCLE_FLAG from user_sequences  where SEQUENCE_NAME='SEQ_NAME';

SEQUENCE_NAME   MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER 
-------------- ---------- ---------- ------------ ----------- 
SEQ_NAME           1       999999999       1      330120        


2. Alter sequence increment by doing some minus here
33012-1= 330119
alter sequence SEQ_NAME increment by -330119 ;

3. Select next value from sequence
select SEQ_NAME.nextval next from dual;

4. Alter sequence increment to 1 again
alter sequence SEQ_NAME increment by 1;

5. let's verify
select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER, CYCLE_FLAG from user_sequences  where SEQUENCE_NAME='SEQ_NAME';
SEQUENCE_NAME   MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
-------------- ---------- ---------- ------------ -----------
SEQ_NAME           1       999999999       1      1


Olrait !! done