Oracle Export-Import with QUERY parameter on LINUX box

There's this one time when I like to purge a certain table but it is very large. It creates a lot of archive logs when I am performing delete statements using a where clause as a delimiter for the rows I'd like to delete. It is very time consuming and it eats space on the disk. And one more thing is that, there are more rows that I need to delete that the rows I need to keep.

So I just tried to purge this very large table using Oracle' EXP and IMP utility. My plan is to export the rows that I want to keep and truncate the table I am purging. Afterwards, I will IMP the dumpfile into that same table.

Here's what I did:

Environment:
OS: RHELinux 5.4 32bit
DB: Oracle 10.2.0.4

SQL Query:
SQL> select * from sh.sales
     where time_id = to_date('28-DEC-01:00:00:00',
     'DD-MON-RR:HH24:MI:SS');

The output of this query are the rows that I want to keep.

My gameplan is:
1. Perform EXP using QUERY parameter.
2. Truncate the table I am purging
3. Perform IMP
4. Gather table statistics

Here is my export parameter file:

[oracle@shaoran selective_export]$ cat exp_mysale.par
userid="sys/oracle as sysdba"
buffer=10000000
file=mysales148.dmp
query="where time_id=to_date('28-DEC-01:00:00:00','DD-MON-RR:HH24:MI:SS')"
log=mysales148.log
TABLES=sh.mysales
recordlength=65535
statistics=none

My actual export goes:

[oracle@shaoran selective_export]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@shaoran selective_export]$ exp parfile=exp_mysale.par

Export: Release 10.2.0.1.0 - Production on Fri Feb 4 12:13:28 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SH
. . exporting table MYSALES 4784 rows exported
Export terminated successfully without warnings.


My export parfile is:

[oracle@shaoran selective_export]$ cat import_mysales.par
userid="sys/oracle as sysdba"
buffer=10000000
file=mysales148.dmp
log=imp_mysales148.log
IGNORE=y
FEEDBACK=10000
STATISTICS=none
RESUMABLE=y
RESUMABLE_TIMEOUT=600
FROMUSER=sh
TOUSER=sh
TABLES=mysales


And my import goes:


[oracle@shaoran selective_export]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@shaoran selective_export]$ imp parfile=import_mysales.par

Import: Release 10.2.0.1.0 - Production on Fri Feb 4 12:28:18 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SH's objects into SH
. . importing table                      "MYSALES"
                                                         4784 rows imported
Import terminated successfully without warnings.


Post tasks includes gathering table statistics:


SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SH', 
tabname=>'MYSALES',estimate_percent=> dbms_stats.auto_sample_size, cascade => true);


Gilbert S Dela Cruz
Oracle Database Administrator
Oracle Database 11g Certified Associate
Registered Electronics Engineer
Member IECEP Philippines Inc.

Comments

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP ...
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700...
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs...
Read more