Resolving issues for Oracle_Home is not set for Toad even Oracle client is available

Most often after installing Toad developer use to face this problem while starting Toad for Oracle.

Resolution:
Install Oracle 32 bit client on your machine for the same OS if Toad is below version 11.

Install Oracle 64 bit client in your machine for the same OS if Toad version is atleast 11.0...

How to use external table to copy table data in a file to a defined directory location in Oracle

External table is sometimes very useful to copy table data based on condition into a file to a defined directory location in Oracle:

CREATE OR REPLACE DIRECTORY  DP_DIR AS  '/tmp/oracle/';


GRANT READ, WRITE ON DIRECTORY SYS.DP_DIR TO TAPADM WITH GRANT OPTION;

Below will create the file;


CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     LOCATION ('TAP_company_event_detail_xt.dmp')
   )
   AS select * from TAPADM.TAP_COMPANY_EVT_DETAIL where company_evt_detail_seq in(select COMPANY_EVT_DETAIL_SEQ from TAPADM.PTED_DIFF_1);

Now to copy the data to some other location at new DB from the above file we can use the below command or syntax.

CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
(
  COMPANY_EVENT_DETAIL_SEQ  VARCHAR2(255 CHAR),
  VERSION                   NUMBER(19),
  CREATOR_ID                VARCHAR2(30 CHAR),
  CREATED_DATE              TIMESTAMP(6),
  MODIFIER_ID               VARCHAR2(30 CHAR),
  MODIFIED_DATE             TIMESTAMP(6),
  REALM_ID                  NUMBER(19),
  EVENT_DETAIL_SUBTYPE      VARCHAR2(100 CHAR),
  COMPANY_FK                VARCHAR2(255 CHAR)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     ACCESS PARAMETERS 
       (  )
     LOCATION (DP_DIR:'TAP_company_event_detail_xt.dmp')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

Resolving PLS-00905: object GGATE.DDLAUX is invalid while installing Golden Gate in 11gR2

PLS-00905: object GGATE.DDLAUX is invalid


SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.











Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1464/9                                   PL/SQL: SQL Statement ignored
1466/25                                  PL/SQL: ORA-00942: table or view does not exist
1475/9                                   PL/SQL: SQL Statement ignored
1477/25                                  PL/SQL: ORA-00942: table or view does not exist
1489/9                                   PL/SQL: SQL Statement ignored
1491/25                                  PL/SQL: ORA-00942: table or view does not exist
1496/9                                   PL/SQL: SQL Statement ignored
1498/25                                  PL/SQL: ORA-00942: table or view does not exist
1503/9                                   PL/SQL: SQL Statement ignored
1505/25                                  PL/SQL: ORA-00942: table or view does not exist
1510/9                                   PL/SQL: SQL Statement ignored

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1512/25                                  PL/SQL: ORA-00942: table or view does not exist
1595/4                                   PL/SQL: SQL Statement ignored
1596/20                                  PL/SQL: ORA-00942: table or view does not exist
1598/4                                   PL/SQL: SQL Statement ignored
1599/20                                  PL/SQL: ORA-00942: table or view does not exist
1614/27                                  PL/SQL: ORA-00942: table or view does not exist
1614/4                                   PL/SQL: SQL Statement ignored
1616/27                                  PL/SQL: ORA-00942: table or view does not exist
1616/4                                   PL/SQL: SQL Statement ignored


DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist

DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
0/0                                      PL/SQL: Compilation unit analysis terminated
1/23                                     PLS-00304: cannot compile body of 'DDLAUX' without its
                                         specification

1/23                                     PLS-00905: object GGATE.DDLAUX is invalid


SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
FAILED: Table does not exist

DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist

DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist

DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist

DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist

DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist

DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist

GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist

GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
126/9                                    PL/SQL: SQL Statement ignored
128/25                                   PL/SQL: ORA-00942: table or view does not exist
133/23                                   PL/SQL: ORA-02289: sequence does not exist
133/5                                    PL/SQL: SQL Statement ignored
707/16                                   PLS-00905: object GGATE.DDLAUX is invalid
707/5                                    PL/SQL: Statement ignored
992/27                                   PL/SQL: ORA-00942: table or view does not exist
992/4                                    PL/SQL: SQL Statement ignored


DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist


FROM "GGATE" ."GGS_SETUP"
              *
ERROR at line 2:
ORA-00942: table or view does not exist



LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/pspqa03/pspqa03/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)

Script complete.
SQL>
SQL>
SQL>
SQL> grant create any table to ggs_user;
grant create any table to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
grant create any view to ggs_user;
SQL> grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any procedure to ggs_user;
grant create any procedure to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any sequence to ggs_user;
grant create any sequence to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any index to ggs_user;
grant create any index to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL> grant create any trigger to ggs_user;

grant create any trigger to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL> SQL> grant create any view to ggs_user;
grant create any view to ggs_user
*
ERROR at line 1:
ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation


SQL>
SQL>
SQL> @ddl_remove

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ggate
Working, please wait ...
Spooling to file ddl_remove_spool.txt

Script complete.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> grant create any table to ggate;

grant create any view to ggate;

grant create any procedure to ggate;

grant create any sequence to ggate;

grant create any index to ggate;

grant create any trigger to ggate;

grant create any view to ggate;
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>
Grant succeeded.

SQL> SQL>

Grant succeeded.

SQL>
SQL>
SQL>
SQL>
SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggate

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.











Using GGATE as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE

CLEAR_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

CREATE_TRACE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

TRACE_PUT_LINE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

INITIAL_SETUP STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/pspqa03/pspqa03/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

then run the other script:

SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggate
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO GGATE;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.