Table recovery using RMAN Backup

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> connect sita/sita

Connected.

SQL> create table emp(id number);

Table created.

SQL> begin

for i in 1 .. 100000 loop

insert into emp values(i);

end loop;

end;

/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)

----------

    200000

SQL>

SQL>

SQL> commit;

Commit complete.

SQL> archive log list;

ORA-01031: insufficient privileges

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Feb 10 16:05:01 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEMO (DBID=3807952967)

RMAN> backup database plus archivelog;

Starting backup at 10-FEB-21

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=14 RECID=1 STAMP=1064159388

input archived log thread=1 sequence=15 RECID=2 STAMP=1064159407

input archived log thread=1 sequence=16 RECID=3 STAMP=1064160323

channel ORA_DISK_1: starting piece 1 at 10-FEB-21

channel ORA_DISK_1: finished piece 1 at 10-FEB-21

piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_annnn_TAG20210210T160525_j27fqfbx_.bkp tag=TAG20210210T160525 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 10-FEB-21

Starting backup at 10-FEB-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/demo/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/demo/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/demo/users01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/demo/undotbs01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/demo/usertbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/demo/hmld01.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/demo/lmauto01.dbf

channel ORA_DISK_1: starting piece 1 at 10-FEB-21

channel ORA_DISK_1: finished piece 1 at 10-FEB-21

piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_nnndf_TAG20210210T160528_j27fqkpc_.bkp tag=TAG20210210T160528 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 10-FEB-21

Starting backup at 10-FEB-21

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=17 RECID=4 STAMP=1064160344

channel ORA_DISK_1: starting piece 1 at 10-FEB-21

channel ORA_DISK_1: finished piece 1 at 10-FEB-21

piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_annnn_TAG20210210T160544_j27fr10o_.bkp tag=TAG20210210T160544 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-FEB-21

Starting Control File Autobackup at 10-FEB-21

piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/autobackup/2021_02_10/o1_mf_n_1064160346_j27fr2nf_.bkp comment=NONE

Finished Control File Autobackup at 10-FEB-21

RMAN> exit

Recovery Manager complete.

