Flashback_Scn and flashback_Time parameter of Data Pump

Flashback_Scn  and  flashback_Time  are  two  important  feature  of  the  datapump 11g . If  we  want  to  run  a  large  export  whilst  the  database  is  in  use  then  ideally  we  should  always use  one  of  the  two  flashback  parameters. The export  operation  is  performed  with  data  that is  consistent  as  of  the  specified  SCN .  FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive .

FLASHBACK_TIME : The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN. The FLASHBACK_SCN parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive. We can get the scn number from the following query :

SQL> select current_scn from v$database ;       or
SQL>select dbms_flashback.get_system_change_number from dual ; 

Let's have a Demo of the flashback_scn

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1140271

SQL> create table hr.test as select * from test;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1140487
Let's take a export using flashback_scn  parameter

C:\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log       flashback_scn=1140271

Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:35:45
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log    flashback_scn=1140271
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES"                            6.375 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.484 KB       4 rows
ORA-31693: Table data object "HR"."TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\FLASHBACK_HR.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 11:37:50

The above error show that the table "test"  is not include in the  export operation because the SCN mention  is of before the table "test" creation. The below export will show the export upto current SCN when database is in use.

C:\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp  logfile=flashback_log.log

Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:44:50
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES"                                6.375 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."EMPLOYEES"                             16.80 KB     107 rows
. . exported "HR"."JOBS"                                             6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                             7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                                8.273 KB      23 rows
. . exported "HR"."REGIONS"                                      5.484 KB       4 rows
. . exported "HR"."TEST"                                              5.054 KB       8 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\FLASHBACK_HR1.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:46:41


Enjoy     :-)

Estimate Parameter in Data Pump Export

ESTIMATE  parameter  of data pump  export specify the method that Export will use to estimate how much disk space  each  table in the export  job will  consume (in bytes)  before  performing actual data pump export operation .

The ESTIMATE parameter can take two parameters. Either BLOCKS (default) or STATISTICS. The meaning of these two parameter values are specified below.

BLOCKS : The estimate is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes.

STATISTICS : The estimate is calculated using statistics for each table. So to be accurate you must analyze table recently.

Note that the outcome specified by ESTIMATE=BLOCKS is far away from the size of the actual dumpfile. In fact, ESTIMATE=BLOCKS method generates more inaccurate result from dump file size when,

a) The table was created with a much bigger initial extent size than was needed for the actual table data.

b) Many rows have been deleted from the table, or a very small percentage of each block is used.

The outcome generated by ESTIMATE=STATISTICS is most accurate to dump file size if recently table is analyzed.

Below is an example shown both in case of ESTIMATE=STATISTICS and ESTIMATE=BLOCKS. In both cases data pump export dump file is generated after estimation of dump file.

C:\>expdp system/ramtech@noida directory=dpump  schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCKS

Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:26:02

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr logfile=hrlog11.log ESTIMATE=BLOCKS

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

.  estimated "HR"."COUNTRIES"                                64 KB

.  estimated "HR"."DEPARTMENTS"                          64 KB

.  estimated "HR"."EMPLOYEES"                               64 KB

.  estimated "HR"."JOBS"                                             64 KB

.  estimated "HR"."JOB_HISTORY"                             64 KB

.  estimated "HR"."LOCATIONS"                                64 KB

.  estimated "HR"."REGIONS"                                     64 KB

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "HR"."COUNTRIES"                                6.375 KB      25 rows

. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows

. . exported "HR"."EMPLOYEES"                              16.80 KB     107 rows

. . exported "HR"."JOBS"                                             6.984 KB      19 rows

. . exported "HR"."JOB_HISTORY"                            7.054 KB      10 rows

. . exported "HR"."LOCATIONS"                               8.273 KB      23 rows

. . exported "HR"."REGIONS"                                     5.484 KB       4 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  D:\DPUMP\EXPDAT.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:28:16

Now we will export the schemas by using ESTIMATE=STATISTICS

