Oracle Database Object Export Script

Oracle databases are common enterprise-class database types and have good database management tools under the Windows operating system, but in an Ubuntu (Linux, AIX, UNIX) environment, the best way to manage this is scripted management, and the following is an Oracle database user object Export script , here to save for a rainy:

Note: Here, the main use of the Oracle database of a function package dbms_metadata, which can be said to be the Oracle management of the Super function package, if you want to better manage the database, then please actively understand it.

Set pagesize 0
Set Long 90000
Set Feedback off
Set echo off
--Database Login
Conn TEST/TEST@ORCL
--Export File name
Spool Oracleobj.sql
--Output information is formatted with indentation or newline
EXEC Dbms_metadata.set_transform_param (Dbms_metadata.session_transform, ' pretty ', TRUE);
--Make sure each statement takes a semicolon
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' Sqlterminator ', TRUE);
--Close the table index, foreign key, and so on (followed by a separate build)
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' CONSTRAINTS ', FALSE);
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' ref_constraints ', FALSE);
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' Constraints_as_alter ', FALSE);
--Turn off storage, table space properties
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' STORAGE ', FALSE);
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' tablespace ', FALSE);
--Close the Pctfree, Nocompress, and other properties of creating tables
EXEC Dbms_metadata.set_transform_param (dbms_metadata.session_transform, ' segment_attributes ', FALSE);
--Create a User object
SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' TABLE ', ' VIEW ', ' synonym ', ' SEQUENCE ')
;
SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' INDEX ', ' TRIGGER ')
;
SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' FUNCTION ')
;
SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' PROCEDURE ')
;
--Get Table comments
SELECT dbms_lob.substr (dbms_metadata.get_dependent_ddl (' COMMENT ', table_name))
From (SELECT distinct table_name
From User_col_comments
WHERE comments is not NULL
)
;
Spool off;