CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
Gaurav Kumar
Gaurav Kumar

Reputation: 21

How to convert .dmp file to .sql file in pl/sql

I'm not able to convert .dmp file to .sql.

Database username=chartindia
Database password=gaurav12

Code I'm using:

exp chartindia file=D:\\chartindia2020.dmp

I'm new in pl/sql.

Upvotes: 2

Views: 16567

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

To extract the DDLs of a DMP file created with Datapump, you can use the import utility with the option sqlfile

Example

impdp directory=yourdirectory dumpfile=yourdumpfile sqlfile=mysqlscript.sql

However, take this into account

  • The original DMP file must be created using Datapump expdp
  • The SQLFILE only retrieves DDL operations inside the dumpfile. It does not write any DML script to the sqlfile.

You can't convert, as far as I know, an original dmp file created with exp ( old utility ) to a sqlfile, not even for the ddls.

Update

Well, if I remember correctly, in my old times of 9i, Oracle Support has an internal utility called DUL or DataUnLoad, which was able to read binary files or database datafiles to extract data. But this is an unofficial tool and only handled by Oracle Support.

Example

Imagine I want to get the DDLS of objects in one schema

SQL> select object_name, object_type from dba_objects where owner = 'TEST_PERF' order by object_type, object_name ;

    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ ------------------------------
    SYS_IL0001593266C00001$$       INDEX
    SYS_IL0001595139C00001$$       INDEX
    SYS_IL0001595139C00002$$       INDEX
    SYS_LOB0001593266C00001$$      LOB
    SYS_LOB0001595134C00001$$      LOB
    SYS_LOB0001595134C00002$$      LOB
    SYS_LOB0001595139C00002$$      LOB
    WHATEVER_I_WANT_HERE           LOB
    CARDEALERSHIP_OFFERS           TABLE
    EMP                            TABLE
    EXAMPLE_CUSTOMERS              TABLE
    
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ ------------------------------
    MAX_INTERVAL                   TABLE
    MY_MERGE_TEST                  TABLE
    SEQUENCES_IDS                  TABLE
    T                              TABLE
    T1                             TABLE
    T2                             TABLE
    TABLE_A                        TABLE
    TABLE_A_BKP                    TABLE
    TABLE_B                        TABLE
    TABLE_B_BKP                    TABLE
    TABLE_C                        TABLE
    
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ ------------------------------
    TEST_PERFORMANCE               TABLE
    TEST_PERFORMANCE_2             TABLE
    X                              TABLE
    XXSAMPLE2                      TABLE
    T1_TRG                         TRIGGER
    COL_TAB_A                      TYPE
    
    28 rows selected.