C:\>expdp system/ramtech@noida directory=dpump  schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS

 Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 19:31:15

 Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=hr dumpfile=hr1.dmp logfile=hrlog111.log ESTIMATE=STATISTICS

Estimate in progress using STATISTICS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

.  estimated "HR"."EMPLOYEES"                           15.91 KB

.  estimated "HR"."LOCATIONS"                             8.034 KB

.  estimated "HR"."JOB_HISTORY"                          6.861 KB

.  estimated "HR"."JOBS"                                           6.795 KB

.  estimated "HR"."DEPARTMENTS"                        6.710 KB

.  estimated "HR"."COUNTRIES"                              6.150 KB

.  estimated "HR"."REGIONS"                                   5.488 KB

Total estimation using STATISTICS method: 55.95 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows

. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows

. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows

. . exported "HR"."JOBS"                                 6.984 KB      19 rows

. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows

. . exported "HR"."COUNTRIES"                            6.375 KB      25 rows

. . exported "HR"."REGIONS"                              5.484 KB       4 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  D:\DPUMP\HR1.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:33:24

Using ESTIMATE=BLOCKS, before data pump export size is shown as 448 KB and using ESTIMATE=STATISTICS, before data pump export size is shown as 55.95 KB and my actual dump file size was 408KB which is away from estimation using ESTIMATE=BLOCKS as difference is 448-408=40 KB. In later case difference is 448-55.95=392.05KB

Note that if a table involves LOBs, the dump file size may vary as ESTIMATE does not take LOB size into consideration.

Enjoy      J J J

How To Estimate Disk Space Needed for an Export Job ?

If we  just  want to  know how  much  disk space  for  the  dump  without  actually   exporting  any data, we  can use the ESTIMATE_ONLY=y parameter on  the  expdp  command. ESTIMATE_ONLY=y  parameter  estimate  the space in bytes per tables. The below demo will show that how a system user wants to see the disk space estimates on  hr and scott schemas export . 

C:\>expdp system/ramtech@noida directory=dpump  schemas=scott,hr estimate_only=y

Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 18:56:03

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump schemas=scott,hr logfile=scott_hr.log estimate_only=y

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

.  estimated "HR"."COUNTRIES"                               64 KB

.  estimated "HR"."DEPARTMENTS"                             64 KB

.  estimated "HR"."EMPLOYEES"                               64 KB

.  estimated "HR"."JOBS"                                    64 KB

.  estimated "HR"."JOB_HISTORY"                             64 KB

.  estimated "HR"."LOCATIONS"                               64 KB

.  estimated "HR"."REGIONS"                                 64 KB

.  estimated "SCOTT"."DEPT"                                 64 KB

.  estimated "SCOTT"."EMP"                                  64 KB

.  estimated "SCOTT"."SALGRADE"                             64 KB

.  estimated "SCOTT"."BONUS"                                 0 KB

Total estimation using BLOCKS method: 640 KB

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:57:38

Now we see that we need 640 KB disk space to export the entire data base.

Note : 

• The ESTIMATE_ONLY parameter cannot be used in conjunction with the QUERY parameter dumpfile.
• Estimate Parameter  do not give the accurate size . It gives the approax.  size of the dumpfile . 
• If the Data Pump export job involves compressed tables, the default size estimation given for the compressed table is inaccurate when ESTIMATE=BLOCKS is used. This is because the size estimate does not reflect that the data was stored in a compressed form. To get a more accurate size estimate for compressed tables, use ESTIMATE=STATISTICS.
• The estimate may also be inaccurate if the QUERY, SAMPLE, or REMAP_DATA parameter is used.

Enjoy    :-) 

Data Pump Encryption in Oracle

Encryption parameters is a  new feature introduced in oracle 10g data-pump . The only parameter used in 10g is encryption password  . In Oracle 11g , three more encryption parameter was introduced in data-pump .The three parameter are  encryption , encryption_algorithm  and  encryption_mode .

