How to clean datapump imp/exp

1. Check name and owner of running datapump job:

  1* select owner_name, job_name,state from dba_datapump_jobs

OWNER_NAME                     JOB_NAME                       STATE
------------------------------ ------------------------------ ------------------------------
DATAPUMP                       SYS_IMPORT_FULL_01             EXECUTING
DATAPUMP                       SYS_EXPORT_TABLE_01            NOT RUNNING

2. Attach to job :

impdp datapump/<pwd>@<SID> attach=SYS_IMPORT_FULL_01

3. Kill job:

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Datapump - short info I

SQL> create directory datapump as '/app/oracle/export';

Directory created.

SQL> create user datapump identified by datapump;

User created.

SQL> grant create session, exp_full_database, imp_full_database to datapump;

Grant succeeded.

conn datapump:
SQL> create database link JDED_TEST connect to testdta identified by **** using 'JDED';

Database link created.

SQL> alter user datapump default tablespace reddtat;

User altered.

SQL> alter user datapump quota unlimited on reddtat;

expdp.par:
userid=datapump/****@JDERED
DIRECTORY=datapump
NETWORK_LINK=JDED_TEST
DUMPFILE=jded_test1.dmp
LOGFILE=jded_test1.log
tables=TESTDTA.DATAPUMP_1

impdp.par:
userid=datapump/****@JDERED
DIRECTORY=datapump
DUMPFILE=jded_test1.dmp
LOGFILE=jded_test_imp.log
REMAP_SCHEMA=TESTDTA:REDDTA
REMAP_TABLESPACE=TESTDTAT:REDDTAT
REMAP_TABLESPACE=TESDTAI:REDDTAT

Flashback - Time Machine

We have already database in flashback mode. Let's see what option in gives us:

1. See object in a past:

SQL> select count(*) from t1;

  COUNT(*)
----------
    132027

SQL> select count(*) from t1 as of timestamp to_timestamp('2008-11-26 07:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
     40001

Alternatively you can use SCN:

SQL>  SELECT SCN,
2     to_char(TIME_DP, 'YYYY-MM-DD HH24:MI:SS') TIME_DP
3     from sys.SMON_SCN_TIME
4     where to_char(TIME_DP,'YYYY-MM-DD HH24') = to_char(sysdate-1,'YYYY-MM-DD HH24')
5    order by SCN_BAS;

           SCN TIME_DP
-------------- ------------------------------
 8704635105759 2008-11-26 07:00:29
 8704635105915 2008-11-26 07:05:34
 8704635106215 2008-11-26 07:10:31
 8704635106316 2008-11-26 07:15:34
 8704635106420 2008-11-26 07:20:32
 8704635106521 2008-11-26 07:25:35
 8704635106631 2008-11-26 07:30:32
 8704635106735 2008-11-26 07:35:35
 8704635106839 2008-11-26 07:40:32
 8704635106940 2008-11-26 07:45:35
 8704635107058 2008-11-26 07:50:32
 8704635107159 2008-11-26 07:55:35

SQL> select count(*) from t1 as of scn 8704635105759;

  COUNT(*)
----------
     40001




2. Turn database to some point in time:

SQL>  EXECUTE dbms_Flashback.Enable_At_Time(to_date('2008-11-26 07:00:00','YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
     40001

SQL> EXECUTE dbms_Flashback.disable;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
    132027

Quick Flashback setup

1. Archive_log_dest and Archive_log_dest_duplex needs to be disabled. If any additionaly archive log destination is needed, use archive_log_dest_n='LOCATION=.....'

2. alter system set DB_RECOVERY_FILE_DEST_SIZE=<size>M;

3. alter system set DB_RECOVERY_FILE_DEST='<destination>';

4. Shutdown database

5. Startup mount

6. Alter database archivelog (just to be sure)

7. Alter database flashback on;

8. Alter database open

Monitoring:

1. select * from V$FLASH_RECOVERY_AREA_USAGE;

In case of lack of space, use rman to backup flasharea to tape and delete input:

run
{
allocate channel ch1 type = 'SBT_TAPE';
backup recovery area delete input;
}

Find High Water Mark for all datafiles in database

set serveroutput on
execute dbms_output.enable(20000);

declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
order by tablespace_name,file_id;

cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;

blocksize integer;
filesize integer;
extsize integer;

begin

/* get the blocksize of the database, needed to calculate the startaddress */

select value
into blocksize
from v$parameter
where name = 'db_block_size';

/* retrieve all datafiles */

for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/