[oracle@oracle ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 10 16:05:56 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> connect sita/sita

Connected.

SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

NOW

-------------------

02-10-2021 16:06:20

SQL> drop table emp;

Table dropped.

SQL> commit;

Commit complete.

 [oracle@oracle ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Feb 10 16:07:05 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEMO (DBID=3807952967)

RMAN> recover table sita.emp until time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u01/fra/';

Starting recover at 10-FEB-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=61 device type=DISK

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='erwk'

initialization parameters used for automatic instance:

db_name=DEMO

db_unique_name=erwk_pitr_DEMO

compatible=12.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1004M

processes=200

db_create_file_dest=/u01/fra/

log_archive_dest_1='location=/u01/fra/'

#No auxiliary parameter file used

starting up automatic instance DEMO

Oracle instance started

Total System Global Area    1052770304 bytes

Fixed Size                     8628688 bytes

Variable Size                276825648 bytes

Database Buffers             763363328 bytes

Redo Buffers                   3952640 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 10-FEB-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=37 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/demo/DEMO/autobackup/2021_02_10/o1_mf_n_1064159409_j27dts9t_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/autobackup/2021_02_10/o1_mf_n_1064159409_j27dts9t_.bkp tag=TAG20210210T155009

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/fra/DEMO/controlfile/o1_mf_j27fx598_.ctl

Finished restore at 10-FEB-21

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 3, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/fra/DEMO/datafile/o1_mf_tempts1_%u_.tmp in control file

Starting restore at 10-FEB-21

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/fra/DEMO/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/fra/DEMO/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/fra/DEMO/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_nnndf_TAG20210210T160528_j27fqkpc_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_nnndf_TAG20210210T160528_j27fqkpc_.bkp tag=TAG20210210T160528

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:27

Finished restore at 10-FEB-21

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=1064160547 file name=/u01/fra/DEMO/datafile/o1_mf_system_j27fxfbp_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=1064160547 file name=/u01/fra/DEMO/datafile/o1_mf_undotbs1_j27fxfbz_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=1064160547 file name=/u01/fra/DEMO/datafile/o1_mf_sysaux_j27fxfbv_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

Starting recover at 10-FEB-21

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/archive/1_16_1063882631.arc

archived log for thread 1 with sequence 17 is already on disk as file /u01/archive/1_17_1063882631.arc

archived log file name=/u01/archive/1_16_1063882631.arc thread=1 sequence=16

archived log file name=/u01/archive/1_17_1063882631.arc thread=1 sequence=17

media recovery complete, elapsed time: 00:00:02

Finished recover at 10-FEB-21

sql statement: alter database open read only

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files =

  ''/u01/fra/DEMO/controlfile/o1_mf_j27fx598_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1052770304 bytes

Fixed Size                     8628688 bytes

Variable Size                276825648 bytes

Database Buffers             763363328 bytes

Redo Buffers                   3952640 bytes

sql statement: alter system set  control_files =   ''/u01/fra/DEMO/controlfile/o1_mf_j27fx598_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1052770304 bytes

Fixed Size                     8628688 bytes

Variable Size                276825648 bytes

Database Buffers             763363328 bytes

Redo Buffers                   3952640 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  4 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  4;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-FEB-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/fra/ERWK_PITR_DEMO/datafile/o1_mf_users01_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_nnndf_TAG20210210T160528_j27fqkpc_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/demo/DEMO/backupset/2021_02_10/o1_mf_nnndf_TAG20210210T160528_j27fqkpc_.bkp tag=TAG20210210T160528

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 10-FEB-21

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=1064160641 file name=/u01/fra/ERWK_PITR_DEMO/datafile/o1_mf_users01_j27g116z_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-02-10 16:05:35','yyyy-mm-dd:hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  4 online";

# recover and open resetlogs

recover clone database tablespace  "USERS01", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  4 online

Starting recover at 10-FEB-21

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file /u01/archive/1_17_1063882631.arc

archived log file name=/u01/archive/1_17_1063882631.arc thread=1 sequence=17

media recovery complete, elapsed time: 00:00:00

Finished recover at 10-FEB-21

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/fra/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/fra/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/fra/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/fra/''

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_erwk_wofm":

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . . exported "SITA"."EMP"                                1.700 MB  200000 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_erwk_wofm" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_erwk_wofm is:

   EXPDP>   /u01/fra/tspitr_erwk_19724.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_erwk_wofm" successfully completed at Wed Feb 10 16:12:33 2021 elapsed 0 00:01:22

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_erwk_Cxwf" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_erwk_Cxwf":

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "SITA"."EMP"                                1.700 MB  200000 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Job "SYS"."TSPITR_IMP_erwk_Cxwf" successfully completed at Wed Feb 10 16:12:59 2021 elapsed 0 00:00:12

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/fra/DEMO/datafile/o1_mf_tempts1_j27fymdd_.tmp deleted

auxiliary instance file /u01/fra/ERWK_PITR_DEMO/onlinelog/o1_mf_3_j27g1k6p_.log deleted

auxiliary instance file /u01/fra/ERWK_PITR_DEMO/onlinelog/o1_mf_2_j27g1j7m_.log deleted

auxiliary instance file /u01/fra/ERWK_PITR_DEMO/onlinelog/o1_mf_1_j27g1cqs_.log deleted

auxiliary instance file /u01/fra/ERWK_PITR_DEMO/datafile/o1_mf_users01_j27g116z_.dbf deleted

auxiliary instance file /u01/fra/DEMO/datafile/o1_mf_sysaux_j27fxfbv_.dbf deleted

auxiliary instance file /u01/fra/DEMO/datafile/o1_mf_undotbs1_j27fxfbz_.dbf deleted

auxiliary instance file /u01/fra/DEMO/datafile/o1_mf_system_j27fxfbp_.dbf deleted

auxiliary instance file /u01/fra/DEMO/controlfile/o1_mf_j27fx598_.ctl deleted

auxiliary instance file tspitr_erwk_19724.dmp deleted

Finished recover at 10-FEB-21

RMAN> exit

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);

Table Reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================

select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================

--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================

SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...






















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';


2. Export table statistic

Exporting the tables stats is very important; else, tables will start gathering the stats from beginning for the whole table and sometimes that will affects the performance of the queries respect to these tables. Once the reorg has been completed we need to import the stats back to the table, so that the tables will use the old stats while querying the table data


(a) Create Stats Table to store the statistics

Exporting the stats to one of the table say ‘MY_STATS_TABLE’ as temporary table. Once the stats has been imported you can drop the table

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>'<OWNERNAME>’,STATTAB =>'<TABLENAME>’, TBLSPACE =>'<TABLESPACENAME>’);
Example:

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

Example:

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

3. Perform Table & associated index reorg

(a) Table Reorg:

For Non-LOB Tables:



ALTER TABLE <USERNAME>.<TABLE NAME> MOVE;
Example:
ALTER TABLE SCOTT.EMP MOVE;
For Non-LOB Tables:

ALTER TABLE <USERNAME>.<TABLE_NAME> MOVE LOB (COLUMN_NAME) STORE AS (TABLESPACE NEWTABLESPACE);
Example:

ALTER TABLE SCOTT.EMP MOVE LOB (PHOTO) STORE AS TABLESPACE DATA;


b) Rebuild the associated indexes:

