How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases

How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases

Lets now try to export tables from different schemas in Oracle 10g database on a Linux server.
[oracle@localhost ~]$ sqlplus / as sysdba

We are assuming that a1 and a2 are 2 schemas  with a common table t1
[oracle@sap1]> expdp ‘”/ as sysdba”‘ directory=DATAPUMPDEMO_exp
dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1                                                    

Export: Release 10.2.0.4.0 – 64bit Production on Thursday, 23 June, 2013 15:35:01

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00012: table mode exports only allow objects from one schema

As you can see you can not export tables from different schemas by using “tables” parameter in expdp in Oracle 10g.
What you should do is create a table and insert the names of the tables to be exported to this table and use the “schemas” and “include” parameter in expdp command.
A1@ORCL > create table expdp_tables (table_name varchar2(30));

Table created.

A1@ORCL > insert into expdp_tables values (‘T1′);

1 row created.

A1@ORCL > commit;

Commit complete.
You only need to insert the distinct names of all the tables into “expdp_tables” schema. Put the names of the all the distinct schemas whose tables will be exported in the “schemas” parameter.
In that method you export all the tables in “expdp_tables” table from both schemas, this means if a specific table name exist in both schemas and you only want to export one of them, you can not do that.
[oracle@sap1]> expdp ‘”/ as sysdba”‘ directory=UURAL_DATAPUMPDEMO
dumpfile=a1-a2_tables logfile=a1-a2_tables schemas=A1,A2
INCLUDE=TABLE:\”IN \(SELECT table_name FROM a1.expdp_tables\)\”              
Export: Release 10.2.0.4.0 – 64bit Production
Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  “/******** AS SYSDBA” directory=UURAL_DATAPUMPDEMO dumpfile=a1-a2_tables logfile=a1-a2_tables schemas=A1,A2 INCLUDE=TABLE:”IN (SELECT table_name FROM a1.expdp_tables)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
. . exported “A1″.”T1″                                 0 KB       0 rows
. . exported “A2″.”T1″                                 0 KB       0 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************

Lets now try to export tables from different schemas in Oracle 11g database on a Linux server.

[oracle@localhost ~]$ expdp ‘”/ as sysdba”‘ directory=DATA_PUMP_DIR dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1

Export: Release 11.2.0.2.0 – Production
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″:  “/******** AS SYSDBA” directory=DATA_PUMP_DIR dumpfile=a1-a2_tables logfile=a1-a2_tables tables=A1.T1,A2.T1
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “A1″.”T1″                                 0 KB       0 rows
. . exported “A2″.”T1″                                 0 KB       0 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/oracle/admin/orcl/dpdump/a1-a2_tables.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed
As you can see, in Oracle 11g, you can export tables from different schemas by using “tables” parameter in Export Data Pump Utility and this is not possible in Oracle 10g.

Oracle Tablespace High water mark

Oracle Tablespace High water mark

Tablespace High water mark
Tablespace HWM



col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
ANUJTEST        /opt/app/oracle/oradata/orcl/anujtest.dbf                 10      8        2
EXAMPLE         /opt/app/oracle/oradata/orcl/example01.dbf               100     81       19
PERFSTAT        /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf          1000    115      885
RMAN            /opt/app/oracle/oradata/orcl/rman.dbf                     50      7       43
SYSAUX          /opt/app/oracle/oradata/orcl/sysaux01.dbf                830    777       53
SYSTEM          /opt/app/oracle/oradata/orcl/system01.dbf                840    823       17
TSAPEXU         /opt/app/oracle/oradata/orcl/tsapexu01.dbf               110     97       13
UNDOTBR         /opt/app/oracle/oradata/orcl/undotbR.dbf                 500     46      454
USERS           /opt/app/oracle/oradata/orcl/users01.dbf                 609    576       33

9 rows selected.

RMAN Backup Optimization

From 11G R1 onwards only undo data blocks containing undo data for not yet committed --- or rolled back --- transactions are backed up. Undo data in the undo tablespace still required to satisfy the undo retention but only containing undo records for already committed transactions are not backed up anymore. Nice if you have undo tablespaces of multiple Gb !!!

If backup optimization is configured ( see below ) then
1. Data files belonging to read only tablespaces are not backed up all the time. It is not needed anymore to use the SKIP READONLY clause. ( Note that the SKIP READONLY could not be used for RMAN backups of standby databases. )
2. Data files belonging to tablespaces which were offlined normal are not backed up all the time. It is not needed anymore to use the SKIP TABLESPACE clause.
3. The archivelog sequence is not backed up all the time. This is nice if you keep a large amount of archived redo logs on disk for whatever reason ( Logminer, Streams, Data Guard )

How to configure backup optimization ?

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

What is the RMAN output when files are skipped during backup ?

RMAN> backup as compressed backupset database;

Starting backup at 23-JUN-09
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 4; already backed up 2 time(s)
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_SYSAUX_0MK30K1T_.DBF
input datafile file number=00003 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_UNDOTBS1_0NK30K84_.DBF
input datafile file number=00006 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_ARCHIVE_02_0PK30K9S_.DBF
input datafile file number=00009 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM2_4ZM8LTBW_.DBF
input datafile file number=00011 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_CATALOG_53ZL1X82_.DBF

TRIGGER TO GET SERVERERROR ON DATABASE

Lets create a trigger that will log all the errors in the database:

It is better to create this trigger under SYS or DBA user's so that you don't have to grant all permission if you have to grant permissions separately.

--------------------------------------------------------
--  DDL for Trigger AFTER_ERROR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "AFTER_ERROR"
    AFTER SERVERERROR ON DATABASE
DECLARE
    pragma autonomous_transaction;
    id NUMBER;
    sql_text ORA_NAME_LIST_T;
    v_stmt CLOB;
    n NUMBER;
BEGIN
    SELECT oraerror_seq.nextval INTO id FROM dual; --- Seq no's
    --
    n := ora_sql_txt(sql_text);
    --
    IF n >= 1
    THEN
        FOR i IN 1..n LOOP
            v_stmt := v_stmt || sql_text(i);
        END LOOP;
    END IF;
    --
    FOR n IN 1..ora_server_error_depth
    LOOP
       IF ( ora_server_error(n) in (  '6512','25228','25254')
          AND ora_login_User in ('SYSMAN','DBSNMP') )
       THEN
           -- ignore this error
           NULL;
       ELSE
           INSERT INTO oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(n), ora_server_error_msg(n),v_stmt);
           COMMIT;
       END IF;
    END LOOP;

/*  You can un comment this part to make this table auto clean on every insert. This will automatically cleanup the data in the table which is older than  30 days (you can change as per your requirement )
Delete from oraerror where log_date <=sysdate -30;
    commit; */
 --
