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
July 09, 2015
July 05, 2015
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.. :)
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 :)
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
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
January 04, 2015
Fragmentation on tables
Hi guys, below is test case to determine if we have some fragmented object
## Step 1 ##
i'm creating the table and the index also (later for deleting purpose)
===============================================
SQL> select /*+ full(a) parallel(a,8) */ count(*) from TESTAB a;
COUNT(*)
----------
5247350
SQL> create unique index TESTAB_pk on TESTAB(ACCOUNT_ID);
Index created.
below is the estimate space before we gather the stat
=====================================================
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
2 from dba_tables where table_name='TESTAB'
3 and owner in ('TESUSER') ;
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------- ----------------- -------------
TESTAB
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2 from dba_segments
3 where segment_name='TESTAB'
4 and owner in ('TESUSER') group by owner, segment_name;
OWNER SEGMENT_NAME MB
------------- -------------------- ----------
TESUSER TESTAB 872
## Step 2 ##
Gather the stat for the table
=============================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 5);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
----------------- ----------------- -------------
TESTAB 866.88 725.71
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by owner, segment_name; 2 3 4
OWNER SEGMENT_NAME MB
-------------- ---------------- ----------
TESUSER TESTAB 872
## Step 3 ##
==============================================
deleting tables to produce the fragmented space
==============================================
SQL> create table TESTAB_accid as select ACCOUNT_ID from TESTAB where rownum < = 2000000;
SQL> delete from TESTAB
where ACCOUNT_ID in ( select ACCOUNT_ID from TESTAB_accid );
=================
Gather stat again
=================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB 866.88 445.96
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2 from dba_segments
3 where segment_name='TESTAB'
4 and owner in ('TESUSER') group by owner, segment_name;
OWNER SEGMENT_NAME MB
------------- ------------ --------
TESUSER TESTAB 872
## Step 3 ##
==============================================
Defrag table and don't forget to rebuild index
==============================================
SQL> alter table TESTAB move tablespace datal01;
Table altered.
SQL> alter index TESTAB_PK rebuild;
Index altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
as shown below the gap still there because of from the actual size dont include for some percentage each block reserve for header, dict, etc
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB 531.92 447.49
SQL>
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by owner, segment_name; 2 3 4
OWNER SEGMENT_NAME MB
----------- -------------- ----------
TESUSER TESTAB 536
## Step 1 ##
i'm creating the table and the index also (later for deleting purpose)
===============================================
SQL> select /*+ full(a) parallel(a,8) */ count(*) from TESTAB a;
COUNT(*)
----------
5247350
SQL> create unique index TESTAB_pk on TESTAB(ACCOUNT_ID);
Index created.
below is the estimate space before we gather the stat
=====================================================
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
2 from dba_tables where table_name='TESTAB'
3 and owner in ('TESUSER') ;
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------- ----------------- -------------
TESTAB
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2 from dba_segments
3 where segment_name='TESTAB'
4 and owner in ('TESUSER') group by owner, segment_name;
OWNER SEGMENT_NAME MB
------------- -------------------- ----------
TESUSER TESTAB 872
## Step 2 ##
Gather the stat for the table
=============================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 5);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
----------------- ----------------- -------------
TESTAB 866.88 725.71
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by owner, segment_name; 2 3 4
OWNER SEGMENT_NAME MB
-------------- ---------------- ----------
TESUSER TESTAB 872
## Step 3 ##
==============================================
deleting tables to produce the fragmented space
==============================================
SQL> create table TESTAB_accid as select ACCOUNT_ID from TESTAB where rownum < = 2000000;
SQL> delete from TESTAB
where ACCOUNT_ID in ( select ACCOUNT_ID from TESTAB_accid );
=================
Gather stat again
=================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB 866.88 445.96
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2 from dba_segments
3 where segment_name='TESTAB'
4 and owner in ('TESUSER') group by owner, segment_name;
OWNER SEGMENT_NAME MB
------------- ------------ --------
TESUSER TESTAB 872
## Step 3 ##
==============================================
Defrag table and don't forget to rebuild index
==============================================
SQL> alter table TESTAB move tablespace datal01;
Table altered.
SQL> alter index TESTAB_PK rebuild;
Index altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner in ('TESUSER') ; 2 3
as shown below the gap still there because of from the actual size dont include for some percentage each block reserve for header, dict, etc
TABLE_NAME TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB 531.92 447.49
SQL>
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by owner, segment_name; 2 3 4
OWNER SEGMENT_NAME MB
----------- -------------- ----------
TESUSER TESTAB 536
Subscribe to:
Posts (Atom)