To enable encryption, either the ENCRYPTION or ENCRYPTION_PASSWORD parameter, or both, must be specified. If only the ENCRYPTION_PASSWORD parameter is specified, then the ENCRYPTION parameter defaults to ALL. If neither ENCRYPTION nor ENCRYPTION_PASSWORD is specified, then ENCRYPTION defaults to NONE. Data pump encryption is specified by the encryption parameter, the algorithm of the encryption and the mode of the encryption

Here is a demo which includes all the encryption command.

C:\>expdp system/ramtech@noida directory=dpump  dumpfile=hr_schema.dmp logfile=exp_hrlog.log  schemas=hr encryption=all  encryption_password=india encryption_algorithm=AES256 ENCRYPTION_MODE=password

Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:18:32

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump dumpfile=hr_schema.dmp logfile=exp_hrlog.log schemas=hr encryption=all encryption_password=******** encryption_algorithm=AES256 ENCRYPTION_MODE=password

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "HR"."COUNTRIES"                            6.382 KB      25 rows

. . exported "HR"."DEPARTMENTS"                          7.023 KB      27 rows

. . exported "HR"."EMPLOYEES"                            16.81 KB     107 rows

. . exported "HR"."JOBS"                                 6.992 KB      19 rows

. . exported "HR"."JOB_HISTORY"                          7.062 KB      10 rows

. . exported "HR"."LOCATIONS"                            8.281 KB      23 rows

. . exported "HR"."REGIONS"                              5.492 KB       4 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  D:\DPUMP\HR_SCHEMA.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:21:29

Now we will import the above dump file without specifying the password and check the error.

C:\>impdp system/ramtech@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr.log remap_schema=hr:harry

Import: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:30:17

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39174: Encryption password must be supplied.

Now  we use encryption_password command to successfully completed the above task

C:\>impdp system/ramtech@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr1.log remap_schema=scott:harry  encryption_password=india 

Import: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:36:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr1.log remap_schema=hr:harry encryption_password=********

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"HARRY" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HARRY"."COUNTRIES"                         6.382 KB      25 rows

. . imported "HARRY"."DEPARTMENTS"                       7.023 KB      27 rows

. . imported "HARRY"."EMPLOYEES"                         16.81 KB     107 rows

. . imported "HARRY"."JOBS"                              6.992 KB      19 rows

. . imported "HARRY"."JOB_HISTORY"                       7.062 KB      10 rows

. . imported "HARRY"."LOCATIONS"                         8.281 KB      23 rows

. . imported "HARRY"."REGIONS"                           5.492 KB       4 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:37:12

Enjoy      J J J

Tracking Oracle database growth

Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database.

Oracle provides time-series segment statistics for tables and index in the 10g Automated  workload Repository tables (AWR).  These can quickly extract data for Oracle growth reports. In Oracle 10g, total database  growth  reports  are  is  easy because the dba_hist_seg_stat tables  provides   the delta value for every table and index. Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.

This script is helpful in tracking  the oracle growth


SQL> select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  from  ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a

) b GROUP BY b.tsname ORDER BY b.tsname;

 Output : 

TABLESPACE_NAME                CUR_USED_SIZE_MB           AVG_INCREAS_MB

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

ENOM_CM_DATA                          1121.31                            78.71

ENOM_CM_INDEX                         2.38                                  0

ENOM_EL_CATALOG                      4.13                                 .01

ENOM_EL_DATA                           13.63                                0

ENOM_EL_INDEX                          16.06                               .01

ENOM_GS_CATALOG                     1                                      0

ENOM_GS_DATA                          1                                      0

ENOM_LA_DATA                          5                                      0

ENOM_LA_INDEX                         3                                      0

EXAMPLE                                   78.44                                0

SYSAUX                                    614.75                              14.46

SYSTEM                                    692.88                              1.32

UNDOTBS1                                48                                    -.96

USERS                                     644.38                              91.47

14 rows selected.

Enjoy     :-)