How Does Oracle use dbms_metadata.get_ddl to obtain DDL statements?

The dbms_metadata.get_ddl package obtains the DDL script of the database object. As follows (executed in sqlplus ):
1. Get the DDL statement of a table:

Set serveroutput on
Set linesize 1000
Set feedback off
Set long 999999 ------ incomplete display
Set pagesize 1000 ---- execute paging partition (dbms_metadata.session_transform, 'store', false); --- Remove redundant parameters such as storage select dbms_metadata.get_ddl ('table', 'tcc _ ne_frame ') from dual; select dbms_metadata.get_ddl ('table', 'tcc _ ne_snap ') from dual; 2. Obtain the DDL statements for all tables, indexes, and stored procedures of a user.Set serveroutput on
Set linesize 1000
Set feedback off
Set long 999999 ------ incomplete display
Set pagesize 1000 ---- Paging
--- Remove redundant parameters such as storage
Execute dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false); select dbms_metadata.get_ddl (U. object_type, U. object_name)
From user_objects u
Where U. object_type in ('table', 'index', 'Procedure '); 3. Obtain the DDL statements for all tablespaces.Set serveroutput on
Set linesize 1000
Set feedback off
Set long 999999 ------ incomplete display
Set pagesize 1000 ---- Paging
--- Remove redundant parameters such as storage: Select dbms_metadata.get_ddl ('tablespace', ts. tablespace_name)
From dba_tablespaces ts; 4. Obtain the DDL statements of all created users.Set serveroutput on
Set linesize 1000
Set feedback off
Set long 999999 ------ incomplete display
Set pagesize 1000 ---- Paging
--- Remove redundant parameters such as storage: Select dbms_metadata.get_ddl ('user', U. username)
From dba_users U; in addition, if the package cannot be executed, You need to correctly install the corresponding package.

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 2041672

 

**************************************** **************

Common error 1:

SQL> select dbms_metadata.get_ddl ('table', 'pc', 'Scott ') from dual;
Error:
ORA-19206: invalid value for query or ref cursor Parameter
ORA-06512: At "SYS. dbms_xmlgen", line 83
ORA-06512: At "SYS. DBMS_METADATA", line 345
ORA-06512: At "SYS. DBMS_METADATA", line 410
ORA-06512: At "SYS. DBMS_METADATA", line 449
ORA-06512: At "SYS. DBMS_METADATA", line 615
ORA-06512: At "SYS. DBMS_METADATA", line 1221
ORA-06512: At line 1

No rows selected

SQL>

Solution: run $ ORACLE_HOME/rdbms/admin/catmeta. SQL

**************************************** *************

Common Error 2:

A ORA-19206 error occurred today while running the DBMS_METADATA Package on a database.

 

 

The error message is as follows:

SQL> select dbms_metadata.get_ddl ('materialized _ view', 'cat _ auth_gmp ', 'ndmain') from dual;
Error:
ORA-19206: invalid value for query or ref cursor Parameter
ORA-06512: At "SYS. dbms_xmlgen", line 83
ORA-06512: At "SYS. DBMS_METADATA", line 345
ORA-06512: At "SYS. DBMS_METADATA", line 410
ORA-06512: At "SYS. DBMS_METADATA", line 449
ORA-06512: At "SYS. DBMS_METADATA", line 615
ORA-06512: At "SYS. DBMS_METADATA", line 1221
ORA-06512: At line 1

No rows selected

After a careful check, it is found that all tables related to the materialized view will encounter the above error when running dbms_metadata.get_ddl, and other objects are not affected.

Because the data in this database is migrated through exp/IMP, although there is no obvious error message in the IMP process, however, the data dictionary error caused by exp/imp is still suspected.

After querying Metalink information, we found many similar errors. The solution provided by Oracle is to reinstall XDB.

So I tried to reinstall the XML Scheme:

First, log in as sysdba, shut down and restart the database to prepare for uninstalling the XML object:

SQL> Conn/As sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
Oracle instance started.

Total system global area 5876197568 bytes
Fixed size 739520 bytes
Variable Size 503316480 bytes
Database buffers 5368709120 bytes
Redo buffers 3432448 bytes
Database mounted.
Database opened.
SQL> set echo on
SQL> SPO xdb_removal.log
SQL> @? /Rdbms/admin/catnoqm. SQL
SQL> Rem
SQL> REM $ header: catnoqm. SQL 03-Jan-2002.17: 32: 31 spannala exp $
SQL> Rem
SQL> REM catnoqm. SQL
SQL> Rem
SQL> REM copyright (c) 2001,200 2, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> REM name
SQL> REM catnoqm. SQL-catalog script for removing (NO) XDB
SQL> Rem
SQL> REM description
SQL> REM this script drops the metadata created for SQL XML Management
SQL> REM this scirpt must be invoked as SYS. It is to be invoked
SQL> Rem
SQL> REM @ catnoqm
SQL> REM notes
SQL> Rem
SQL> REM modified (mm/DD/yy)
SQL> REM spannala 01/03/02-tables are not handled by XDB
SQL> REM spannala 01/02/02-Registry
SQL> REM spannala 12/20/01-passing in the resource tablespace name
SQL> REM tsingh 11/17/01-Remove connection string
SQL> REM tsingh 06/30/01-XDB: XML database merge
SQL> REM amanikut 02/13/01-Creation
SQL> Rem
SQL> Rem
SQL>
SQL> execute dbms_registry.removing ('xdb ');
Begin dbms_registry.removing ('xdb'); end;

*
Error at line 1:
ORA-01403: No data found
ORA-06512: At "SYS. dbms_registry", line 420
ORA-06512: At line 1

SQL> drop user XDB cascade;

User dropped.

SQL> SPO off

From the results of the above script call, it can be determined that the XML object was not successfully registered during the execution of the import, which may be the cause of the error.

Ensure that both the Java pool and the shared pool are larger than 150 m, and the XDB tablespace can be expanded or larger than 150 m. Then you can restart the system.

SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
Oracle instance started.

Total system global area 5876197568 bytes
Fixed size 739520 bytes
Variable Size 503316480 bytes
Database buffers 5368709120 bytes
Redo buffers 3432448 bytes
Database mounted.
Database opened.

Next, reinstall the XML Scheme:

SQL> set echo on
SQL> SPO xdb_install.log
SQL> @? /Rdbms/admin/catqm. SQL xdbpasswd XDB temp

When catqm. SQL is called, the following three parameters are followed by the XDB user password, default tablespace, and temporary guest.

SQL> @? /Rdbms/admin/catxdbj. SQL

Then execute the preceding SQL statement:

Then, check whether the XDB user has an error object and whether the XDB is registered to the database:

SQL> select count (*) from dba_objects where owner = 'xdb' and status = 'invalid ';

Count (*)
----------
0

SQL> select comp_name, status, version from dba_registry where comp_name = 'oracle XML database ';

Comp_name status version
-----------------------------------------------------------------------
Oracle XML database valid 9.2.0.4.0

Restart the database and listen, and the XDB is reinstalled.

After XDB is rebuilt, the access to DBMS_METADATA becomes normal:

SQL> select dbms_metadata.get_ddl ('materialized _ view', 'cat _ auth_gmp ', 'ndmain') from dual;

Dbms_metadata.get_ddl ('materialized _ view', 'cat _ auth_gmp ', 'ndmain ')
------------------------------------------------------------------

Create Materialized View "ndmain". "cat_auth_gmp"
Organization heap pctfree