Sequence

sequence rebuild
+++++++++++


select 'drop sequence ' || sequence_name || ';' from user_sequences;


select 'select dbms_metadata.get_ddl('||'''SEQUENCE'''||','''||SEQUENCE_NAME||''','''||SEQUENCE_OWNER||''') FROM DUAL ;' FROM all_sequences where sequence_owner='ODSADMIN'';

select 'select dbms_metadata.get_ddl('||"'SEQUENCE"'||',"'||SEQUENCE_NAME||","||SEQUENCE_OWNER||"’) FROM DUAL ;' FROM all_sequences where sequence_owner='ORADMIN’ and sequence_name like 'MKT_SHOPPING_SEQ';



Create sequence
+++++++++++

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;' from all_sequences where sequence_owner='ODSADMIN';
#!/usr/bin/ksh
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




TOD1DB_get_sequences.ksh


sqlplus /nolog <<EOF
connect / as sysdba

set lines 188
set trimspool on
set pages 0

spool create_sequences.lst

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;'
from all_sequences where sequence_owner='ODSADMIN';

EOF


if [ -f create_sequences.sql ]; then
        rm create_sequences.sql
fi

cat TOD1DB_get_sequences_header.txt > create_sequences.sql

cat create_sequences.lst |grep -v selected |grep -v SQL |grep -v "  2  from all_" >> create_sequences.
sql

echo "" >> create_sequences.sql
echo "quit" >> create_sequences.sql





2)TOD1DB_get_sequences_header.txt

connect / as sysdba

set time on  timing on  echo on pagesize 20000 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;


3) drop sequence


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
spool drop_sequences_r.sql
select 'drop sequence '||sequence_owner||'.'||sequence_name||';'
from dba_sequences
where sequence_owner=upper('&l_schema') ;

spool off



4)grant prilvege




SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS where owner='ODSADMIN';

Table reorganization sample

select SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_segments where segment_name='TABLE_NAME';

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';

SUM(BYTES)/1024/1024/1024
-------------------------
               228.612793

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';

SUM(BYTES)/1024/1024/1024
-------------------------
               197.090515


select segment_name,segment_type from dba_segments where owner='ownername' and segment_name='TABLE_NAME';



export and import


expdp \'/ as sysdba\' tables=OMSTG.KVI_SUMMARY_HIST directory=EXPDP_DIR dumpfile=META.dmp logfile=META.log content=METADATA_ONLY

expdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump.dmp logfile=ods_dump.log


expdp /  logfile=metadata dumpfile=metadata parallel=4 compression=all content=METADATA_ONLY


impdp  \'/ as sysdba\'  tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump.dmp  REMAP_TABLESPACE =OSADMIN_DATA01:USER_TEMP;


expdp \'/ as sysdba\' tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump1.dmp logfile=ods_dump1.log

impdp  \'/ as sysdba\'  tables=odsadmin.ORDER_DETAIL_STATUS directory=EXPDP_DIR dumpfile=ods_dump1.dmp  REMAP_TABLESPACE =USER_TEMP:OSADMIN_DATA01 table_exists_action=truncate;




set lines 200
set pages 0
set feedback off
spool move.sql
select
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace USERS_TEMP' || ';'
from dba_segments
where tablespace_name='ODSADMIN_DATA01'
and segment_type in('TABLE','INDEX') order by owner, segment_type desc, segment_name;
spool off


set lines 200
set pages 0
set feedback off
spool move1.sql
select 'alter index '|| owner||'.'||segment_name || ' rebuild partition '|| partition_name ||' tablespace USERS_TEMP;'
from dba_segments where segment_type ='INDEX PARTITION' and tablespace_name='ODSADMIN_DATA01';
spool off

select table_name,index_name,column_name from user_ind_columns where table_name='SHSHTDTA'

CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/oraclebase/backup/TRACKER/dump';
GRANT READ, WRITE ON DIRECTORY EXPDP_DIR TO sys;

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

expdp \'/ as sysdba\' tables=OMSTG.BASE_ASSIGNMENTS directory=EXPDP_DEV_DIR dumpfile=test.dmp logfile=test.log parallel=1
compression=all


___________________________________________________________________

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '||owner||'.'|| table_name || ' move tablespace USERS_TEMP;' from dba_indexes where index_name in (
select  segment_name from dba_segments
where tablespace_name='OMSTG_HIST' and segment_type='INDEX'and segment_name='KVI_SUMMARY_HIST');
spool off

set lines 200
set pages 0
set feedback off
spool move.sql
select 'alter table '|| owner || '.' ||table_name|| ' move LOB ('||COLUMN_NAME ||') STORE AS (tablespace USERS_TEMP);'
from DBA_LOBS where tablespace_name='USERS';
spool off

select table_name,column_name from dba_lobs where table_name='KVI_SUMMARY_HIST';

select  segment_name from dba_segments where tablespace_name='OMSTG_HIST' and segment_type='INDEX');

select 'alter index '||owner||'.'||segment_name ||' rebuild subpartition ' || partition_name ||' tablespace USERS_TEMP;'   
from dba_segments where tablespace_name='USERS' and segment_type = 'INDEX SUBPARTITION';

select segment_name,partition_name from dba_segments where tablespace_name='OMSTG_HIST' and segment_type = 'INDEX PARTITION';

select
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move', ' rebuild' ) ||
' tablespace USERS_TEMP' || ';'
from dba_segments
where tablespace_name='OMSTG_HIST'
and segment_type in('TABLE','INDEX') order by owner, segment_type desc, segment_name;


SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('KVI_SUMMARY_HIST');

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN ('KVI_SUMMARY_HIST');

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('KVI_SUMMARY_HIST');

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';

__________________________________________________________________



Index
_____

sqlplus /nolog

connect system/password

select index_name from dba_indexes
where  tablespace_name ='EXAMPLE' and
       index_name like 'E%'
order  by 1 asc;

select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;


set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
spool rcindscr.sql

select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';

spool off

set heading off
set echo off
set flush off
set pagesize 8000
set linesize 80
set long 3999
set longc 3999
spool indsyntax.sql

select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_NAME_IX', 'HR') from dual;

spool off



Partitioned tables
============================================

SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' ||  PARTITION_NAME||  ' TABLESPACE TESTTBS;'  FROM ALL_tab_partitions
WHERE TABLE_OWNER = 'owner_name' AND table_NAME NOT LIKE 'BIN$%';


select 'alter table ' || table_name || ' move partition ' || partition_name|| ' tablespace ODSADMIN_DATA01 parallel 10;' "PARTITION_MOVE_SCRIPT" from dba_tab_partitions where table_name='SALES_PART';

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20

SELECT table_name,
       partition_name,
       subpartition_name,
       num_rows
FROM   dba_tab_subpartitions where table_name='KVI_SUMMARY_HIST';



column owner format a20;
column SEGMENT_NAME   format a20;
column PARTITION_NAME format a20;
column SEGMENT_TYPE format a20
column TABLESPACE_NAME format a20;
column TABLESPACE_NAME format 99999999999999;
SELECT  OWNER  , SEGMENT_NAME   , PARTITION_NAME ,  SEGMENT_TYPE,TABLESPACE_NAME , BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE '&TABLE_NAME';






Partitioned indexes
============================================

SELECT  'ALTER INDEX '||I.INDEX_NAME||'REBUILD PARITION'|| S.PARTITION_NAME || ' TABLESPACE  TESTTBS '
                   FROM  DBA_INDEXES I,    DBA_SEGMENTS S
                  WHERE  I.INDEX_NAME = S.SEGMENT_NAME
                    AND I.INDEX_TYPE IN ('NORMAL', 'BITMAP')
--                    AND I.OWNER = 'owner_name'
                           and S.SEGMENT_NAME='TABLE_NAME';



___________________________________________________________________


connect / as sysdba

set time off timing off echo off pagesize 10000 linesize 150 feed off



SELECT 'ALTER TABLE '|| TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION ' ||  PARTITION_NAME||  ' TABLESPACE OMSTG_TEMP;'  FR
OM ALL_tab_partitions
WHERE TABLE_NAME = 'KVI_SUMMARY_HIST' AND table_NAME NOT LIKE 'BIN$%';

___________________________________________________________________________________________

SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME||  ' TABLESPACE OMSTG_TEMP;'
 FROM dba_tab_subpartitions
WHERE TABLE_NAME ='DISCRETE_HIST' AND table_NAME NOT LIKE 'BIN$%';


connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;



select table_owner,table_name,subpartition_name,tablespace_name from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';


select segment_name,partition_name from dba_segments where segment_name='W_RP_MED_ASSIGNMENT_F';


select partition_name,SUBPARTITION_NAME from dba_tab_subpartitions where table_name='W_RP_MED_ASSIGNMENT_F';


SELECT 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE SUBPARTITION ' ||SUBPARTITION_NAME||  ' TABLESPACE OMSTG_TEMP;'  FROM dba_tab_subpartitions
WHERE TABLE_NAME ='W_RP_MED_ASSIGNMENT_F' AND table_NAME NOT LIKE 'BIN$%';

subpartition;


connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

alter table OMEDW.W_RP_MED_ASSIGNMENT_F enable row movement;

ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169979 TABLESPACE OMSTG_TEMP parallel 6;
ALTER TABLE OMEDW.W_RP_MED_ASSIGNMENT_F MOVE SUBPARTITION SYS_SUBP169980 TABLESPACE OMSTG_TEMP parallel 6;


__________________________________________________________________________
synonyms

SELECT OWNER, CONSTRAINT_NAME , STATUS FROM  dba_constraints WHERE
CONSTRAINT_NAME LIKE 'FK1_BRANCH_ITEM_CHANGE_LOG%'
UNION
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS
WHERE
SYNONYM_NAME ='BRANCH_ITEM'
;


select distinct object_type from dba_objects where owner='OMSTG';



SET HEADING OFF
SET pagesize 200
SET linesize 200
select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';



SET HEADING OFF
SET PAGESIZE 200
SET LINESIZE 200
select ' ALTER TABLE OMEDW.'||TABLE_NAME||' MOVE TABLESPACE OMSTG_HIST parallel 5 nologging;' from dba_tables where owner='OMEDW';


SET HEADING OFF
SET long 9999
SET linesize 200
select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>)   online parallel 3 nologging;' where owner='<owner>.';

REBUILDING THE TABLE BY SHRINKING ITS FREE SPACE

Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a short table level lock takes place to adjust the HWM (Highwater Mark) of the table.

And it's easy!!
Just enable 'row movement' and start shrinking tables:
SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;

This enables row movement of the table. This is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.
SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;

This shrinks the used space of the table, but does not update the HWM. This is useful if you don't want to have a table-level lock during business hours.
SQL> ALTER TABLE MYTABLE SHRINK SPACE; 

This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.
SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE; 

This command shrinks the contents of the table and all dependent objects like indexes. 



Triggers and indexes: 

In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.
Wasted space: 

How to determine if a table is a candidate for a reorganization ?

Use the below script to determine if a table has a lot of wasted space.
If it substantial in relation to the total size of the table, then a reorganization ( shrink ) is recommended.
set lines 200;
column owner format a15;
column segment_name format a30;

select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;

The above script is only working if the tables involved have statistics gathered on them 
PCTFREE 

An extra remark about PCTFREE.
Tables with a high value of PCTFREE show also a higher value of wasted space.
The space reserved for PCTFREE in a block can not be shrinked. So even after a shrink of a table a lot of wasted space can remain.

The effect of PCTFREE also seen before the shrink is executed when looking at the wasted space using the above script.

In an example we have three identical tables with the following structure: 
SQL> CREATE TABLE T1 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 40;
SQL> CREATE TABLE T2 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 10;
SQL> CREATE TABLE T3 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 50;

All tables are loaded with the same data, approx. 5.5 million rows.
When running the above script it shows: 

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2348
T2	TABLE	3333	617
T3	TABLE	6144	3428

Just because of the variation in the value of PCTFREE, the total size and wasted space are different per table.

The effect of PCTFREE after the shrink is that not all "wasted space" is released.
The term "wasted space" therefore is not really valid with a high PCTFREE. It is wasted space + the reserved space directly available for updates. Of course above, just after table creation there is no wasted space. It is only reserved (pctfree) space. Therefore - for this example - we are going to delete approx 30% of the rows of all tables.

SQL> delete from t1 where length(val)< 300;

SQL> delete from t2 where length(val)< 300;

SQL> delete from t3 where length(val)< 300;

As soon as the delete is finished we compute stats on these tables in order for the wasted-space script to have a valid calculation base.

Running the script again now shows:

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2789
T2	TABLE	3333	1058
T3	TABLE	6144	3869

After this we are going to 'shrink' the available wasted space, using:

SQL> ALTER TABLE T1 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T2 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T3 SHRINK SPACE CASCADE;

The result is:

SEGMENT_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	4245	1970
T2	TABLE	2779	505
T3	TABLE	5292	3018

This test clearly shows that even if Oracle or its tools like ADDM inform you about wasted space in a table, it might be that this is because you have a high value for PCTFREE. Shrinking these table might afterwards look like if nothing happened.
Check in that case if PCTFREE is high and if the value of 'wasted space' divided by the 'total size' is equal to that value:

Example:

Table T1: PCTFREE = 40% ==> 1970 / 4245= 46%
Table T3: PCTFREE = 50% ==> 3018/5292 = 57%

So, actually the 'wasted space' of these tables is respectively 6% and 7 %

Table Reorganization

By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.

Take the sizes of tables , associate indexes and take the invalid object information , Check for table,index status

Example:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<INDEXNAMES>);

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID';

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB =>’MY_STATS_TABLE’, TBLSPACE =>’USER’);

(b) Export the stats to the above created table

EXEC

DBMS_STATS.EXPORT_TABLE_STATS(‘<OWNERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:

EXEC DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);