You have first ( if you don't have it ) to create a database directory

 SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS '/mypath' ;

 SQL> GRANT READ,WRITE ON DIRECTORY MY_DIR to MY_SCHEMA ;

Then

$ expdp directory=DIR_DATAPUMP_EXPORT dumpfile=mytestfile.dmp schemas=test_perf

Export: Release 12.2.0.1.0 - Production on Thu Jul 30 13:26:51 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DIR_DATAPUMP_EXPORT dumpfile=mytestfile.dmp schemas=test_perf
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "TEST_PERF"."TEST_PERFORMANCE"              1.044 GB 20000000 rows
. . exported "TEST_PERF"."EXAMPLE_CUSTOMERS"             1.918 GB 10000000 rows
. . exported "TEST_PERF"."CARDEALERSHIP_OFFERS"              0 KB       0 rows
. . exported "TEST_PERF"."EMP"                               0 KB       0 rows
. . exported "TEST_PERF"."MAX_INTERVAL"                  5.546 KB       3 rows
. . exported "TEST_PERF"."MY_MERGE_TEST"                 6.414 KB       5 rows
. . exported "TEST_PERF"."SEQUENCES_IDS"                 5.539 KB       3 rows
. . exported "TEST_PERF"."T"                                 0 KB       0 rows
. . exported "TEST_PERF"."T1"                            5.484 KB       1 rows
. . exported "TEST_PERF"."T2"                            5.492 KB       2 rows
. . exported "TEST_PERF"."TABLE_A"                       6.585 KB      10 rows
. . exported "TEST_PERF"."TABLE_A_BKP"                   6.585 KB      10 rows
. . exported "TEST_PERF"."TABLE_B"                       8.171 KB      15 rows
. . exported "TEST_PERF"."TABLE_B_BKP"                   7.921 KB       8 rows
. . exported "TEST_PERF"."TABLE_C"                       5.507 KB       3 rows
. . exported "TEST_PERF"."TEST_PERFORMANCE_2"                0 KB       0 rows
. . exported "TEST_PERF"."X"                                 0 KB       0 rows
. . exported "TEST_PERF"."XXSAMPLE2"                     11.43 KB      44 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /orabatch/ftpcpl/export/mytestfile.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jul 30 13:28:39 2020 elapsed 0 00:01:42

Finally, to get the ddl script

 $ impdp directory=DIR_DATAPUMP_EXPORT dumpfile=mytestfile.dmp sqlfile=mytestfile.sql

Import: Release 12.2.0.1.0 - Production on Thu Jul 30 14:11:59 2020

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA directory=DIR_DATAPUMP_EXPORT dumpfile=mytestfile.dmp sqlfile=mytestfile.sql
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Jul 30 14:12:06 2020 elapsed 0 00:00:03

To verify the conent

$ cat mytestfile.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "TEST_PERF" IDENTIFIED BY VALUES 'S:7240E0356A595A26923D93A85C08CDFAC961811B13A667AD7F8D7AA98FD7;T:8BC68AE777A63439590B5871BBFEDA134221768DB740D7F1F2B258DC982E4FAA5DB047C0978616BE5759450BE8A7876D65F4B6FCA649779932561541F9F52D40158F82211689BA1033A8314A8968F798;589595E2214F485A'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP_GROUP";

 ALTER USER "TEST_PERF" LOCAL TEMPORARY TABLESPACE "TEMP_GROUP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE TABLE TO "TEST_PERF";
GRANT UNLIMITED TABLESPACE TO "TEST_PERF";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "CONNECT" TO "TEST_PERF";
 GRANT "DBA" TO "TEST_PERF";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "TEST_PERF" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/TABLESPACE_QUOTA
DECLARE
  TEMP_COUNT NUMBER;
  SQLSTR VARCHAR2(200);
BEGIN
  SQLSTR := 'ALTER USER "TEST_PERF" QUOTA UNLIMITED ON "TBTEST"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
              WHERE TABLESPACE_NAME = ''TBTEST'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN;
      ELSE RAISE;
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/
-- new object type path: SCHEMA_EXPORT/PASSWORD_HISTORY
-- CONNECT SYS
 DECLARE
     SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS;
     HIST_REC HIST_RECORD;
     i number := 0;
 BEGIN
      SYS.DBMS_PSWMG_IMPORT.PURGE_HISTORY('TEST_PERF');
      i := i+1;
      HIST_REC(i).USERNAME := 'TEST_PERF';
      HIST_REC(i).PASSWORD := 'T:8BC68AE777A63439590B5871BBFEDA134221768DB740D7F1F2B258DC982E4FAA5DB047C0978616BE5759450BE8A7876D65F4B6FCA649779932561541F9F52D40158F82211689BA1033A8314A8968F798';
      HIST_REC(i).PASSWD_DATE := '2020/07/25 00:19:28';

      SYS.DBMS_PSWMG_IMPORT.IMPORT_HISTORY(HIST_REC,i);
 END;
/
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT TEST_PERF

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ODCGRC1R', inst_scn=>'13141920867965');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC
-- CONNECT SYS
CREATE EDITIONABLE TYPE "TEST_PERF"."COL_TAB_A"
  OID 'AB845F3E572AC77DE0532A8016B4C00C' as object
(
 PRODUCT_ID        VARCHAR2(30 CHAR),
 STATE             VARCHAR2(30 CHAR),
 ZIP_CD            VARCHAR2(30 CHAR),
 VALID_FROM        DATE             ,
 VALID_TO          DATE             ,
 LATEST_FLAG       VARCHAR2(1 CHAR)
);
/

ALTER TYPE "TEST_PERF"."COL_TAB_A"
  COMPILE SPECIFICATION
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'  NLS_LENGTH_SEMANTICS= CHAR
  REUSE SETTINGS TIMESTAMP '2020-07-28 19:00:10'
/
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "TEST_PERF"."TEST_PERFORMANCE"
   (    "ID" VARCHAR2(20 CHAR),
        "DATE_ID" VARCHAR2(12 CHAR),
        "INSTANCE_ID" VARCHAR2(20 CHAR),
        "TERRITORY_ID" VARCHAR2(10 CHAR),
        "CODE" VARCHAR2(10 CHAR),
        "PRICE" VARCHAR2(10 CHAR),
        "CURRENCY_CODE_ID" VARCHAR2(20 CHAR),
        "PRICE_IN_USD" VARCHAR2(10 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED NOLOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBTEST"
  INMEMORY PRIORITY HIGH MEMCOMPRESS FOR QUERY LOW
  DISTRIBUTE AUTO NO DUPLICATE
  PARALLEL  ENABLE ROW MOVEMENT ;
CREATE TABLE "TEST_PERF"."TABLE_A"
   (    "PRODUCT_ID" VARCHAR2(30 CHAR),
        "STATE" VARCHAR2(30 CHAR),
        "ZIP_CD" VARCHAR2(30 CHAR),
        "MODIFIED_DT" DATE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."TABLE_B_BKP"
   (    "SEQUENCE_KEY" NUMBER,
        "PRODUCT_ID" VARCHAR2(30 CHAR),
        "STATE" VARCHAR2(30 CHAR),
        "ZIP_CD" VARCHAR2(30 CHAR),
        "VALID_FROM" DATE,
        "VALID_TO" DATE,
        "LATEST_FLAG" VARCHAR2(1 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."EXAMPLE_CUSTOMERS"
   (    "C1" VARCHAR2(120 CHAR),
        "C2" VARCHAR2(120 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 COMPRESS BASIC NOLOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBTEST" ;
CREATE TABLE "TEST_PERF"."TABLE_A_BKP"
   (    "PRODUCT_ID" VARCHAR2(30 CHAR),
        "STATE" VARCHAR2(30 CHAR),
        "ZIP_CD" VARCHAR2(30 CHAR),
        "MODIFIED_DT" DATE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."CARDEALERSHIP_OFFERS"
   (    "OFFER_ID" NUMBER,
        "CAR_ID" NUMBER,
        "USERNAME" VARCHAR2(20 CHAR),
        "STATUS" VARCHAR2(20 CHAR)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."TABLE_B"
   (    "SEQUENCE_KEY" NUMBER,
        "PRODUCT_ID" VARCHAR2(30 CHAR),
        "STATE" VARCHAR2(30 CHAR),
        "ZIP_CD" VARCHAR2(30 CHAR),
        "VALID_FROM" DATE,
        "VALID_TO" DATE,
        "LATEST_FLAG" VARCHAR2(1 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."TABLE_C"
   (    "C1" NUMBER,
        "C2" VARCHAR2(1 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."T2"
   (    "C1" NUMBER,
        "C2" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."XXSAMPLE2"
   (    "JG_INFO_V1" VARCHAR2(240 BYTE),
        "JG_INFO_V14" VARCHAR2(150 BYTE),
        "JG_INFO_V16" VARCHAR2(150 BYTE),
        "JG_INFO_V21" VARCHAR2(150 BYTE),
        "JG_INFO_V32" VARCHAR2(1996 BYTE),
        "JG_INFO_N3" NUMBER,
        "JG_INFO_N4" NUMBER,
        "JG_INFO_N11" NUMBER,
        "JG_INFO_N15" NUMBER,
        "JG_INFO_N30" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."EMP"
   (    "ENAME" VARCHAR2(10 CHAR),
        "SAL" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."TEST_PERFORMANCE_2"
   (    "ID" NUMBER,
        "DATE_ID" DATE,
        "INSTANCE_ID" NUMBER,
        "TERRITORY_ID" VARCHAR2(8 CHAR),
        "CODE" VARCHAR2(9 CHAR),
        "PRICE" NUMBER,
        "CURRENCY_CODE_ID" VARCHAR2(5 CHAR),
        "PRICE_IN_USD" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."MY_MERGE_TEST"
   (    "PK_ID" NUMBER,
        "DATA_TYPE_ID" NUMBER,
        "CONTENT_ID" NUMBER,
        "VALUE" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."T1"
   (    "C1" NUMBER,
        "C2" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."MAX_INTERVAL"
   (    "NEW_VALID_TO" DATE,
        "PRODUCT_ID" VARCHAR2(30 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."SEQUENCES_IDS"
   (    "MAX_SEQ" NUMBER,
        "PRODUCT_ID" VARCHAR2(30 CHAR)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
CREATE TABLE "TEST_PERF"."T"
   (    "C1" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("C1") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES ) ;
CREATE TABLE "TEST_PERF"."X"
   (    "C1" CLOB,
        "C2" BLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("C1") STORE AS SECUREFILE "WHATEVER_I_WANT_HERE"(
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES )
 LOB ("C2") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES ) ;
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
-- CONNECT TEST_PERF
CREATE EDITIONABLE TRIGGER t1_trg after update on t1
referencing new as new old as old
for each row
begin
    if :new.c1 != :old.c1 or :new.c2 != :old.c2
    then
       update t2 set c1=:new.c1 , c2=:new.c2 where c1=:old.c1 or c2=:old.c2 ;
    end if;
end;
/

ALTER TRIGGER "TEST_PERF"."T1_TRG" ENABLE;

ALTER TRIGGER "TEST_PERF"."T1_TRG"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER

Upvotes: 5

Littlefoot
Littlefoot

Reputation: 142733

I don't think that you can do that.

DMP extension - in Oracle world - represents files which are result of export operation (either by the original EXP utility, or modern Export Data Pump). Whichever it is, file isn't a textual file and you can't just read it and convert it to a .SQL. Besides, what kind of a .SQL do you have on mind? INSERT statements? CREATE this and that?

If you want to import contents of the DMP file into the database, you'd use IMP utility (or Import Data Pump). That's the correct way to do that.

PL/SQL has nothing to do with that operation.

Upvotes: 2

Related Questions