dbms_metadata package to extract the schema ddl

Oracle schema / user ddl  via dbms_metadata

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);

SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';

dbms_metadata package to extract the schema ddl

Oracle dbms_metadata package to extract the schema ddl ....

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);


SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';



set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual ;


set autoprint on long 100000
variable Y clob
variable x clob

declare
            no_grant exception;
            pragma exception_init( no_grant, -31608 );
begin
   
  
begin 
            :Y := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '&&USER' );
         
exception
           when no_grant then :Y := '-- no system grants';
end ;
 end;
/




SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT','RESOURCE') from dual;

What is a Clustering Factor and how to improve?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,b.num_rows,b.blocks,a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
and a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;


SELECT constraint_name,table_name,r_constraint_name,status FROM dba_constraints 
WHERE constraint_type='P' and table_name='USER_SITES' 
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED



SQL> create table anuj.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "ANUJ"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ"; 

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
-- and index_name in ('USER_SITES_USPK1')
and a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') 
/


INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792 <<<<<--- 
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<<--new Clustering Factor 


now drop the old table. rename new table to old table name


to modify clustering factor >12c

TABLE_CACHED_BLOCKS statistics preference allows us to modify how the CF is 
calculated by not incrementing the CF value if an index rowid points to a block that was visited 
just TABLE_CACHED_BLOCKS




set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual 





PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             1




to set value !!!

set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
exec dbms_stats.set_table_prefs(ownname=>'&&owner', tabname=>'&&TABLE', pname=>'TABLE_CACHED_BLOCKS', pvalue=>10);
 



define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual

SQL> /
old   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual
new   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'EMP',ownname => 'ANUJ') prefs_for__EMP FROM dual

PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             10


EXEC dbms_stats.gather_table_stats(ownname=>&&owner, tabname=>'&&TABLE', estimate_percent=> null, cascade=> true,   method_opt=>'FOR ALL COLUMNS SIZE 1');


gather stats ... 

define tabname='EMP'
define owner='ANUJ'

set pages 100 lines 250
--set echo off feedback off heading on 
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather  from   dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;



exec dbms_stats.gather_table_stats (ownname => 'ANUJ', tabname => 'EMP' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

What is a Clustering Factor and how to improve ?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where
a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;




SELECT constraint_name,table_name,r_constraint_name,status
FROM dba_constraints WHERE constraint_type='P' and table_name='USER_SITES' 2
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED




select dbms_metadata.get_ddl('TABLE','USER_SITES','ANUJ') from dual;

DBMS_METADATA.GET_DDL('TABLE','USER_SITES','ANUJ')
--------------------------------------------------------------------------------

CREATE TABLE "ANUJ"."USER_SITES"
( "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
"SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
"SITE_DATE_ADDED" DATE NOT NULL ENABLE,
"TANDC_DATE_ACCEPTED" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "ANUJ" ;
ALTER TABLE "ANUJ"."USER_SITES" ADD CONSTRAINT "USER_SITES_USPK1" PRIMARY
KEY ("USER_OBJECT_ID", "SITE_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "ANUJ" ENABLE;






SQL> create table cccprod1.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "CCCPROD1"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ";


Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where
-- index_name in ('USER_SITES_USPK1')
a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') /

INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<---- good Clustering Factor

now drop the old table. rename new table to old table name