Context Switches and Performance issue In PL/SQL With BULK COLLECT Statement.

Context Switches:- In a PL/SQL program contain SQL and PL/SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When Pl/SQL engine find SQL Statement it stop own works and pass SQL statement to SQL engine. The SQL engine execute SQL Statement and return back to PL/SQL engine. This types of transferring are called Context Switching.

Example :-
SQL> CONN RND/rnd
Connected.
SQL>
SQL> SPOOL D:\BULK_COLLECT.TXT
SQL>
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> CREATE TABLE EMP
  2  (
  3    EMPLOYEE_ID     NUMBER(6),
  4    FIRST_NAME      VARCHAR2(20 BYTE),
  5    LAST_NAME       VARCHAR2(25 BYTE)             NOT NULL,
  6    EMAIL           VARCHAR2(25 BYTE)             NOT NULL,
  7    PHONE_NUMBER    VARCHAR2(20 BYTE),
  8    HIRE_DATE       DATE                          NOT NULL,
  9    JOB_ID          VARCHAR2(10 BYTE)             NOT NULL,
 10    SALARY          NUMBER(8,2),
 11    COMMISSION_PCT  NUMBER(2,2),
 12    MANAGER_ID      NUMBER(6),
 13    DEPARTMENT_ID   NUMBER(4)
 14  );

Table created.

SQL> CREATE TABLE E AS SELECT * FROM EMP;

Table created.

