Export and Import Tables Whose Names are Starting From MMM using Oracle Data-Pump

Hi Guys,

In Oracle we can easily Export and Import tables with your desired Names.
You can see in below example I exported tables whose names are starting from MMM


E:\expbab> expdp directory=expbab dumpfile=MMM include=table:\"in\(select table_name from all_tables where table_name like \'MMM%\')\"


Export: Release 11.2.0.3.0 - Production on Wed Nov 30 14:33:56 2016

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

Username: HMIS
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HMIS"."SYS_EXPORT_SCHEMA_01":  HMIS/******** directory=expbab dumpfile=MMM include=table:"in\(select table_name from all_tables where table_name like \'MMM%\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 185.1 MB
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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HMIS"."MMMEDREPORT"                        39.59 MB    4976 rows
. . exported "HMIS"."MMMEDFILLOC"                        525.2 KB    5566 rows
. . exported "HMIS"."MMMDISSUM"                          18.32 MB    2644 rows
. . exported "HMIS"."MMMEDORD"                           13.41 MB   44904 rows
. . exported "HMIS"."MMMEDLOC"                           227.2 KB    2684 rows
. . exported "HMIS"."MMMEDREPORT2"                       103.5 KB      11 rows
. . exported "HMIS"."MMMETAFILE"                         40.16 KB       3 rows
. . exported "HMIS"."MMMESSAGE"                          6.335 KB      20 rows
. . exported "HMIS"."MMMLOCCD"                           6.210 KB       1 rows
. . exported "HMIS"."MMM"                                    0 KB       0 rows
. . exported "HMIS"."MMMEDRQST"                              0 KB       0 rows
. . exported "HMIS"."MMMEDSTAT"                              0 KB       0 rows
. . exported "HMIS"."MMMEDTEAM"                              0 KB       0 rows
. . exported "HMIS"."MMMOHCPT"                               0 KB       0 rows
. . exported "HMIS"."MMMRGCAS"                               0 KB       0 rows
Master table "HMIS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HMIS.SYS_EXPORT_SCHEMA_01 is:
  E:\EXPBAB\MMM.DMP
Job "HMIS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:34:19

• Now i will import these tables in SAM schema.

E:\impbab> impdp dumpfile=MMM.DMP directory=impbab remap_schema=hmis:sam

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 14:37:58 2016

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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/******** dumpfile=MMM.DMP directory=impbab remap_schema=hmis:sam
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SAM"."MMMEDREPORT"                         39.59 MB    4976 rows
. . imported "SAM"."MMMEDFILLOC"                         525.2 KB    5566 rows
. . imported "SAM"."MMMDISSUM"                           18.32 MB    2644 rows
. . imported "SAM"."MMMEDORD"                            13.41 MB   44904 rows
. . imported "SAM"."MMMEDLOC"                            227.2 KB    2684 rows
. . imported "SAM"."MMMEDREPORT2"                        103.5 KB      11 rows
. . imported "SAM"."MMMETAFILE"                          40.16 KB       3 rows
. . imported "SAM"."MMMESSAGE"                           6.335 KB      20 rows
. . imported "SAM"."MMMLOCCD"                            6.210 KB       1 rows
. . imported "SAM"."MMM"                                     0 KB       0 rows
. . imported "SAM"."MMMEDRQST"                               0 KB       0 rows
. . imported "SAM"."MMMEDSTAT"                               0 KB       0 rows
. . imported "SAM"."MMMEDTEAM"                               0 KB       0 rows
. . imported "SAM"."MMMOHCPT"                                0 KB       0 rows
. . imported "SAM"."MMMRGCAS"                                0 KB       0 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/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:38:25



Happy Learning ....

Dropping and Recreating DB-Link in Oracle

Hi Guy's,

Its very simple in oracle to drop and recreate the db-link pls find the steps.

Task :-

• Drop the existing db-link and recreate with the same name.

Steps :-

• First take out the DDL of existing db-link to get the db-link details like name of db-link,remote user,db-port,service name of remote database which you are connecting..
• When i extracted Metadata for db-link i found 4 db-links which are created in my database.
• I will drop 'EYES' db-link and recreate with the same name as per our requirement..

SQL> Set long 1000
SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;

DBMS_METADATA.GET_DDL('DB_LINK',DB.DB_LINK,DB.OWNER)
--------------------------------------------------------------------------------

  CREATE PUBLIC DATABASE LINK "DMS.COM"
   USING 'DMS'


  CREATE PUBLIC DATABASE LINK "EYES"
   CONNECT TO "FMIS" IDENTIFIED BY VALUES '06C652AFA6A72ADA2EFCFDF0850C4A2B817A8
F1EB7D9D01CC4C88940C63D492915BD2C9F6AECD7367C944154F377700E299604BB91A4E369497B9
6540C66A084BBD808BB7EB68C7956108A189BE969CB0D968F54C729FDA4EDF037204EDFF0F4D72E2
B4EC7F08657089E2CF64AB3FD4B374A63A7FB4C6EF66D53F62D22001C39'
   USING 'ORADB1.iecc.md:1521/HRLIVE'

  CREATE PUBLIC DATABASE LINK "MCC.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05CF926A55876BECA1276B20465CE75269BB3
45571ACAF806C'
   USING 'MCCDB'

  CREATE PUBLIC DATABASE LINK "MCCLOCAL.COM"
   CONNECT TO "HMIS" IDENTIFIED BY VALUES '05791082DCF9BBB5C0492C52008E0CFAF1'
   USING 'MCCLOCAL'

                                                                          *********
                                                               

 SQL> DROP PUBLIC DATABASE LINK eyes;

 SQL> CREATE PUBLIC DATABASE LINK EYES CONNECT TO fmis IDENTIFIED BY fmis
USING 'ORADB1.iecc.md:1521/HRLIVE';


Happy Learning...

ORA-00980: synonym translation is no longer valid

This can happen for many reasons. Some of them are:

1. You created a synonym on non-existing object by mistake. 
2. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing. 
3. You dropped an object but you did not drop the synonyms referencing the object.
4. You dropped a user, but you did not drop synonyms referencing the objects owned by that user. 
5. When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms:

• This script generates DDL to drop synonyms whose translation is no longer valid. 

SQL>  rem
           rem  Exludes SYS and SYSTEM users
           rem
           select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
           'SYNONYM'||s.owner||'.')||s.synonym_name||';'
           from dba_synonyms  s
           where table_owner not in('SYSTEM','SYS')
           and db_link is null
           and not exists (select  1 from dba_objects o
           where s.table_owner=o.owner and s.table_name=o.object_name)
            /


Happy Learning....

How to find all Configured Oracle Services in Windows Environment.

C:\Users> sc query state= all | find "SERVICE_NAME" | find "Oracle"

SERVICE_NAME: OracleJobSchedulerPROD
SERVICE_NAME: OracleJobSchedulerTEST
SERVICE_NAME: OracleMTSRecoveryService
SERVICE_NAME: OracleOraDb11g_home1ClrAgent
SERVICE_NAME: OracleServicePROD
SERVICE_NAME: OracleServiceTEST
SERVICE_NAME: OracleVssWriterPROD
SERVICE_NAME: OracleVssWriterTEST
SERVICE_NAME: OracleOraDb11g_home1TNSListener


C:\Users> sc qc OracleServicePROD

[SC] QueryServiceConfig SUCCESS

        SERVICE_NAME                : OracleServicePROD
        TYPE                                  : 10  WIN32_OWN_PROCESS
        START_TYPE                     : 2   AUTO_START
        ERROR_CONTROL           : 1   NORMAL
        BINARY_PATH_NAME        : e:\app\moin\product\11.2.0\dbhome_1\bin\ORACLE.EXE PROD
        LOAD_ORDER_GROUP    :
        TAG                                    : 0
        DISPLAY_NAME                 : OracleServicePROD
        DEPENDENCIES                :
        SERVICE_START_NAME    : LocalSystem


Happy Learning...