Through the 9i DBMS

Statement
The syntax that is basically used is as follows:

A. Getting a single table-and-indexed syntax
Set heading off;
Set echo off;
Set pages 999;
Set long 90000;

Spool Dept.sql
Select Dbms_metadata.get_ddl (' TABLE ', ' DEPT ', ' SCOTT ') from dual;
Select Dbms_metadata.get_ddl (' INDEX ', ' dept_idx ', ' SCOTT ') from dual;
Spool off;

B. Get all the table and indexed syntax under a schema, take Scott for example:

Set pagesize 0
Set Long 90000

Set Feedback off

Set echo off
Spool Scott_schema.sql
Connect Scott/tiger;
SELECT Dbms_metadata. GET_DDL (' TABLE ', u.table_name)
From User_tables u;
SELECT Dbms_metadata. GET_DDL (' INDEX ', u.index_name)
From User_indexes u;
Spool off;

C. Getting the syntax of a schema to build all stored procedures

Connect Brucelau/brucelau;

Spool Procedures.sql

Select
Dbms_metadata. GET_DDL (' PROCEDURE ', u.object_name)
From
User_objects u

where
object_type = ' PROCEDURE ';

Spool off;


Other:

DBMS_METADATA.GET_DDL (' TABLE ', ' TAB1 ', ' USER1 ')
In three parameters, the first specifies the type of object to export the DDL definition (in this case, the table type), the second is the object name (in this case, the table name), and the third is the user name of the object.