SQL>
SQL> Insert into E
  2     (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
  3   Values
  4     (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822',
  5      TO_DATE('05/23/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'SH_CLERK', 3000, 124, 50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO E
  2  SELECT * FROM E;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /

65536 rows created.

SQL> /

131072 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE PROCEDURE RND.DPR_BULK_DATA_TEST (P_DEPT_ID NUMBER)
  2  IS
  3     V_SQL          VARCHAR2 (3000);
  4
  5     TYPE TYP_EMP_ALL IS TABLE OF EMP%ROWTYPE;
  6
  7     REC_EMP_ALL    TYP_EMP_ALL;
  8     V_START_TIME   NUMBER;
  9     V_END_TIME     NUMBER;
 10  BEGIN
 11     V_SQL :=
 12        'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
 13                FROM E
 14                WHERE DEPARTMENT_ID=:2';
 15
 16     EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO REC_EMP_ALL USING P_DEPT_ID;
 17
 18     V_START_TIME := DBMS_UTILITY.GET_CPU_TIME;
 19
 20     FOR I IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 21     LOOP
 22        INSERT INTO EMP (EMPLOYEE_ID,
 23                         FIRST_NAME,
 24                         LAST_NAME,
 25                         EMAIL,
 26                         PHONE_NUMBER,
 27                         HIRE_DATE,
 28                         JOB_ID,
 29                         SALARY,
 30                         COMMISSION_PCT,
 31                         MANAGER_ID,
 32                         DEPARTMENT_ID)
 33             VALUES (REC_EMP_ALL (I).EMPLOYEE_ID,
 34                     REC_EMP_ALL (I).FIRST_NAME,
 35                     REC_EMP_ALL (I).LAST_NAME,
 36                     REC_EMP_ALL (I).EMAIL,
 37                     REC_EMP_ALL (I).PHONE_NUMBER,
 38                     REC_EMP_ALL (I).HIRE_DATE,
 39                     REC_EMP_ALL (I).JOB_ID,
 40                     REC_EMP_ALL (I).SALARY,
 41                     REC_EMP_ALL (I).COMMISSION_PCT,
 42                     REC_EMP_ALL (I).MANAGER_ID,
 43                     REC_EMP_ALL (I).DEPARTMENT_ID);
 44     END LOOP;
 45
 46     V_END_TIME := DBMS_UTILITY.GET_CPU_TIME;
 47
 48     DBMS_OUTPUT.
 49      PUT_LINE ('Executing Time Using Loop ' ||TO_CHAR( V_END_TIME-V_START_TIME));
 50     V_START_TIME := DBMS_UTILITY.GET_CPU_TIME;
 51
 52     FORALL J IN REC_EMP_ALL.FIRST .. REC_EMP_ALL.LAST
 53        INSERT INTO EMP (EMPLOYEE_ID,
 54                         FIRST_NAME,
 55                         LAST_NAME,
 56                         EMAIL,
 57                         PHONE_NUMBER,
 58                         HIRE_DATE,
 59                         JOB_ID,
 60                         SALARY,
 61                         COMMISSION_PCT,
 62                         MANAGER_ID,
 63                         DEPARTMENT_ID)
 64             VALUES (REC_EMP_ALL (J).EMPLOYEE_ID,
 65                     REC_EMP_ALL (J).FIRST_NAME,
 66                     REC_EMP_ALL (J).LAST_NAME,
 67                     REC_EMP_ALL (J).EMAIL,
 68                     REC_EMP_ALL (J).PHONE_NUMBER,
 69                     REC_EMP_ALL (J).HIRE_DATE,
 70                     REC_EMP_ALL (J).JOB_ID,
 71                     REC_EMP_ALL (J).SALARY,
 72                     REC_EMP_ALL (J).COMMISSION_PCT,
 73                     REC_EMP_ALL (J).MANAGER_ID,
 74                     REC_EMP_ALL (J).DEPARTMENT_ID);
 75
 76     V_END_TIME := DBMS_UTILITY.GET_CPU_TIME;
 77
 78     DBMS_OUTPUT.
 79      PUT_LINE (
 80        'Executing Time Using BULK COLLECT ' ||TO_CHAR(V_END_TIME-V_START_TIME));
 81     COMMIT;
 82  END;
 83  /

Procedure created.

SQL> EXEC DPR_BULK_DATA_TEST(50);
Executing Time Using Loop 5327
Executing Time Using BULK COLLECT 80

PL/SQL procedure successfully completed.

SQL>

Find out database name and database size .....

Using this query you can find database name, server name and database total size in mega byte ....


SQL> SET LINE 2000;
SQL>
SQL>   SELECT D.NAME, I.HOST_NAME, ROUND (SUM (F.BYTES) / 1048576) MB
  2      FROM V$DATABASE D
  3           CROSS JOIN V$INSTANCE I
  4           CROSS JOIN V$DATAFILE F
  5  GROUP BY D.NAME, I.HOST_NAME;

NAME      HOST_NAME                                                                MB
--------- ---------------------------------------------------------------- ----------
ORCL      N1020                                                                 39685

SQL>

SQL Injection technique

SQL Injection technique:

An SQL Injection attack is a form of attack where the attacker inputs extra SQL in an application. Here is a simple but illustrative example. If you have an APEX Report based on a SQL Query, like this Select:

SELECT CUSTOMER_NAME, BALANCE FROM CUSTOMER_INFO WHERE CUSTOMER_ID=’&P1_CUSTOMER_ID’
P1_CUSTOMER_ID is a text item that the user can input. If the user inputs a valid id one row is displayed, but if the user instead input this:
10 or 1=1
All rows will be displayed, which might not be the intention. You can change the Select to use bind variables like this:
RETURN 'SELECT CUSTOMER_NAME, BALANCE FROM CUSTOMER_INFO WHERE CUSTOMER_ID=:P1_CUSTOMER_ID';

Export table data with where condition.

It is allow to export specific rows from an table using oracle data pump just using QUERY Parameter. Like below ............

expdp rnd/rnd@orcl TABLES=DEPT query=\"where DEPARTMENT_ID=10\" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG


C:\Users\rajib.pradhan>expdp rnd/rnd@orcl TABLES=DEPT query=\"where DEPARTMENT_ID=10\" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

Export: Release 11.2.0.1.0 - Production on Sun Jun 29 18:24:05 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RND"."SYS_EXPORT_TABLE_01":  rnd/********@orcl TABLES=DEPT query="where DEPARTMENT_ID=10" DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "RND"."DEPT"                                6.390 KB       1 rows
Master table "RND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RND.SYS_EXPORT_TABLE_01 is:
  D:\DUMP\EXP_EMP_DEPT.DMP
Job "RND"."SYS_EXPORT_TABLE_01" successfully completed at 18:24:24

Find and Move Tablespace Using by an Table.

Finding Tablespace used by tables....

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
  2  FROM DBA_TABLES
  3  WHERE TABLE_NAME ='EMP'
  4  AND OWNER IN('RND');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS


Moving Tablespace ......

SQL> ALTER TABLE RND.EMP
  2  MOVE TABLESPACE DATA;

Table altered.

Finding Tablespace used by tables....

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
  2  FROM DBA_TABLES
  3  WHERE TABLE_NAME ='EMP'
  4  AND OWNER IN('RND');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            DATA

Writing scripts for all table move
[N.B:- Should be run so much carefully ]

SQL> SELECT  'ALTER TABLE '||TABLE_NAME||CHR(10)|| 'MOVE TABLESPACE '||TABLESPACE_NAME
  2  FROM DBA_TABLES
  3  WHERE OWNER IN(USER);

'ALTERTABLE'||TABLE_NAME||CHR(10)||'MOVETABLESPACE'||TABLESPACE_NAME
-----------------------------------------------------------------------------------------
ALTER TABLE FLASHBACK_DATABASE_TEST
MOVE TABLESPACE USERS

ALTER TABLE D
MOVE TABLESPACE USERS

ALTER TABLE PLSQL_PROFILER_RUNS
MOVE TABLESPACE USERS

ALTER TABLE PLSQL_PROFILER_UNITS
MOVE TABLESPACE USERS

Partitioning an Existing Table in Oracle Database Using DBMS_REDEFINITION Package

SQL>
SQL> CREATE TABLE MAIN_TABLE
  2  (ACC_NUM NUMBER(14) CONSTRAINT PK_MAIN_TABLE_ACC_NUM PRIMARY KEY,
  3  ACC_TITLE VARCHAR2(300),
  4  OPPENING_DATE DATE
  5  );

Table created.

SQL> BEGIN
  2  FOR I IN 1.. 100000 LOOP
  3  INSERT INTO MAIN_TABLE(ACC_NUM, ACC_TITLE, OPPENING_DATE)
  4  VALUES (I, 'ACC TITLE '||I, TRUNC(SYSDATE)-I);
  5  END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL>
SQL> CREATE TABLE PART_TABLE
  2  (ACC_NUM NUMBER(14) CONSTRAINT PK_PART_TABLE_ACC_NUM PRIMARY KEY,
  3  ACC_TITLE VARCHAR2(300),
  4  OPPENING_DATE DATE
  5  )
  6  TABLESPACE DATA
  7  PARTITION BY RANGE  (OPPENING_DATE)
  8  (
  9  PARTITION ACC_MON2014_JAN VALUES LESS THAN (TO_DATE('31/01/2014', 'DD/MM/YYYY')),
 10  PARTITION ACC_MON2014_FEB VALUES LESS THAN (TO_DATE('28/02/2014', 'DD/MM/YYYY')),
 11  PARTITION ACC_MON2014_MAR VALUES LESS THAN (TO_DATE('31/03/2014', 'DD/MM/YYYY')),
 12  PARTITION ACC_MON2014_APR VALUES LESS THAN (TO_DATE('30/04/2014', 'DD/MM/YYYY')),
 13  PARTITION ACC_MON2014_MAY VALUES LESS THAN (TO_DATE('31/05/2014', 'DD/MM/YYYY')),
 14  PARTITION ACC_MON2014_JUN VALUES LESS THAN (TO_DATE('30/06/2014', 'DD/MM/YYYY')),
 15  PARTITION ACC_MON2014_JUL VALUES LESS THAN (TO_DATE('31/07/2014', 'DD/MM/YYYY')),
 16  PARTITION ACC_MON2014_AUG VALUES LESS THAN (TO_DATE('31/08/2014', 'DD/MM/YYYY')),
 17  PARTITION ACC_MON2014_SEP VALUES LESS THAN (TO_DATE('30/09/2014', 'DD/MM/YYYY')),
 18  PARTITION ACC_MON2014_OCT VALUES LESS THAN (TO_DATE('31/10/2014', 'DD/MM/YYYY')),
 19  PARTITION ACC_MON2014_NOV VALUES LESS THAN (TO_DATE('30/11/2014', 'DD/MM/YYYY')),
 20  PARTITION ACC_MON2014_DEC VALUES LESS THAN (TO_DATE('31/12/2014', 'DD/MM/YYYY'))
 21  );

Table created.

SQL> EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'MAIN_TABLE');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_REDEFINITION.start_redef_table(
  3      uname      => USER,
  4      orig_table => 'MAIN_TABLE',
  5      int_table  => 'PART_TABLE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'PART_TABLE', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    dbms_redefinition.finish_redef_table(
  3      uname      => USER,
  4      orig_table => 'MAIN_TABLE',
  5      int_table  => 'PART_TABLE');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME='MAIN_TABLE';

TABLE_NAME                     COMPRESS COMPRESS_FOR                          
------------------------------ -------- ------------                          
MAIN_TABLE                                                                    

SQL> SET LINE 2000
SQL> /

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR          
------------------------------ ------------------------------ -------- ------------                                      
MAIN_TABLE                     ACC_MON2014_JAN                DISABLED                                               MAIN_TABLE                     ACC_MON2014_FEB                DISABLED                                             MAIN_TABLE                     ACC_MON2014_MAR                DISABLED                                             MAIN_TABLE                     ACC_MON2014_APR                DISABLED                                             MAIN_TABLE                     ACC_MON2014_MAY                DISABLED                                             MAIN_TABLE                     ACC_MON2014_JUN                DISABLED                                               MAIN_TABLE                     ACC_MON2014_JUL                DISABLED                                               MAIN_TABLE                     ACC_MON2014_AUG                DISABLED                                             MAIN_TABLE                     ACC_MON2014_SEP                DISABLED                                               MAIN_TABLE                     ACC_MON2014_OCT                DISABLED                                             MAIN_TABLE                     ACC_MON2014_NOV                DISABLED                                             MAIN_TABLE                     ACC_MON2014_DEC                DISABLED                                             12 rows selected.

SQL> SELECT 'ALTER TABLE MAIN_TABLE MODIFY PARTITION '||PARTITION_NAME||' COMPRESS FOR ALL OPERATIONS;'
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME='MAIN_TABLE';

'ALTERTABLEMAIN_TABLEMODIFYPARTITION'||PARTITION_NAME||'COMPRESSFORALLOPERATIONS;'                                                                                                                                             --------------------------------------------------------------------------------------------------                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JAN COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_FEB COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAR COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_APR COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAY COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUN COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUL COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_AUG COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_SEP COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_OCT COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_NOV COMPRESS FOR ALL OPERATIONS;                                                                                                                                 ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_DEC COMPRESS FOR ALL OPERATIONS;                                                                                                                                 12 rows selected.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JAN COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_FEB COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAR COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_APR COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_MAY COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUN COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_JUL COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_AUG COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_SEP COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_OCT COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_NOV COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> ALTER TABLE MAIN_TABLE MODIFY PARTITION ACC_MON2014_DEC COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL>
SQL>
SQL> SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION, COMPRESS_FOR
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME='MAIN_TABLE';

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR             ------------------------------ ------------------------------ -------- ------------                                         MAIN_TABLE                     ACC_MON2014_JAN                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_FEB                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_MAR                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_APR                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_MAY                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_JUN                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_JUL                ENABLED  OLTP                                     MAIN_TABLE                     ACC_MON2014_AUG                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_SEP                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_OCT                ENABLED  OLTP                                   MAIN_TABLE                     ACC_MON2014_NOV                ENABLED  OLTP                                 MAIN_TABLE                     ACC_MON2014_DEC                ENABLED  OLTP                                                                                                                                                                                                    
12 rows selected.

SQL> SPOOL OFF;

Table Exports/Imports Using Oracle Data Pump

1. You need to create directory (If you already created no need to create) from sys schema.

Example :
CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

2. You need to given read & write permeation to specific user which you want to export. 
GRANT READ, WRITE ON DIRECTORY DATA_DIR TO RND;

3. Now you can export list of tables using data pump

You need to connect specific schema which tables you want to export ..

For export

expdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

For import (TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.)

impdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG


C:\Users\rajib.pradhan>expdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG

Export: Release 11.2.0.1.0 - Production on Sun Jun 29 16:56:41 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RND"."SYS_EXPORT_TABLE_01":  rnd/********@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=EXP_EMP_DEPT.LOG
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "RND"."DEPT"                                7.023 KB      28 rows
. . exported "RND"."EMP"                                 16.83 KB     107 rows
Master table "RND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RND.SYS_EXPORT_TABLE_01 is:
  D:\DUMP\EXP_EMP_DEPT.DMP
Job "RND"."SYS_EXPORT_TABLE_01" successfully completed at 16:57:12


C:\Users\rajib.pradhan>impdp rnd/rnd@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG

Import: Release 11.2.0.1.0 - Production on Sun Jun 29 16:58:29 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RND"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RND"."SYS_IMPORT_TABLE_01":  rnd/********@orcl TABLES=EMP,DEPT DIRECTORY=DATA_DIR TABLE_EXISTS_ACTION=APPEND DUMPFILE=EXP_EMP_DEPT.DMP LOGFILE=IMP_EMP_DEPT.LOG
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "RND"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-39152: Table "RND"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RND"."DEPT"                                7.023 KB      28 rows
. . imported "RND"."EMP"                                 16.83 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "RND"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 16:58:35

How to find object lock session ..

Using this query you can find all locking session


SELECT SID  FROM V$LOCK
 WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
 WHERE OWNER='RND'
 AND OBJECT_NAME='DEPT');

Archive log directory going full

Archive log directory going full, what needs to   , only 6% available

This is for your information archive log file are using for backup & recovery database using RMAN. Oracle database track all of change log in log files (also called redo log files). When log file are switch then this log file send to archive location (This file called archive log file).

There are more than one solution for this problem ..
1. If you have available disk space in your hard drive then you can add another destination. Database will try to put logs on both destinations, but if one of them is full it will fail and will put it only in the second destination. This is not a problem.

First you need to see current archive destination and size. You can monitor your archive log files details from this dynamic performance view "V$ARCHIVED_LOG".

SQL> SHOW PARAMETER LOG_ARCHIVE_MIN_SUCCEED_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest         integer     1
SQL>
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\app\flash_recovery_area
db_recovery_file_dest_size           big integer 8000M

SQL> SELECT DEST_NAME,STATUS,DESTINATION FROM V$ARCHIVE_DEST;

DEST_NAME
                                                 STATUS    DESTINATION
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1
                                                 VALID     D:\APP\ORADATA\ARCHIVE\
LOG_ARCHIVE_DEST_2
                                                 INACTIVE
LOG_ARCHIVE_DEST_3
                                                 INACTIVE
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=D:\app\oradata\ARCHIVE2' SCOPE=BOTH;

SYSTEM ALTERED.

SQL> SELECT DEST_NAME,STATUS,DESTINATION FROM V$ARCHIVE_DEST;

DEST_NAME
                                                 STATUS    DESTINATION
------------------------------------------------------------------------------------------
-------------------------------------------------------- --------- -----------------------
------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1
                                                 VALID     D:\APP\ORADATA\ARCHIVE\
LOG_ARCHIVE_DEST_2
                                                 VALID     D:\APP\ORADATA\ARCHIVE2

2. You can manually cut and past to another location all of archive log files. You must remember that if your archive log file backup are not complete by RMAN then you need to put all of this file in same location.
3. You can take backup by using RMAN and remove archive log list.

ORA-16179: INCREMENTAL CHANGES TO "log_archive_dest_2" NOT ALLOWED WITH SPFILE

Cause :- Modifying log_archive_dest_2 will return this error for invalid syntax. This parameter is dynamic and can be changed using the ALTER SYSTEM/ALTER SESSION statement.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='D:\app\oradata\ARCHIVE2' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='D:\app\oradata\ARCHIVE2' SCOPE=BOTH
*
ERROR AT LINE 1:
ORA-32017: FAILURE IN UPDATING SPFILE
ORA-16179: INCREMENTAL CHANGES TO "log_archive_dest_2" NOT ALLOWED WITH SPFILE

Correct Syntax is

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=D:\app\oradata\ARCHIVE2' SCOPE=BOTH;

SYSTEM ALTERED.

ORA-30926: unable to get a stable set of rows in the source tables

ORA-30926: unable to get a stable set of rows in the source tables

Cause :- This error occurred in MERGE statement when your ON Clause find duplicate record. This trying to update the same row of the target table multiple times

MERGE INTO D D
USING (SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPT) DP
ON (D.DEPARTMENT_ID=DP.DEPARTMENT_ID)
WHEN MATCHED
THEN
   UPDATE SET D.DEPARTMENT_NAME=DP.DEPARTMENT_NAME, D.MANAGER_ID=DP.MANAGER_ID, D.LOCATION_ID=DP.LOCATION_ID
WHEN NOT MATCHED THEN
INSERT (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (DP.DEPARTMENT_ID, DP.DEPARTMENT_NAME, DP.MANAGER_ID, DP.LOCATION_ID);