END after_error;
/
ALTER TRIGGER "AFTER_ERROR" ENABLE;





Now create a seq needed for id generation :

--------------------------------------------------------
--  DDL for Sequence ORAERROR_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "ORAERROR_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1040 CACHE 20 NOORDER  NOCYCLE ;

Table to capture err :


 CREATE TABLE "ORAERROR"
   (    "ID" NUMBER,
    "LOG_DATE" DATE,
    "LOG_USR" VARCHAR2(30 BYTE),
    "TERMINAL" VARCHAR2(50 BYTE),
    "ERR_NR" NUMBER(10,0),
    "ERR_MSG" VARCHAR2(4000 BYTE),
    "STMT" CLOB
   )  TABLESPACE ) ;

Find index skewed, rebuild
                                                

Summary
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

The key column to decide index skewed is blevel. You must estimate statistics for the index or analyze index validate structure.

If the BLEVEL were to be more than 4, it is recommended to rebuild the index.

SELECT OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED, BLEVEL
FROM DBA_INDEXES 
WHERE OWNER NOT IN ('SYS', 'SYSTEM') 
AND BLEVEL >= 4
ORDER BY BLEVEL DESC;

Which SQL are doing a lot of disk I/O
                                                

Which SQL are doing a lot of disk I/O

SELECT * FROM 
   (SELECT  SUBSTR(sql_text,1,500) SQL,  
            ELAPSED_TIME, CPU_TIME, 
            disk_reads, executions,  
            disk_reads/executions "Reads/Exec", 
            hash_value,address   
     FROM V$SQLAREA   
     WHERE  ( hash_value, address ) IN (  
         SELECT DISTINCT HASH_VALUE, address 
           FROM v$sql_plan  
           WHERE DISTRIBUTION IS NOT NULL ) 
      AND disk_reads > 100
      AND executions > 0
     ORDER BY ELAPSED_TIME DESC) 
  WHERE ROWNUM <=30;

Disk I/O

Script

Datafiles Disk I/O

Tablespace Disk I/O

Which segments have top Logical I/O & Physical I/O

Which SQL are doing a lot of disk I/O