If the table has associated indexes which we got it from the step-1 queries, we need to perform the rebuild as they were become INVALID once after the reorg of the parent table.
For Normal Indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD ONLINE;
Example:

ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;
For Bitmap indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD;
Example:

ALTER INDEX SCOTT.EMP_BIT_IDX REBUILD;

4. Import table stats

 EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘<USERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:
EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);

5. Repeat step 1

6. Please collect the invalid object count

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

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS=’INVALID';



7. Run utlrp.sql (If necessary)

SQL> @?/rdbms/admin/utlrp.sql

8. Again Collect the Invalid Object information

9. Execute the below query and compare the values before and after the the reorg, you will be surprised

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

------------------ TABLE FRAGMENTATION--------------------

(I)   ------------------- TABLE FRAGMENTATION--------------------



When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

===TABLE SIZE (WITH FRAGMENTATION)===============

SQL> select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'BIG1';

Example:

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb


========ACTUAL DATA IN TABLE======================

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables
where table_name = 'BIG1';

Example:
TABLE_NAME                     size
------------------------------ ------------------------------------------
BIG1                           30604.2kb


Note = 72952 - 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) - so, the table has 50% extra space which is wasted because there is no data.


How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table ... move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

#################################################################################################################################################################

2. JOBS RUNNING STATUS SCRIPTS

SQL>SELECT owner, job_name, enabled FROM dba_scheduler_jobs

SQL>select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name='ABC';

PARTITION

CREATE TABLE big_t AS
  SELECT ROWNUM AS n_uniq
       , MOD(ROWNUM,10) AS n10
       , MOD(ROWNUM,100000) AS n100k
       , RPAD(rownum,1000,'A') AS filler
    FROM all_source, all_source
   WHERE rownum<= 100000;

SELECT COUNT(1) FROM big_t;

COUNT(1)
--------
  100000

Task completed in 2,249 seconds

SELECT COUNT(1)
  FROM big_t
 WHERE n10 = 1;

COUNT(1)
--------
   10000

Task completed in 2,328 sec


CREATE TABLE big_t_list
 PARTITION BY LIST(n10)
   (partition part1 VALUES (1)
   ,partition part2 VALUES (2,3,4)
   ,partition part3 VALUES (DEFAULT))
  AS SELECT *
       FROM big_t;

SELECT table_name, tablespace_name, blocks, num_rows
  FROM user_tables
 WHERE table_name LIKE 'BIG_T%';



SELECT table_name, partition_name, high_value, tablespace_name, blocks, num_rows
  FROM user_tab_partitions
 WHERE table_name LIKE 'BIG_T%';


select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where owner='&owner';
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('SHY')

select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || ';' from DBA_INDEXES WHERE OWNER IN ('OWNER1','OWNER2')
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ';' from DBA_TABLES WHERE OWNER IN ('OWNER1','OWNER2')

Table recover

drop table roy.persons.


select object_name, original_name, type from dba_recyclebin;

connect to users.

flashback table persons to before drop.

select * from tab;

ALTER TABLE OME.W_RP_ADJ_F ADD (

INS_DT  TIMESTAMP(3),

LAST_UPD_DT  TIMESTAMP(3),

IS_ACTIVE VARCHAR2(1 CHAR)

);

ALTER TABLE OMG.KVI_ADJNTS_HCL_HIST ADD (

INS_DT  TIMESTAMP(3),

LAST_UPD_DT  TIMESTAMP(3));

Table and Index

Table and index extract

set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
 
spool ddl_list.sql
 select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; 
 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
 spool off;



set pagesize 0

 set long 90000

 set feedback off

 set echo off 
 spool scott_schema.sql 
 connect scott/tiger;
 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;
 spool off;



For table

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

 DBMS_METADATA




________________________________________________________________________________________


ORA-31693: Table data object
"PMFADMIN"."TMP_PRODUCT_DESC_KWD" failed to load/unload and
is being skipped due to error:



ORA-02354: error in exporting/importing data



ORA-39826: Direct path load of view or synonym
(PMFADMIN.TMP_PRODUCT_DESC_KWD) could not be resolved.













select owner, object_name, object_type, last_ddl_time
from   dba_objects
where  owner = 'SDB' and object_name in ('XSDB_ALL_39A_PLANTS_CNTRCT');