How to get the DDL from the export dump file in the Oracle Database on the Windows server

Rameshdba.com


Summary: In this tutorial, you will learn How to get the DDL from an export dump file in the Oracle Database Windows platform


Step 1: We need to confirm which dumpfile we are going to take ddl and dumpfile name important.

Step 2: We are going to take the Scoot user dump file. 

 C:\Users\priya>impdp directory=DATA_PUMP_DIR dumpfile=scoot.dmp sqlfile=mysqlscript.sql


Import: Release 12.1.0.2.0 - Production on Mon Apr 3 12:20:26 2023


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


Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=scoot.dmp sqlfile=mysqlscript.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/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

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/CONSTRAINT/REF_CONSTRAINT

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 Mon Apr 3 12:20:31 2023 elapsed 0 00:00:01


Step3:Once done we can open the SQL file and find the DDL of the schema.

We can open and double-click the file you can get the file like this.. 

-- 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 "SCOTT" IDENTIFIED BY VALUES 'S:6F790A33F7D5B6B3D5CA43B8452D18898D5E40341DED66931ED8ABBDFD19;H:39033131DF1B91CA97082CBA223687A0;T:FB9D159013AE101D7EDD4B49E0215BCC4A90AA7FC7F47ABB60F59AC31748CB58BC46E2560CE54CB1144C3A526714EF21904F40433BC5347D23369768B21D79BEDBEDDB2531D71E80463277E706BB5CAD;C89B3100F762D968'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

 GRANT "CONNECT" TO "SCOTT";

 GRANT "RESOURCE" TO "SCOTT";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT


BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PROD', inst_scn=>'2800468');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYS

CREATE TABLE "SCOTT"."BONUS"

   (    "ENAME" VARCHAR2(10 BYTE),

        "JOB" VARCHAR2(9 BYTE),

        "SAL" NUMBER,

        "COMM" NUMBER

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10 BYTE),

        "JOB" VARCHAR2(9 BYTE),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 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 "SCOTT"."SALGRADE"

   (    "GRADE" NUMBER,

        "LOSAL" NUMBER,

        "HISAL" NUMBER

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 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 "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14 BYTE),

        "LOC" VARCHAR2(13 BYTE)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 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" ;

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX

-- CONNECT SCOTT

BEGIN

NULL;

END;

/


BEGIN

NULL;

END;

/


-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

-- CONNECT SYS

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 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" )  ENABLE;

ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 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" )  ENABLE;

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER


Thanks

Rameshbabu P

0 Comments

DELETE Statement in ORACLE DB— Step by Step Internal Flow with classroom example.

by
Imagine your Oracle database like a school, and you’re Teacher decides to remove a student (row) from the classroom(table). Let’s see how the journey works step by step: 1)Classroom = Oracle Table 2)Students = Rows (Records) 3)Class Teacher = Oracle Server Process 4)Attendance Register = Data Dictionary User fires DELETE statement: DELETE FROM students WHERE grade = 'D'; Internal Processing Steps: Client Process Sends DELETE Statement to Server Process The teacher receives a request to remove a student from the classroom. Syntax Check (Parsing) The teacher checks if the request is grammatically correct — proper command, keywords, and a semicolon. Semantic Check Teacher verifies if the classroom exists and whether the condition (like grade = 'D') makes sense. Object Resolution Teacher confirms that the ‘students’ classroom (table) exists in the school records (data dictionary). Optimization Decides the fastest way to find those students — by roll number, grade, or al...
Image