반응형


Oracle version이 10.2.0.4로 동일하고 DB간 character set 도 동일하다고 가정한다면....

 

그냥 데이타파일을 copy하면 안되고요...

Cross-Platform Transportable Tablespaces 기능을 사용하시면 됩니다.

 

이때 Source 와 Target DB에서 아래를 조회해서 endian format이 같은지 검사해서...

아래 경우 little endian인데...source db와 target db의 os가 동일한 endian이면 상관없지만

 

 

        SQL> select PLATFORM_ID, PLATFORM_NAME  from v$database;
       
        PLATFORM_ID PLATFORM_NAME
        ----------- ------------------------------
                 10 Linux IA (32-bit)

 

        SQL> select  * from v$transportable_platform;
       
        PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
        ----------- ------------------------------ --------------
                  1 Solaris[tm] OE (32-bit)        Big
                  2 Solaris[tm] OE (64-bit)        Big
                  7 Microsoft Windows IA (32-bit)  Little  
                 10 Linux IA (32-bit)              Little   <--- Little endian임.
                  6 AIX-Based Systems (64-bit)     Big
                  3 HP-UX (64-bit)                 Big
                  5 HP Tru64 UNIX                  Little
                  4 HP-UX IA (64-bit)              Big
                 11 Linux IA (64-bit)              Little
                 15 HP Open VMS                    Little
                  8 Microsoft Windows IA (64-bit)  Little
                  9 IBM zSeries Based Linux        Big
                 13 Linux 64-bit for AMD           Little
                 16 Apple Mac OS                   Big
                 12 Microsoft Windows 64-bit for A Little
                    MD

 

다를 경우 Rman으로 Data File Conversion 을 해야 합니다.

 

     - Source 에서
       > rman target=/
       RMAN> Convert Tablespace 'FINANCE, HR' to Platform ='AIX_Based System (64-bit)'  <--    

                  v$transportable_platform.platform_name
                     DB_FILE_NAME_CONVERT = '/orahome/dbs1', '/orahome/dbs/transport_aix',
                                            '/orahome/dbs2', '/orahome/dbs/transport_aix'; 
         <--해당 디렉토리 아래 FINANCE, HR TS에 해당하는 모든 것을 convert하여 지정된 디렉토리 아래로 copy
        
     또는
    
     - Target 에서
       > rman target=/    
       RMAN> Convert Datafile '/tmp/transport_stage/*' From Platform = 'Solaris[tm] OE (32-bit)'
                     DB_FILE_NAME_CONVERT = '/tmp/transport_stage/fin', '/orahome/dbs1/fin',
                                            '/tmp/transport_stage/hr',  '/orahome/db2/hr';
     - DB_FILE_NAME_CONVERT 가 없으면 flash recovery area에 같은 이름으로 만들어진다.
     - Parallelism option은 parallel하게 복수개의 file을 convert할때 사용.  Convert 시간은 Rman으로
       백업할때 걸리는 시간과 같다. Convert 전후의 file size는 변함이 없다.





TDB라는 방법도 있습니다.

http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-platformmigrationtdb-131164.pdf


HP.UX -> AIX로 진행 해 봤는데..

시간이 많이 걸리던 순서대로 나열하면 exp/imp, TTS, TDB 순서 더군요...

참고 하세요..





반응형
반응형

 

*patch 순서 정리
1. shared_pool_size/java_pool_size=>150M요구
① SYS> show parameter sga_m <-확인
② SYS> shutdown immediate
③ SYS> create pfile from spfile
④ SYS> vi /u01/app/oracle/product/10.2.0/db_1/dbs/initdb01.ora
⑤ sga_max_size=293601280(280*1024*1024=293601280)
⑥ SYS> create spfile from pfile
⑦ SYS> startup
⑧ SYS> show parameter sga_m <-확인
⑨ SYS> alter system set shared_pool_size=100m;
⑩ SYS> alter system set java_pool_size=100m;

2. patch전 서비스 종료
① db shutdown -> SQL*PLUS 종료
② lsnrctl stop -> Listener 종료
③ emctl stop dbsonsole -> emctl 종료
④ root 계정에서
/u01/app/oracle/product/10.2.0/db_1/bin/crsctl stop crs
-> asm 설치할때 필요한 crs입니다.

3. patch 설치 -> ./runinstall
① next
② script 확인
③ y

4. startup upgrade
① export ORACLE_SID=db01 -> 접속
② sqlplus /as sysdba
③ startup upgrade

5. $ORACLE_HOME/rdbms/admin/catupgrd.sql
① @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql

6. shutdown immediate

7. startup

8. $ORACLE_HOME/rdbms/admin/utlrp.sql
-> dba_objects.status의 값이 invalid가 없을 때까지 재실행!!!
① @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql
② select status from dba_objects;

[출처] Oracle 10.2.0.1.0 to 10.2.0.4.0 Upgrade (Oracle University (Korea)) |작성자 마이너스

반응형
반응형
반응형
반응형

 proc: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory

proc를 컴파일 하다가 보면 발생할 수 있는 에러....

proc는 Server 용 오라클을 설치할 경우 같이 설치됨.

환경 변수에

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

추가하면 정상 작동한다...
반응형
반응형

1. imp/exp는 Remote로 받는경우만 가능(버전문제로 인하여)

2. 11gR2에서 exp 받고 10gR2에서 imp 는 안되고 expdp는 VERSION 파라미터를 사용하면 가능합니다 

(2번 방법은 10gR2서버에 데이터펌프(impdp)가 있는 10g이상만 가능합니다)

USERID=system/system
DIRECTORY=data_pump
SCHEMAS=MYSCHEMA
DUMPFILE=exp.dmp
LOGFILE=exp.log
STATUS=600
NETWORK_LINK=SPARE9I
VERSION='10.2'

 

반응형
반응형

SQL Plus 명령어 모음

 명령어  설명
 L[IST]  SQL PLUS 버퍼상에 있는 내용을 출력
 L[IST] m   SQL PLUS 버퍼상에 있는 m번째 라인만 출력
 L[IST] m n  SQL PLUS 버퍼상에 있는 m번째 라인부터 n번째라인까지 출력
 R[UN]  SQL PLUS 버퍼상에 있는 SQL문을 실행 
 n 내용  SQL PLUS 버퍼상에 있는 n 번째 라인의 값을 "내용"으로 채워짐  
 I[NSERT]  SQL PLUS 버퍼상에 있는 제일 마지막 라인 다음에 추가됨. 
 DEL  SQL PLUS 버퍼상에 있는 제일 마지막 라인을 삭제함.
 DEL m  SQL PLUS 버퍼상에 있는 m 번째 라인을 삭제함.
 DEL m n  SQL PLUS 버퍼상에 있는 m 번째 라인부터 n번째 라인까지 삭제함.
 A[PPEND] "내용"  SQL PLUS 버퍼상에 있는 제일 마지막 라인에 "내용"을 추가함.
 C[hange]/old_str/new_str/  SQL PLUS 버퍼상에 있는 old_str 문자열을 new_str 문자열로 변경처리함.
 CL[EAR] BUFF[ER]  SQL PLUS 버퍼상에 있는 내용을 모두 삭제 
 CL[EAR] SCR[EEN]  SQL PLUS 화면을 깨끗하게 함


 List 예제

SQL> list
     1  select
     2  *
     3  from
     4  tab
     5*

 n 내용 예제

SQL> 2 tname
SQL> list
     1  select
     2  tname
     3  from
     4  tab
     5*

 I[nsert] 예제


SQL> list
     1  select
     2  tname
     3  from
     4  tab
     5*
SQL> i
     6  where tname='KKK';

TNAME
------------------------------
KKK


 DEL 예제

SQL> list
     1  select
     2  tname
     3  from
     4  tab
     5*
SQL> i
     6  where tname='KKK';

TNAME
------------------------------
KKK

SQL> del
SQL> list
     1  select
     2  tname
     3  from
     4  tab
     5*


 A[ppend] 예제


SQL> list
     1  select
     2  tname
     3  from
     4  tab
     5* where tname='KKK'4
SQL> del 5
SQL> a where tname='KKK'
     4* tabwhere tname='KKK'
SQL> list
     1  select
     2  tname
     3  from
     4* tabwhere tname='KKK'

 C[hange] 예제

SQL> list
     1  select
     2  tname
     3  from
     4* where  ='KKK'
SQL> c /where/where tname/
     4* where tname  ='KKK'
SQL> list
     1  select
     2  tname
     3  from
     4* where tname  ='KKK'

반응형
반응형

2008년 1월일때 200801 로 만들려면

아래와 같이

l_YEAR:='2008';

I_VAL := I_YEAR || TO_CHAR(I_MONTH,'FM09') ;

와 같이 한다.

반응형
반응형

add_months의 함수는

2-28일의 마지막 요일 일때 -1, +1 등과 같이 이전달, 다음달 값을 조회하면

3-31 or 1-31일과 같이 마지막 요일값이 출력된다...

이때는 오라클 add_months함수의 설정값이 마지막 요일값을 가지고 오도록 되어 있기 때문이다.

이문제를 해결하기 위해서는 별도로 함수를 작성해야한다...


Motivation


To use an Oracle function for adding months with the following characteristics:
  • When the resulting month has as many or fewer days than the initial month, and when the initial day of the month is greater than the number of days in the resulting month, then the resulting day should fall on the last day of the resulting month (this is how add_months already works).
  • When the resulting month has more days than the initial month, and when the initial day is the last day of the initial month, then the resulting day of the resulting month should be the same as the initial day (this is not how add_months works).


The Problem


As I had been using the Oracle add_months function for date calculations, I started noticing an unexpected and unintuitive result when a new date is calculated on the last day of certain months. For example,


SELECT add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;

ADD_MONTH
---------
31-MAR-09



I would have expected the resulting date to be 28-MAR-09.

Of course, in the case where the initial month contains more days than the resulting month, I get the results that I expect.


SELECT add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;

ADD_MONTH
---------
28-FEB-09



This feature appears to be part of the ANSI definition for interval math, but this result does not seem particularly intuitive to me.

Unfortunately, the numtoyminterval function only gives the result we expect when go from a month with fewer days to a month with more days, but when going from a month with more days to fewer, it raises an exception when calculating from the last day of the month (or from any day of the month that is greater than the number of days in the resulting month).


SELECT to_date('2009-02-28','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual;

TO_DATE('
---------
28-MAR-09

SELECT to_date('2009-01-31','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified



The Function


The function itself is fairly straightforward using a combination of both add_months and numtoyminterval. When we are going from a month with more days to fewer days, then add_months yields the expected result. If we are going from a date with fewer days in the months to a date with more days in the month, then using numtoyminterval is safe because there will be no overflow.


CREATE OR REPLACE FUNCTION non_ansi_add_months
( vDate DATE,
vMonths INTEGER )
RETURN DATE AS
newDate DATE;
BEGIN
newDate := add_months(vDate, vMonths);
IF to_char(vDate, 'DD') < to_char(newDate, 'DD') THEN
newDate := vDate + numtoyminterval(vMonths, 'month');
END IF;
RETURN newDate;
END non_ansi_add_months;



The Result


This function now yields the results we expect.


SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;

NON_ANSI_
---------
28-MAR-09

SELECT non_ansi_add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;

NON_ANSI_
---------
28-FEB-09



The function even works as expected when adding negative months (calculating month intervals in the past). For example,


SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), -1) FROM dual;

NON_ANSI_
---------
28-JAN-09

SELECT non_ansi_add_months(to_date('2009-03-30','YYYY-MM-DD'), -1) FROM dual;

NON_ANSI_
---------
28-FEB-09

출처  : http://timezra.blogspot.com/2008/11/non-ansi-oracle-addmonths-function.html
반응형
반응형

EXPORT 시 QUERY OPTION에 대한 사용 예(ORACLE 8I 이상)
============================================

PURPOSE
============
oracle 8i에서 export 시 query option에 대한 사용 예


8i에서 export의 query syntax 를 이용하여 table data의 한 부분만 exporting 이 가능


- 8i 에서 select 문장의 where 절을 사용하는 것처럼 export 시에 부분적으로 table data 를 받아 낼수 있는 기능을 소개 한다.
- Direct 옵션은 사용될 수 없다..
- where 절에 해당하는 export utility는 query parameter 를 사용한다.

UNIX syntax:



- Example:
1.SCOTT.Emp table의 ename 이 JAME과 비슷한 이름의 data 를 export ..
exp scott/tiger query=\"where ename like \'JAME%\'\" tables=emp file=exp.dmp log=exp.log


2. employee와 cust table에서 new york 주의 data 만 export ..
exp scott/tiger query=\"where st=\'NY\'\" tables=(employee,cust) file=exp.dmp log=exp.log

query 문장에서 UNIX reserved characters( ", ', ,< .. 등) 를 사용하는 경우에는 escape ('\') 을 반드시 사용해야 한다.
예)query=\"where JOB = \'SALESMAN\' and salary \< 1600\"

더 중요한 것은 command line에서 export option을 사용할때는 반드시 escape 이 있어야 하나
parfile을 사용할때는 eacape이 불필요하다.

예를 보면 .. p라는 이름의 file을 다음과 같이 생성
tables=emp query="where job='SALESMAN'"

parfile을 이용해서 export 를 실행해 보면
[rmtdchp6]/apac/rdbms/64bit/app/oracle/product/9.2.0> exp scott/tiger parfile=p

Export: Release 9.2.0.4.0 - Production on Wed Mar 17 00:12:34 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 4 rows exported
와 같이 정상 처리 됨을 알수 있다.

만일 command line에서 위의 내용을 실행하게 되면 다음과 같이 error 를 만난다.
exp scott/tiger tables=emp query="where job='SALESMAN'"

LRM-00101: unknown parameter name 'job'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

command line에는 query 내에 single(')나 double quotes(") 를 사용한다면 반드시 double quotes(") 를 사용하여
query 문을 묶어야 한다.그러나 query 내에서 single ('')나 double quotes(") 를 사용하지 않는다면 single quotes (')을 사용하여
query 문을 수행할 수도 있다..

다음 예를 보면..
1>exp scott/tiger tables=emp query=\'where deptno=20\'

Export: Release 9.2.0.4.0 - Production on Wed Mar 17 00:22:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16KSC5601 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 4 rows exported

2>exp scott/tiger tables=emp query=\'where job=\'SALESMAN\'\'

LRM-00112: multiple values not allowed for parameter 'query'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

즉.. 정리를 하자면
command line에서 query 내에 '," 을사용하지 않는 다면 '나 " 으로 query option을 사용할수 있다
query=\'where deptno = 20\'
query=\"where deptno = 20\"
query=\'where deptno \< 2\'
(이 경우 single 이나 double quotes 을 둘다 사용할수 있다.)

parfile을 사용하는 경우에는 다음과 같이 단순하게 사용이 가능하다.
query='where deptno = 20'
query="where deptno = 20"
query='where deptno < 20'


WINDOWS NT / 2000 와 NETWARE syntax:


다음의 자료를 참조바란다.

Example:

EXP demo/demo tables=emp file=exp1.dmp query="""where deptno>30"""

double quotes(") 를 둘러 싸는 경우에는 space 가있으면 안된다.

parfile의 사용은 다음과 같이 하시면 됩니다.

file=exp66.dmp
query="where deptno > 20"
tables=(emp)
log=log66.txt
userid=scott/tiger


Explanation


Windows NT or Win2000의 경우 command line에서는 3 double quotes 이 필요하고
'PARFILE 을 사용하는 경우에는 double quotes(") 한번만 필요함

Reference Documents


Oracle8i Utilities guide
Note:91864.1

출처 : http://kr.forums.oracle.com/forums/thread.jspa?messageID=1698906
반응형
반응형

  첨부파일은 Oracle® Database Utilities
10g Release 2 (10.2)
Part Number B14215-01 의 pdf 파일임.


출처 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm


SQL> SET LONG 2000000 PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

GRANT UNLIMITED TABLESPACE TO "SCOTT"

 

SQL> SET LONG 2000000 PAGESIZE 0
SQL> SELECT
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;

GRANT "CONNECT" TO "SCOTT"

GRANT "RESOURCE" TO "SCOTT"

 

SQL> SET LONG 2000000 PAGESIZE 0
SQL> SELECT
DBMS_METADATA.GET_DDL('TABLE','SALGRADE','SCOTT') from dual;

CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) 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)
TABLESPACE "USERS"

 

SQL> SET LONG 2000000 PAGESIZE 0
SQL> SELECT
DBMS_METADATA.GET_DDL('TRIGGER','TRIGGER_1','SCOTT') from dual;

 

SQL> SET LONG 2000000 PAGESIZE 0
SQL> SELECT
DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_1','SCOTT') from dual;


57 DBMS_METADATA

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

See Also:

Oracle Database Utilities for more information and for examples of using the Metadata API

This chapter contains the following topics:


Using DBMS_METADATA

This section contains topics which relate to using the DBMS_METADATA package.


Overview

You can use the DBMS_METADATA package to retrieve metadata and also to submit XML.

Retrieving Metadata

If you are retrieving metadata, you can specify:

  • The kind of object to be retrieved. This can be either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export).

  • Optional selection criteria, such as owner or name.

  • Parse items (attributes of the returned objects to be parsed and returned separately).

  • Optional transformations on the output, implemented by XSLT (Extensible Stylesheet Language Transformation) scripts. By default the output is represented in XML, but you can specify transformations (into SQL DDL, for example), which are implemented by XSLT stylesheets stored in the database or externally.

DBMS_METADATA provides the following retrieval interfaces:

  • For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM,SET_REMAP_PARAM, FETCH_xxx, and CLOSE retrieve multiple objects.

  • For use in SQL queries and for browsing: GET_XML and GET_DDL return metadata for a single named object. The GET_DEPENDENT_XML, GET_DEPENDENT_DDL, GET_GRANTED_XML, and GET_GRANTED_DDL interfaces return metadata for one or more dependent or granted objects. These procedures do not support heterogeneous object types.

Submitting XML

If you are submitting XML, you specify:

  • The type of object

  • Optional transform parameters to modify the object (for example, changing the object's owner)

  • Parse items (attributes of the submitted objects to be parsed and submitted separately)

  • Whether to execute the operation or simply return the generated DDL

DBMS_METADATA provides a programmatic interface for submission of XML. It is comprised of the following procedures: OPENW, ADD_TRANSFORM, SET_TRANSFORM_PARAM, SET_REMAP_PARAM, SET_PARSE_ITEM, CONVERT, PUT, and CLOSE.


Security Model

The object views of the Oracle metadata model implement security as follows:

  • Nonprivileged users can see the metadata of only their own objects.

  • SYS and users with SELECT_CATALOG_ROLE can see all objects.

  • Nonprivileged users can also retrieve public synonyms, system privileges granted to them, and object privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.

  • If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.

  • If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

  • In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.


Rules and Limits

In an Oracle Shared Server (OSS) environment, the DBMS_METADATA package must disable session migration and connection pooling. This results in any shared server process that is serving a session running the package to effectively become a default, dedicated server for the life of the session. You should ensure that sufficient shared servers are configured when the package is used and that the number of servers is not artificially limited by too small a value for the MAX_SHARED_SERVERS initialization parameter.


Data Structures - Object and Table Types

The DBMS_METADATA package defines, in the SYS schema, the following OBJECT and TABLE types.

CREATE TYPE sys.ku$_parsed_item AS OBJECT (
  item            VARCHAR2(30),
  value           VARCHAR2(4000),
  object_row      NUMBER )
/

CREATE PUBLIC SYNONYM ku$_parsed_item FOR sys.ku$_parsed_item;

CREATE TYPE sys.ku$_parsed_items IS TABLE OF sys.ku$_parsed_item
/

CREATE PUBLIC SYNONYM ku$_parsed_items FOR sys.ku$_parsed_items;

CREATE TYPE sys.ku$_ddl AS OBJECT (
   ddlText        CLOB,
parsedItem sys.ku$_parsed_items )
/

CREATE PUBLIC SYNONYM ku$_ddl FOR sys.ku$_ddl;

CREATE TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl
/

CREATE PUBLIC SYNONYM ku$_ddls FOR sys.ku$_ddls;

CREATE TYPE sys.ku$_multi_ddl AS OBJECT (
   object_row     NUMBER,
   ddls           sys.ku$_ddls )
/

CREATE OR REPLACE PUBLIC SYNONYM ku$_multi_ddl FOR sys.ku$_multi_ddl;

CREATE TYPE sys.ku$_multi_ddls IS TABLE OF sys.ku$_multi_ddl;
/

CREATE OR REPLACE PUBLIC SYNONYM ku$_multi_ddls FOR
                          sys.ku$_multi_ddls;

CREATE TYPE sys.ku$_ErrorLine IS OBJECT (
   errorNumber    NUMBER,
   errorText      VARCHAR2(2000) )
/

CREATE PUBLIC SYNONYM ku$_ErrorLine FOR sys.ku$_ErrorLine;

CREATE TYPE sys.ku$_ErrorLines IS TABLE OF sys.ku$_ErrorLine
/
CREATE PUBLIC SYNONYM ku$ErrorLines FOR sys.ku$_ErrorLines;

CREATE TYPE sys.ku$_SubmitResult AS OBJECT (
   ddl          sys.ku$_ddl,
   errorLines   sys.ku$_ErrorLines );
/

CREATE TYPE sys.ku$_SubmitResults IS TABLE OF sys.ku$_SubmitResult
/

CREATE PUBLIC SYNONYM ku$_SubmitResults FOR sys.ku$_SubmitResults;

Subprogram Groupings

The DBMS_METADATA subprograms are used to retrieve objects from, and submit XML to, a database. Some subprograms are used for both activities, while others are used only for retrieval or only for submission.

  • Table 57-1 provides a summary, in alphabetical order, of DBMS_METADATA subprograms used to retrieve multiple objects from a database.

  • Table 57-2 provides a summary, in alphabetical order, of DBMS_METADATA subprograms used to submit XML metadata to a database.


Subprograms for Retrieving Multiple Objects From the Database

Table 57-1 lists the subprograms used for retrieving multiple objects from the database.

Table 57-1 DBMS_METADATA Subprograms for Retrieving Multiple Objects

Subprogram Description

ADD_TRANSFORM Function

Specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects

CLOSE Procedure2

Invalidates the handle returned by OPEN and cleans up the associated state

FETCH_xxx Functions and Procedures

Returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on

GET_QUERY Function

Returns the text of the queries that are used by FETCH_xxx

GET_xxx Functions

Fetches the metadata for a specified object as XML or DDL, using only a single call

OPEN Function

Specifies the type of object to be retrieved, the version of its metadata, and the object model

SET_COUNT Procedure

Specifies the maximum number of objects to be retrieved in a single FETCH_xxx call

SET_FILTER Procedure

Specifies restrictions on the objects to be retrieved, for example, the object name or schema

SET_PARSE_ITEM Procedure

Enables output parsing by specifying an object attribute to be parsed and returned

SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures

Specifies parameters to the XSLT stylesheets identified by transform_handle



Subprograms for Submitting XML to the Database

Table 57-2 lists the subprograms used for submitting XML to the database.

Table 57-2 DBMS_METADATA Subprograms for Submitting XML

Subprogram Description

ADD_TRANSFORM Function

Specifies a transform for the XML documents

CLOSE Procedure2

Closes the context opened with OPENW

CONVERT Functions and Procedures

Converts an XML document to DDL

OPENW Function

Opens a write context

PUT Function

Submits an XML document to the database

SET_PARSE_ITEM Procedure

Specifies an object attribute to be parsed

SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures

SET_TRANSFORM_PARAM specifies a parameter to a transform

SET_REMAP_PARAM specifies a remapping for a transform



Summary of All DBMS_METADATA Subprograms

Table 57-3 DBMS_METADATA Package Subprograms

Subprogram Description

ADD_TRANSFORM Function

Specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects

CLOSE Procedure2

Invalidates the handle returned by OPEN and cleans up the associated state

CONVERT Functions and Procedures

Converts an XML document to DDL.

FETCH_xxx Functions and Procedures

Returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on

GET_xxx Functions

Fetches the metadata for a specified object as XML or DDL, using only a single call

GET_QUERY Function

Returns the text of the queries that are used by FETCH_xxx

OPEN Function

Specifies the type of object to be retrieved, the version of its metadata, and the object model

OPENW Function

Opens a write context

PUT Function

Submits an XML document to the database

SET_COUNT Procedure

Specifies the maximum number of objects to be retrieved in a single FETCH_xxx call

SET_FILTER Procedure

Specifies restrictions on the objects to be retrieved, for example, the object name or schema

SET_PARSE_ITEM Procedure

Enables output parsing by specifying an object attribute to be parsed and returned

SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures

Specifies parameters to the XSLT stylesheets identified by transform_handle



ADD_TRANSFORM Function

This function is used for both retrieval and submission:

Syntax

DBMS_METADATA.ADD_TRANSFORM (
   handle       IN NUMBER,
   name         IN VARCHAR2,
   encoding     IN VARCHAR2 DEFAULT NULL,
   object_type  IN VARCHAR2 DEFAULT NULL)
 RETURN NUMBER;

Parameters

Table 57-4 ADD_TRANSFORM Function Parameters

Parameters Description

handle

The handle returned from OPEN when this transform is used to retrieve objects. Or the handle returned from OPENW when this transform is used in the submission of XML metadata.

name

The name of the transform. If name contains a period, colon, or forward slash, it is interpreted as the URL of a user-supplied XSLT script. See Oracle XML DB Developer's Guide.

Otherwise, name designates a transform implemented by this project. The following transforms are defined:

  • DDL - the document is transformed to DDL that creates the object. The output of this transform is not an XML document.

  • MODIFY - The document is modified as directed by transform and remap parameters. The output of this transform is an XML document. If no transform or remap parameters are specified, the document is unchanged.

encoding

The name of the Globalization Support character set in which the stylesheet pointed to by name is encoded. This is only valid if name is a URL. If left NULL and the URL is external to the database, UTF-8 encoding is assumed. If left NULL and the URL is internal to the database (that is, it begins with /oradb/), then the encoding is assumed to be the database character set.

object_type

The definition of this parameter depends upon whether you are retrieving objects or submitting XML metadata.

  1. When you use ADD_TRANFORM to retrieve objects, the following definition of object_type applies:

Designates the object type to which the transform applies. (Note that this is an object type name, not a path name.) By default the transform applies to the object type of the OPEN handle. When the OPEN handle designates a heterogeneous object type, the following behavior can occur:

  • if object_type is omitted, the transform applies to all object types within the heterogeneous collection

  • if object_type is specified, the transform only applies to that specific object type within the collection

    If you omit this parameter you can add the DDL transform to all objects in a heterogeneous collection with a single call. If you supply this parameter, you can add a transform for a specific object type.

  1. When you use ADD_TRANSFORM in the submission of XML metadata, this parameter is the object type to which the transform applies. By default, it is the object type of the OPENW handle. Because the OPENW handle cannot designate a heterogeneous object type, the caller would normally leave this parameter NULL in the ADD_TRANSFORM calls.


Return Values

The opaque handle that is returned is used as input to SET_TRANSFORM_PARAM and SET_REMAP_PARAM. Note that this handle is different from the handle returned by OPEN or OPENW; it refers to the transform, not the set of objects to be retrieved.

Usage Notes

  • With no transforms added, objects are returned by default as XML documents. You call ADD_TRANSFORM to specify the XSLT stylesheets to be used to transform the returned XML documents.

  • You can call ADD_TRANSFORM more than once to apply multiple transforms to XML documents. Transforms are applied in the order in which they were specified, the output of the first transform being used as input to the second, and so on.

  • The output of the DDL transform is not an XML document. Therefore, no transform should be added after the DDL transform.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OPERATION. ADD_TRANSFORM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to ADD_TRANSFORM for the current OPEN context are permitted.

  • INCONSISTENT_ARGS. The arguments are inconsistent. Possible inconsistencies include the following:

    • encoding is specified even though name is not a URL

    • object_type is not part of the collection designated by handle


CLOSE Procedure

This procedure is used for both retrieval and submission. This procedure invalidates the handle returned by OPEN (or OPENW) and cleans up the associated state.

Syntax

DBMS_METADATA.CLOSE (
   handle  IN NUMBER);

Parameters

Table 57-5 CLOSE Procedure Parameters

Parameter Description

handle

The handle returned from OPEN (or OPENW).


Usage Notes

Note:

The following notes apply only to object retrieval

You can prematurely terminate the stream of objects established by OPEN or (OPENW).

  • If a call to FETCH_xxx returns NULL, indicating no more objects, a call to CLOSE is made transparently. In this case, you can still call CLOSE on the handle and not get an exception. (The call to CLOSE is not required.)

  • If you know that only one specific object will be returned, you should explicitly call CLOSE after the single FETCH_xxx call to free resources held by the handle.

Exceptions

  • INVALID_ARGVAL. The value for the handle parameter is NULL or invalid.


CONVERT Functions and Procedures

The CONVERT functions and procedures transform input XML documents. The CONVERT functions return creation DDL. The CONVERT procedures return either XML or DDL, depending on the specified transforms.

See Also:

For more information about related subprograms:

Syntax

The CONVERT functions are as follows:

DBMS_METADATA.CONVERT (
   handle   IN NUMBER,
   document IN sys.XMLType)
 RETURN sys.ku$_multi_ddls;

DBMS_METADATA.CONVERT (
  handle   IN NUMBER,
  document IN CLOB)
 RETURN sys.ku$_multi_ddls;

The CONVERT procedures are as follows:

DBMS_METADATA.CONVERT (
  handle   IN NUMBER,
  document IN sys.XMLType,
  result   IN OUT NOCOPY CLOB);

DBMS_METADATA.CONVERT (
  handle   IN NUMBER,
  document IN CLOB,
  result   IN OUT NOCOPY CLOB);

Parameters

Table 57-6 CONVERT Subprogram Parameters

Parameter Description

handle

The handle returned from OPENW.

document

The XML document containing object metadata of the type of the OPENW handle.

result

The converted document.


Return Values

DDL to create the object(s).

Usage Notes

You can think of CONVERT as the second half of FETCH_xxx, either FETCH_DDL (for the function variants) or FETCH_CLOB (for the procedure variants). There are two differences:

  • FETCH_xxx gets its XML document from the database, but CONVERT gets its XML document from the caller

  • FETCH_DDL returns its results in a sys.ku$_ddls nested table, but CONVERT returns a sys.ku$_multi_ddls nested table

The transforms specified with ADD_TRANSFORM are applied in turn, and the result is returned to the caller. For the function variants, the DDL transform must be specified. If parse items were specified, they are returned in the parsedItems column. Parse items are ignored by the procedure variants.

The encoding of the XML document is embedded in its CLOB or XMLType representation. The version of the metadata is embedded in the XML. The generated DDL is valid for the database version specified in OPENW.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INCONSISTENT_OPERATION. No transform was specified. The DDL transform was not specified (function variants only).

  • INCOMPATIBLE_DOCUMENT. The version of the XML document is not compatible with this version of the software.


FETCH_xxx Functions and Procedures

These functions and procedures return metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on. See "Usage Notes" for the variants.

See Also:

For more information about related subprograms:

Syntax

The FETCH functions are as follows:

DBMS_METADATA.FETCH_XML (
   handle  IN NUMBER) 
RETURN sys.XMLType;

See Also:

Oracle XML DB Developer's Guide for a description of XMLType
DBMS_METADATA.FETCH_DDL (
   handle  IN NUMBER)
RETURN sys.ku$_ddls;

DBMS_METADATA.FETCH_CLOB (
   handle       IN NUMBER,
   cache_lob    IN BOOLEAN DEFAULT TRUE,
   lob_duration IN PLS INTEGER DEFAULT DBMS_LOB.SESSION)
RETURN CLOB;

The FETCH procedures are as follows:

DBMS_METADATA.FETCH_CLOB (
   handle  IN NUMBER,
   doc     IN OUT NOCOPY CLOB);

DBMS_METADATA.FETCH_XML_CLOB (
   handle  IN NUMBER,
   doc     IN OUT NOCOPY CLOB,
   parsed_items OUT sys.ku$_parsed_items,
   object_type_path OUT VARCHAR2);

Parameters

Table 57-7 FETCH_xxx Function Parameters

Parameters Description

handle

The handle returned from OPEN.

cache_lob

TRUE=read LOB into buffer cache

lob_duration

The duration for the temporary LOB created by FETCH_CLOB, either DBMS_LOB.SESSION (the default) or DBMS_LOB.CALL.

doc

The metadata for the objects, or NULL if all objects have been returned.

parsed_items

A nested table containing the items specified by SET_PARSE_ITEM. If SET_PARSE_ITEM was not called, a NULL is returned.

object_type_path

For heterogeneous object types, this is the full path name of the object type for the objects returned by the call to FETCH_XXX. If handle designates a homogeneous object type, a NULL is returned.


Return Values

The metadata for the objects or NULL if all objects have been returned.

Usage Notes

These functions and procedures return metadata for objects meeting the criteria established by the call to OPEN that returned the handle, and subsequent calls to SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on. Each call to FETCH_xxx returns the number of objects specified by SET_COUNT (or less, if fewer objects remain in the underlying cursor) until all objects have been returned. After the last object is returned, subsequent calls to FETCH_xxx return NULL and cause the stream created by OPEN to be transparently closed.

There are several different FETCH_xxx functions and procedures:

  • The FETCH_XML function returns the XML metadata for an object as an XMLType. It assumes that if any transform has been specified, that transform will produce an XML document. In particular, it assumes that the DDL transform has not been specified.

  • The FETCH_DDL function returns the DDL (to create the object) in a sys.ku$_ddls nested table. It assumes that the DDL transform has been specified. Each row of the sys.ku$_ddls nested table contains a single DDL statement in the ddlText column; if requested, parsed items for the DDL statement will be returned in the parsedItems column. Multiple DDL statements may be returned under the following circumstances:

    • When you call SET_COUNT to specify a count greater than 1

    • When an object is transformed into multiple DDL statements. For example, A TYPE object that has a DDL transform applied to it can be transformed into both CREATE TYPE and CREATE TYPE BODY statements. A TABLE object can be transformed into a CREATE TABLE, and one or more ALTER TABLE statements

  • The FETCH_CLOB function simply returns the object, transformed or not, as a CLOB. By default, the CLOB is read into the buffer cache and has session duration, but these defaults can be overridden with the cache_lob and lob_duration parameters.

  • The FETCH_CLOB procedure returns the objects by reference in an IN OUT NOCOPY parameter. This is faster than the function variant, which returns LOBs by value, a practice that involves an expensive LOB copy.

  • The FETCH_XML_CLOB procedure returns the XML metadata for the objects as a CLOB in an IN OUT NOCOPY parameter. This helps to avoid LOB copies, which can consume a lot of resources. It also returns a nested table of parse items and the full path name of the object type of the returned objects.

  • All LOBs returned by FETCH_xxx are temporary LOBs. You must free the LOB. If the LOB is supplied as an IN OUT NOCOPY parameter, you must also create the LOB.

  • If SET_PARSE_ITEM was called, FETCH_DDL and FETCH_XML_CLOB return attributes of the object's metadata (or the DDL statement) in a sys.ku$_parsed_items nested table. For FETCH_XML_CLOB, the nested table is an OUT parameter. For FETCH_DDL, it is a column in the returned sys.ku$_ddls nested table. Each row of the nested table corresponds to an item specified by SET_PARSE_ITEM and contains the following columns:

    • item—the name of the attribute as specified in the name parameter to SET_PARSE_ITEM.

    • value—the attribute value, or NULL if the attribute is not present in the DDL statement.

    • object-row—a positive integer indicating the object to which the parse item applies. If multiple objects are returned by FETCH_xxx, (because SET_COUNT specified a count greater than 1) then object_row=1 for all items for the first object, 2 for the second, and so on.

  • The rows of the sys.ku$_parsed_items nested table are ordered by ascending object_row, but otherwise the row order is undetermined. To find a particular parse item within an object row the caller must search the table for a match on item.

  • In general there is no guarantee that a requested parse item will be returned. For example, the parse item may not apply to the object type or to the particular line of DDL, or the item's value may be NULL.

  • If SET_PARSE_ITEM was not called, NULL is returned as the value of the parsed items nested table.

  • It is expected that the same variant of FETCH_xxx will be called for all objects selected by OPEN. That is, programs will not intermix calls to FETCH_XML, FETCH_DDL, FETCH_CLOB, and so on using the same OPEN handle. The effect of calling different variants is undefined; it might do what you expect, but there are no guarantees.

  • Every object fetched will be internally consistent with respect to on-going DDL (and the subsequent recursive DML) operations against the dictionary. In some cases, multiple queries may be issued, either because the object type is heterogeneous or for performance reasons (for example, one query for heap tables, one for index-organized tables). Consequently the FETCH_xxx calls may in fact be fetches from different underlying cursors (meaning that read consistency is not guaranteed).

Exceptions

Most exceptions raised during execution of the query are propagated to the caller. Also, the following exceptions may be raised:

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INCONSISTENT_OPERATION. Either FETCH_XML was called when the DDL transform had been specified, or FETCH_DDL was called when the DDL transform had not been specified.


GET_xxx Functions

The following GET_xxx functions let you fetch metadata for objects with a single call:

Syntax

DBMS_METADATA.GET_XML (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

DBMS_METADATA.GET_DEPENDENT_XML (
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT NULL,
object_count       IN NUMBER   DEFAULT 10000)
RETURN CLOB;

DBMS_METADATA.GET_DEPENDENT_DDL (
object_type         IN VARCHAR2,
base_object_name    IN VARCHAR2,
base_object_schema  IN VARCHAR2 DEFAULT NULL,
version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
model               IN VARCHAR2 DEFAULT 'ORACLE',
transform           IN VARCHAR2 DEFAULT 'DDL',
object_count        IN NUMBER   DEFAULT 10000)
RETURN CLOB;

DBMS_METADATA.GET_GRANTED_XML (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT NULL,
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

DBMS_METADATA.GET_GRANTED_DDL (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL',
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

Parameters

Table 57-8 GET_xxx Function Parameters

Parameter Description

object_type

The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter, except that it cannot be a heterogeneous object type. The attributes of the object type must be appropriate to the function. That is, for GET_xxx it must be a named object.

name

The object name. It is used internally in a NAME filter. (If the name is longer than 30 characters, it will be used in a LONGNAME filter.) If this parameter is NULL, then no NAME or LONGNAME filter is specifiedSee Table 57-17 for a list of filters.

schema

The object schema. It is used internally in a SCHEMA filter. The default is the current user.

version

The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.

model

The object model to use. This parameter takes the same values as the OPEN model parameter.

transform

The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.

base_object_name

The base object name. It is used internally in a BASE_OBJECT_NAME filter.

base_object_schema

The base object schema. It is used internally in a BASE_OBJECT_SCHEMA filter. The default is the current user.

grantee

The grantee. It is used internally in a GRANTEE filter. The default is the current user.

object_count

The maximum number of objects to return. See SET_COUNT Procedure .


Return Values

The metadata for the specified object as XML or DDL.

Usage Notes

  • These functions allow you to fetch metadata for objects with a single call. They encapsulate calls to OPEN, SET_FILTER, and so on. The function you use depends on the characteristics of the object type and on whether you want XML or DDL.

    • GET_xxx is used to fetch named objects, especially schema objects (tables, views).

    • GET_DEPENDENT_xxx is used to fetch dependent objects (audits, object grants).

    • GET_GRANTED_xxx is used to fetch granted objects (system grants, role grants).

  • For some object types you can use more than one function. For example, you can use GET_xxx to fetch an index by name, or GET_DEPENDENT_xxx to fetch the same index by specifying the table on which it is defined.

  • GET_xxx only returns a single named object.

  • For GET_DEPENDENT_xxx and GET_GRANTED_xxx, an arbitrary number of dependent or granted objects can match the input criteria. You can specify an object count when fetching these objects. (The default count of 10000 should be adequate in most cases.)

  • If the DDL transform is specified, session-level transform parameters are inherited.

  • If you invoke these functions from SQL*Plus, you should set the PAGESIZE to 0 and set LONG to some large number to get complete, uninterrupted output.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • OBJECT_NOT_FOUND. The specified object was not found in the database.

Examples

Example: Fetch the XML Representation of SCOTT.EMP

To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.

SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_XML('TABLE','EMP','SCOTT')
FROM DUAL;

Example: Fetch the DDL for all Complete Tables in the Current Schema, Filter Out Nested Tables and Overflow Segments

This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults.

To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.

SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

Example: Fetch the DDL For All Object Grants On HR.EMPLOYEES

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',
    'EMPLOYEES','HR') FROM DUAL;

Example: Fetch the DDL For All System Grants Granted To SCOTT

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
    FROM DUAL;

GET_QUERY Function

This function returns the text of the queries that are used by FETCH_xxx. This function assists in debugging.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.GET_QUERY (
   handle  IN NUMBER)
 RETURN VARCHAR2;

Parameters

Table 57-9 GET_QUERY Function Parameters

Parameter Description

handle

The handle returned from OPEN. It cannot be the handle for a heterogeneous object type.


Return Values

The text of the queries that will be used by FETCH_xxx.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for the handle parameter.


OPEN Function

This function specifies the type of object to be retrieved, the version of its metadata, and the object model. The return value is an opaque context handle for the set of objects to be used in subsequent calls.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.OPEN (
   object_type  IN VARCHAR2,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE', 
   network_link IN VARCHAR2 DEFAULT NULL)
 RETURN NUMBER;

Parameters

Table 57-10 Open Function Parameters

Parameter Description

object_type

The type of object to be retrieved. Table 57-11 lists the valid type names and their meanings. These object types will be supported for the ORACLE model of metadata (see model in this table).

The Attributes column in Table 57-11 specifies some object type attributes:

  • Schema objects, such as tables, belong to schemas.

  • Named objects have unique names (if they are schema objects, the name is unique to the schema).

  • Dependent objects, such as indexes, are defined with reference to a base schema object.

  • Granted objects are granted or assigned to a user or role and therefore have a named grantee.

  • Heterogeneous object types denote a collection of related objects of different types. See Table 57-12 for a listing of object types returned for the heterogeneous object type.

These attributes are relevant when choosing object selection criteria. See "SET_FILTER Procedure" for more information.

version

The version of metadata to be extracted. Database objects or attributes that are incompatible with the version will not be extracted. Legal values for this parameter are as follows:

COMPATIBLE (default)—the version of the metadata corresponds to the database compatibility level.

LATEST—the version of the metadata corresponds to the database version.

A specific database version, for example, 9.2.0. As of Oracle Database 10g, this value cannot be lower than 9.2.0.

model

Specifies which view to use, because the API can support multiple views on the metadata. Only the ORACLE model is supported as of Oracle Database 10g.

network_link

Reserved.


Table 57-11 provides the name, meaning, attributes, and notes for the DBMS_METADATA package object types. In the attributes column, S represents a schema object, N represents a named object, D represents a dependent object, G represents a granted object, and H represents a heterogeneous object.

Table 57-11 DBMS_METADATA: Object Types

Type Name Meaning Attributes Notes

AQ_QUEUE

queues

SND

Dependent on table

AQ_QUEUE_TABLE

additional metadata for queue tables

ND

Dependent on table

AQ_TRANSFORM

transforms

SN

None

ASSOCIATION

associate statistics

D

None

AUDIT

audits of SQL statements

DG

Modeled as dependent, granted object. The base object name is the statement audit option name (for example, ALTER SYSTEM). There is no base object schema. The grantee is the user or proxy whose statements are audited.

AUDIT_OBJ

audits of schema objects

D

None

CLUSTER

clusters

SN

None

COMMENT

comments

D

None

CONSTRAINT

constraints

SND

Does not include:

  • primary key constraint for IOT

  • column NOT NULL constraints

  • certain REF SCOPE and WITH ROWID constraints for tables with REF columns

CONTEXT

application contexts

N

None

DATABASE_EXPORT

all metadata objects in a database

H

Corresponds to a full database export

DB_LINK

database links

SN

Modeled as schema objects because they have owners. For public links, the owner is PUBLIC. For private links, the creator is the owner.

DEFAULT_ROLE

default roles

G

Granted to a user by ALTER USER

DIMENSION

dimensions

SN

None

DIRECTORY

directories

N

None

FGA_POLICY

fine-grained audit policies

D

Not modeled as named object because policy names are not unique.

FUNCTION

stored functions

SN

None

INDEX_STATISTICS

precomputed statistics on indexes

D

The base object is the index's table.

INDEX

indexes

SND

None

INDEXTYPE

indextypes

SN

None

JAVA_SOURCE

Java sources

SN

None

JOB

jobs

S

None

LIBRARY

external procedure libraries

SN

None

MATERIALIZED_VIEW

materialized views

SN

None

MATERIALIZED_VIEW_LOG

materialized view logs

D

None

OBJECT_GRANT

object grants

DG

None

OPERATOR

operators

SN

None

OUTLINE

stored outlines

N

This type is being deprecated.

PACKAGE

stored packages

SN

By default, both package specification and package body are retrieved. See "SET_FILTER Procedure".

PACKAGE_SPEC

package specifications

SN

None

PACKAGE_BODY

package bodies

SN

None

PROCEDURE

stored procedures

SN

None

PROFILE

profiles

N

None

PROXY

proxy authentications

G

Granted to a user by ALTER USER

REF_CONSTRAINT

referential constraint

SND

None

REFRESH_GROUP

refresh groups

SN

None

RESOURCE_COST

resource cost info

 

None

RLS_CONTEXT

driving contexts for enforcement of fine-grained access-control policies

D

Corresponds to the DBMS_RLS.ADD_POLICY_CONTENT procedure

RLS_GROUP

fine-grained access-control policy groups

D

Corresponds to the DBMS_RLS.CREATE_GROUP procedure

RLS_POLICY

fine-grained access-control policies

D

Corresponds to DBMS_RLS.ADD_GROUPED_POLICY. Not modeled as named objects because policy names are not unique.

RMGR_CONSUMER_GROUP

resource consumer groups

SN

Data Pump does not use these object types. Instead, it exports resource manager objects as procedural objects.

RMGR_INTITIAL_CONSUMER_GROUP

assign initial consumer groups to users

G

None

RMGR_PLAN

resource plans

SN

None

RMGR_PLAN_DIRECTIVE

resource plan directives

D

Dependent on resource plan

ROLE

roles

N

None

ROLE_GRANT

role grants

G

None

ROLLBACK_SEGMENT

rollback segments

N

None

SCHEMA_EXPORT

all metadata objects in a schema

H

Corresponds to user-mode export.

SEQUENCE

sequences

SN

None

SYNONYM

synonyms

See notes

Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is PUBLIC. The name of a synonym is considered to be the synonym itself. For example, in CREATE PUBLIC SYNONYM FOO FOR BAR, the resultant object is considered to have name FOO and schema PUBLIC.

SYSTEM_GRANT

system privilege grants

G

None

TABLE

tables

SN

None

TABLE_DATA

metadata describing row data for a table, nested table, or partition

SND

For partitions, the object name is the partition name.

For nested tables, the object name is the storage table name. The base object is the top-level table to which the table data belongs. For nested tables and partitioning, this is the top-level table (not the parent table or partition). For nonpartitioned tables and non-nested tables this is the table itself.

TABLE_EXPORT

metadata for a table and its associated objects

H

Corresponds to table-mode export

TABLE_STATISTICS

precomputed statistics on tables

D

None

TABLESPACE

tablespaces

N

None

TABLESPACE_QUOTA

tablespace quotas

G

Granted with ALTER USER

TRANSPORTABLE_EXPORT

metadata for objects in a transportable tablespace set

H

Corresponds to transportable tablespace export

TRIGGER

triggers

SND

None

TRUSTED_DB_LINK

trusted links

N

None

TYPE

user-defined types

SN

By default, both type and type body are retrieved. See "SET_FILTER Procedure".

TYPE_SPEC

type specifications

SN

None

TYPE_BODY

type bodies

SN

None

USER

users

N

None

VIEW

views

SN

None

XMLSCHEMA

XML schema

SN

The object's name is its URL (which may be longer than 30 characters). Its schema is the user who registered it.


Table 57-12 lists the types of objects returned for the major heterogeneous object types. For SCHEMA_EXPORT, certain object types are only returned if the INCLUDE_USER filter is specified at TRUE. In the table, such object types are marked INCLUDE_USER.

Table 57-12 Object Types Returned for the Heterogeneous Object Type

Object Type DATABASE_EXPORT SCHEMA_EXPORT TABLE_EXPORT TRANSPORTABLE_EXPORT

ASSOCIATION

Yes

No

No

No

AUDIT

Yes

No

No

No

AUDIT_OBJ

Yes

Yes

Yes

Yes

CLUSTER

Yes

Yes

No

Yes

COMMENT

Yes

Yes

Yes

Yes

CONSTRAINT

Yes

Yes

Yes

Yes

CONTEXT

Yes

No

No

No

DB_LINK

Yes

Yes

No

No

DEFAULT_ROLE

Yes

INCLUDE_USER

No

No

DIMENSION

Yes

Yes

No

No

DIRECTORY

Yes

No

No

No

FGA_POLICY

Yes

No

No

Yes

FUNCTION

Yes

Yes

No

No

INDEX_STATISTICS

Yes

Yes

Yes

Yes

INDEX

Yes

Yes

Yes

Yes

INDEXTYPE

Yes

Yes

No

No

JAVA_SOURCE

Yes

Yes

No

No

JOB

Yes

Yes

No

No

LIBRARY

Yes

Yes

No

No

MATERIALIED_VIEW

Yes

Yes

No

No

MATERIALIZED_VIEW_LOG

Yes

Yes

No

No

OBJECT_GRANT

Yes

Yes

Yes

Yes

OPERATOR

Yes

Yes

No

No

OUTLINE

If OUTLN user's objects are returned

if user is OUTLN

No

No

PACKAGE

Yes

Yes

No

No

PACKAGE_SPEC

Yes

Yes

No

No

PACKAGE_BODY

Yes

Yes

No

No

PASSWORD_HISTORY

Yes

INCLUDE_USER

No

No

PASSWORD_VERIFY_FUNCTION

Yes

No

No

No

PROCEDURE

Yes

Yes

No

No

PROFILE

Yes

No

No

No

PROXY

Yes

No

No

No

REF_CONSTRAINT

Yes

Yes

Yes

Yes

REFRESH_GROUP

Yes

Yes

No

No

RESOURCE_COST

Yes

No

No

No

RLS_CONTEXT

Yes

No

No

Yes

RLS_GROUP

Yes

No

No

Yes

RLS_POLICY

Yes

Table data is retrieved according to policy

Table data is retrieved according to policy

Yes

ROLE

Yes

No

No

No

ROLE_GRANT

Yes

No

No

No

ROLLBACK_SEGMENT

Yes

No

No

No

SEQUENCE

Yes

Yes

No

No

SYNONYM

Yes

Yes

No

No

SYSTEM_GRANT

Yes

INCLUDE_USER

No

No

TABLE

Yes

Yes

Yes

Yes

TABLE_DATA

Yes

Yes

Yes

Yes

TABLE_STATISTICS

Yes

Yes

Yes

Yes

TABLESPACE

Yes

No

No

No

TABLESPACE_QUOTA

Yes

INCLUDE_USER

No

No

TRIGGER

Yes

Yes

Yes

Yes

TRUSTED_DB_LINK

Yes

No

No

No

TYPE

Yes

Yes

No

Yes, if the types are used by tables in the transportable set

TYPE_SPEC

Yes

Yes

No

Yes, if the types are used by tables in the transportable set

TYPE_BODY

Yes

Yes

No

Yes, if the types are used by tables in the transportable set

USER

Yes

INCLUDE_USER

No

No

VIEW

Yes

Yes

No

No

XMLSCHEMA

Yes

Yes

No

No


Return Values

An opaque handle to the class of objects. This handle is used as input to SET_FILTER, SET_COUNT, ADD_TRANSFORM, GET_QUERY, SET_PARSE_ITEM, FETCH_xxx, and CLOSE.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OBJECT_PARAM. The version or model parameter was not valid for the object_type.


OPENW Function

This function specifies the type of object to be submitted and the object model. The return value is an opaque context handle.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.OPENW
  (object_type  IN VARCHAR2,
  version       IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model         IN VARCHAR2 DEFAULT 'ORACLE') 
 RETURN NUMBER;

Parameters

Table 57-13 OPENW Function Parameters

Parameter Description

object_type

The type of object to be submitted. Valid types names and their meanings are listed in Table 57-11. The type cannot be a heterogeneous object type.

version

The version of DDL to be generated by the CONVERT function. DDL clauses that are incompatible with the version will not be generated. The legal values for this parameter are as follows:

  • COMPATIBLE - This is the default. The version of the DDL corresponds to the database compatibility level. Database compatibility must be set to 9.2.0 or higher.

  • LATEST - The version of the DDL corresponds to the database version.

  • A specific database version. As of Oracle Database 10g, this value cannot be lower than 9.2.0.

model

Specifies which view to use. Only the Oracle proprietary (ORACLE) view is supported by DBMS_METADATA.


Return Values

An opaque handle to write context. This handle is used as input to the ADD_TRANSFORM, CONVERT, PUT, and CLOSE procedures.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OBJECT_PARAM. The model parameter was not valid for the object_type.


PUT Function

This function submits an XML document containing object metadata to the database to create the object.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.PUT (
   handle     IN             NUMBER,
   document   IN             sys.XMLType,
   flags      IN             NUMBER,
   results    IN OUT NOCOPY  sys.ku$_SubmitResults)
  RETURN BOOLEAN;

DBMS_METADATA.PUT (
   handle     IN             NUMBER,
   document   IN             CLOB,
   flags      IN             NUMBER,
   results    IN OUT NOCOPY  sys.ku$_SubmitResults)
 RETURN BOOLEAN;

Parameters

Table 57-14 PUT Function Parameters

Parameter Description

handle

The handle returned from OPENW.

document

The XML document containing object metadata for the type of the OPENW handle.

flags

Reserved for future use

results

Detailed results of the operation.


Return Values

TRUE if all SQL operations succeeded; FALSE if there were any errors.

Usage Notes

The PUT function converts the XML document to DDL just as CONVERT does (applying the specified transforms in turn) and then submits each resultant DDL statement to the database. As with CONVERT, the DDL transform must be specified. The DDL statements and associated parse items are returned in the sys.ku$_SubmitResults nested table. With each DDL statement is a nested table of error lines containing any errors or exceptions raised by the statement.

The encoding of the XML document is embedded in its CLOB or XMLType representation. The version of the metadata is embedded in the XML. The generated DDL is valid for the database version specified in OPENW.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INCONSISTENT_OPERATION. The DDL transform was not specified.

  • INCOMPATIBLE_DOCUMENT. The version of the XML document is not compatible with this version of the software.


SET_COUNT Procedure

This procedure specifies the maximum number of objects to be retrieved in a single FETCH_xxx call. By default, each call to FETCH_xxx returns one object. You can use the SET_COUNT procedure to override this default. If FETCH_xxx is called from a client, specifying a count value greater than 1 can result in fewer server round trips and, therefore, improved performance.

For heterogeneous object types, a single FETCH_xxx operation only returns objects of a single object type.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.SET_COUNT (
   handle           IN NUMBER,
   value            IN NUMBER,
   object_type_path IN VARCHAR2 DEFAULT NULL);

Parameters

Table 57-15 SET_COUNT Procedure Parameters

Parameter Description

handle

The handle returned from OPEN.

value

The maximum number of objects to retrieve.

object_type_path

A path name designating the object types to which the count value applies. By default, the count value applies to the object type of the OPEN handle. When the OPEN handle designates a heterogeneous object type, behavior can be either of the following:

  • if object_type_path is omitted, the count applies to all object types within the heterogeneous collection

  • if object_type_path is specified, the count only applies to the specific node (or set of nodes) within the tree of object types forming the heterogeneous collection


Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OPERATION. SET_COUNT was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_COUNT for the current OPEN context are permitted.

  • INCONSISTENT_ARGS. object_type parameter is not consistent with handle.


SET_FILTER Procedure

This procedure specifies restrictions on the objects to be retrieved, for example, the object name or schema.

See Also:

For more information about related subprograms:

Syntax

DBMS_METADATA.SET_FILTER (
   handle           IN NUMBER,
   name             IN VARCHAR2,
   value            IN VARCHAR2,
   object_type_path IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_FILTER (
   handle            IN NUMBER,
   name              IN VARCHAR2,
   value             IN BOOLEAN DEFAULT TRUE,
   object_type_path  IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_FILTER (
   handle            IN NUMBER,
   name              IN VARCHAR2,
   value             IN NUMBER,
   object_type_path  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 57-16 SET_FILTER Procedure Parameters

Parameter Description

handle

The handle returned from OPEN.

name

The name of the filter. For each filter, Table 57-17 lists the object_type it applies to, its name, its datatype (text or Boolean) and its meaning or effect (including its default value, if any).

The Datatype column of Table 57-17 also indicates whether a text filter is an expression filter. An expression filter is the right-hand side of a SQL comparison (that is, a SQL comparison operator (=, !=, and so on.)) and the value compared against. The value must contain parentheses and quotation marks where appropriate. Note that in PL/SQL and SQL*Plus, two single quotes (not a double quote) are needed to represent an apostrophe. For example, an example of a NAME_EXPR filter in PL/SQL is as follows:

'IN (''DEPT'',''EMP'')'

The filter value is combined with a particular object attribute to produce a WHERE condition in the query that fetches the objects. In the preceding example, the filter is combined with the attribute corresponding to an object name; objects named 'DEPT' and 'EMP' are selected.

value

The value of the filter. Text, Boolean, and Numeric filters are supported.

object_type_path

A path name designating the object types to which the filter applies. By default, the filter applies to the object type of the OPEN handle. When the OPEN handle designates a heterogeneous object type, you can use this parameter to specify a filter for a specific node or set of nodes within the tree of object types that form the heterogeneous collection. See Table 57-18 for a listing of some of the values for this parameter.


Table 57-17 describes the object type, name, datatype, and meaning of the filters available with the SET_FILTER procedure.

Table 57-17 SET_FILTER: Filters

Object Type Name Datatype Meaning

Named objects

NAME

text

Objects with this exact name are selected.

Named objects

NAME_EXPR

text expression

The filter value is combined with the object attribute corresponding to the object name to produce a WHERE condition in the query that fetches the objects.

By default, all named objects of object_type are selected.

Named objects

EXCLUDE_NAME_EXPR

text expression

The filter value is combined with the attribute corresponding to the object name to specify objects that are to be excluded from the set of objects fetched.

By default, all named objects of the object type are selected.

Schema objects

SCHEMA

text

Objects in this schema are selected. If the object type is SYNONYM, specify PUBLIC to select public synonyms.

Schema objects

SCHEMA_EXPR

text expression

The filter value is combined with the attribute corresponding to the object's schema.

The default is determined as follows:

- if BASE_OBJECT_SCHEMA is specified, then objects in that schema are selected;

- otherwise, objects in the current schema are selected.

PACKAGE, TYPE

SPECIFICATION

Boolean

If TRUE, retrieve the package or type specification. Defaults to TRUE.

PACKAGE, TYPE

BODY

Boolean

If TRUE, retrieve the package or type body. Defaults to TRUE.

TABLE, CLUSTER, INDEX, TABLE_DATA, TABLE_EXPORT, TRANSPORTABLE_EXPORT

TABLESPACE

text

Objects in this tablespace (or having a partition in this tablespace) are selected.

TABLE, CLUSTER, INDEX,TABLE_DATA, TABLE_EXPORT, TRANSPORTABLE_EXPORT

TABLESPACE_EXPR

text expression

The filter value is combined with the attribute corresponding to the object's tablespace (or in the case of a partitioned table or index, the partition's tablespaces). By default, objects in all tablespaces are selected.

TABLE, objects dependent on tables

PRIMARY

Boolean

If TRUE, retrieve primary tables (that is, tables for which the secondary object bit in obj$ is clear.

Defaults to TRUE.

TABLE, objects dependent on tables

SECONDARY

Boolean

If TRUE, retrieve secondary tables (that is, tables for which the secondary object bit in obj$ is set).

Defaults to TRUE.

Dependent Objects

BASE_OBJECT_NAME

text

Objects are selected that are defined or granted on objects with this name. Specify SCHEMA for triggers on schemas. Specify DATABASE for database triggers. Column-level comments cannot be selected by column name; the base object name must be the name of the table, view, or materialized view containing the column.

Dependent Objects

BASE_OBJECT_SCHEMA

text

Objects are selected that are defined or granted on objects in this schema. If BASE_OBJECT_NAME is specified with a value other than SCHEMA or DATABASE, this defaults to the current schema.

Dependent Objects

BASE_OBJECT_NAME_EXPR

text expression

The filter value is combined with the attribute corresponding to the name of the base object.

Not valid for schema and database triggers.

Dependent Objects

EXCLUDE_BASE_OBJECT_NAME_EXPR

text expression

The filter value is combined with the attribute corresponding to the name of the base object to specify objects that are to be excluded from the set of objects fetched.

Not valid for schema and database triggers.

Dependent Objects

BASE_OBJECT_SCHEMA_EXPR

text expression

The filter value is combined with the attribute corresponding to the schema of the base object.

Dependent Objects

BASE_OBJECT_TYPE

text

The object type of the base object.

Dependent Objects

BASE_OBJECT_TYPE_EXPR

text expression

The filter value is combined with the attribute corresponding to the object type of the base object.

By default no filtering is done on object type.

Dependent Objects

BASE_OBJECT_TABLESPACE

text

The tablespace of the base object.

Dependent Objects

BASE_OBJECT_TABLESPACE_EXPR

text expression

The filter value is combined with the attribute corresponding to the tablespaces of the base object. By default, no filtering is done on the tablespace.

INDEX, TRIGGER

SYSTEM_GENERATED

Boolean

If TRUE, select indexes or triggers even if they are system-generated. If FALSE, omit system-generated indexes or triggers. Defaults to TRUE.

Granted Objects

GRANTEE

text

Objects are selected that are granted to this user or role. Specify PUBLIC for grants to PUBLIC.

Granted Objects

PRIVNAME

text

The name of the privilege or role to be granted. For TABLESPACE_QUOTA, only UNLIMITED can be specified.

Granted Objects

PRIVNAME_EXPR

text expression

The filter value is combined with the attribute corresponding to the privilege or role name. By default, all privileges/roles are returned.

Granted Objects

GRANTEE_EXPR

text expression

The filter value is combined with the attribute corresponding to the grantee name.

Granted Objects

EXCLUDE_GRANTEE_EXPR

text expression

The filter value is combined with the attribute corresponding to the grantee name to specify objects that are to be excluded from the set of objects fetched.

OBJECT_GRANT

GRANTOR

text

Object grants are selected that are granted by this user.

SYNONYM, JAVA_SOURCE, XMLSCHEMA

LONGNAME

text

A name longer than 30 characters. Objects with this exact name are selected. If the object name is 30 characters or less, the NAME filter must be used.

SYNONYM, JAVA_SOURCE, XMLSCHEMA

LONGNAME_EXPR

text

The filter value is combined with the attribute corresponding to the object's long name. By default, no filtering is done on the long name of an object.

All objects

CUSTOM_FILTER

text

The text of a WHERE condition. The condition is appended to the query that fetches the objects. By default, no custom filter is used.

The other filters are intended to meet the needs of the majority of users. Use CUSTOM_FILTER when no defined filters exists for your purpose. Of necessity such a filter depends on the detailed structure of the UDTs and views used in the query. Because filters may change from version to version, upward compatibility is not guaranteed.

SCHEMA_EXPORT

SCHEMA

text

The schema whose objects are selected.

SCHEMA_EXPORT

SCHEMA_EXPR

text expression

The filter value is either:

combined with the attribute corresponding to a schema name to produce a WHERE condition in the query that fetches schema objects,

combined with the attribute corresponding to a base schema name to produce a WHERE condition in the query that fetches dependent objects.

By default the current user's objects are selected.

SCHEMA_EXPORT

INCLUDE_USER

Boolean

If TRUE, retrieve objects containing privileged information about the user. For example, USER, PASSWORD_HISTORY, TABLESPACE_QUOTA.

Defaults to FALSE.

TABLE_EXPORT

SCHEMA

text

Objects (tables and their dependent objects) in this schema are selected.

TABLE_EXPORT

SCHEMA_EXPR

text expression

The filter value is either:

combined with the attribute corresponding to a schema name to produce a WHERE condition in the query that fetches the tables,

combined with the attribute corresponding to a base schema name to produce a WHERE condition in the query that fetches the tables' dependent objects.

By default the current user's objects are selected.

TABLE_EXPORT

NAME

text

The table with this exact name is selected along with its dependent objects.

TABLE_EXPORT

NAME_EXPR

text expression

The filter value is combined with the attribute corresponding to a table name in the queries that fetch tables and their dependent objects.

By default all tables in the selected schemas are selected, along with their dependent objects.

Heterogeneous objects

BEGIN_WITH

text

The fully qualified path name of the first object type in the heterogeneous collection to be retrieved. Objects normally fetched prior to this object type will not be retrieved.

Heterogeneous objects

BEGIN_AFTER

text

The fully qualified path name of an object type after which the heterogeneous retrieval should begin. Objects of this type will not be retrieved, nor will objects normally fetched prior to this object type.

Heterogeneous objects

END_BEFORE

text

The fully qualified path name of an object type where the heterogeneous retrieval should end. Objects of this type will not be retrieved, nor will objects normally fetched after this object type.

Heterogeneous objects

END_WITH

text

The fully qualified path name of the last object type in the heterogeneous collection to be retrieved. Objects normally fetched after this object type will not be retrieved.

Heterogeneous objects

INCLUDE_PATH_EXPR, EXCLUDE_PATH_EXPR

text expression

For these two filters, the filter value is combined with the attribute corresponding to an object type path name to produce a WHERE condition in the query that fetches the object types belonging to the heterogeneous collection. Objects of types satisfying this condition are included (INCLUDE_PATH_EXPR) or excluded (EXCLUDE_PATH_EXPR) from the set of object types fetched. Path names in the filter value do not have to be fully qualified. See Table 57-18 for valid path names that can be used with these filters.

BEGIN_WITH, BEGIN_AFTER, END_BEFORE, END_WITH, INCLUDE_PATH_EXPR, and EXCLUDE_PATH_EXPR all restrict the set of object types in the heterogeneous collection. By default, objects of all object types in the heterogeneous collection are retrieved.


Usage Notes

  • Each call to SET_FILTER causes a WHERE condition to be added to the underlying query that fetches the set of objects. The WHERE conditions are ANDed together, so you can use multiple SET_FILTER calls to refine the set of objects to be returned. For example to specify that you want the object named EMP in schema SCOTT, do the following:

    SET_FILTER(handle,'SCHEMA','SCOTT');
     SET_FILTER(handle,'NAME','EMP');
    
  • You can use the same text expression filter multiple times with different values. All the filter conditions will be applied to the query. For example, to get objects with names between Felix and Oscar, do the following:

    SET_FILTER(handle,'NAME_EXPR','>=''FELIX''');
    SET_FILTER(handle,'NAME_EXPR','<=''OSCAR''');
    
  • With SET_FILTER, you can specify the schema of objects to be retrieved, but security considerations may override this specification. If the caller is SYS or has SELECT_CATALOG_ROLE, then any object can be retrieved; otherwise, only the following can be retrieved:

    • Schema objects owned by the current user

    • Public synonyms

    • System privileges granted to the current user or to PUBLIC

    • Grants on objects for which the current user is owner, grantor, or grantee (either explicitly or as PUBLIC).

    • SCHEMA_EXPORT where the name is the current user

    • TABLE_EXPORT where SCHEMA is the current user

    If you request objects that you are not privileged to retrieve, no exception is raised; the object is not retrieved, as if it did not exist.

    In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

  • For heterogeneous object types, the BEGIN_WITH and BEGIN_AFTER filters allow restart on an object type boundary. Appropriate filter values are returned by the FETCH_XML_CLOB procedure.

    Filters on heterogeneous objects provide default values for filters on object types within the collection. You can override this default for a particular object type by specifying the appropriate filter for the specific object type path. For example, for SCHEMA_EXPORT the NAME filter specifies the schema to be fetched including all the tables in the schema, but you can further restrict this set of tables by supplying a NAME_EXPR filter explicitly for the TABLE object type path. Table 57-18 lists valid object type path names for the major heterogeneous object types along with an explanation of the scope of each path name. (The same information is available in the following catalog views: DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.) See Table 57-17 for filters defined for each path name. These path names are valid in the INCLUDE_PATH_EXPR and EXCLUDE_PATH_EXPR filters. Path names marked with an asterisk (*) are only valid in those filters; they cannot be used as values of the SET_FILTER object_type_path parameter.

Table 57-18 Object Type Path Names for Heterogeneous Object Types

Heterogeneous Type Path Name (*=valid only in xxx_PATH_EXPR) Scope

TABLE_EXPORT

AUDIT_OBJ

Object audits on the selected tables

TABLE_EXPORT

COMMENT

Table and column comments for the selected tables

TABLE_EXPORT

CONSTRAINT

Constraints (including referential constraints) on the selected tables

TABLE_EXPORT

*GRANT

Object grants on the selected tables

TABLE_EXPORT

INDEX

Indexes (including domain indexes) on the selected tables

TABLE_EXPORT

OBJECT_GRANT

Object grants on the selected tables

TABLE_EXPORT

REF_CONSTRAINT

Referential (foreign key) constraints on the selected tables

TABLE_EXPORT

STATISTICS

Statistics on the selected tables

TABLE_EXPORT

TABLE_DATA

Row data for the selected tables

TABLE_EXPORT

TRIGGER

Triggers on the selected tables

SCHEMA_EXPORT

ASSOCIATION

Statistics type associations for objects in the selected schemas

SCHEMA_EXPORT

AUDIT_OBJ

Audits on all objects in the selected schemas

SCHEMA_EXPORT

CLUSTER

Clusters in the selected schemas and their indexes

SCHEMA_EXPORT

COMMENT

Comments on all objects in the selected schemas

SCHEMA_EXPORT

CONSTRAINT

Constraints (including referential constraints) on all objects in the selected schemas

SCHEMA_EXPORT

DB_LINK

Private database links in the selected schemas

SCHEMA_EXPORT

DEFAULT_ROLE

Default roles granted to users associated with the selected schemas

SCHEMA_EXPORT

DIMENSION

Dimensions in the selected schemas

SCHEMA_EXPORT

FUNCTION

Functions in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

*GRANT

Grants on objects in the selected schemas

SCHEMA_EXPORT

INDEX

Indexes (including domain indexes) on tables and clusters in the selected schemas

SCHEMA_EXPORT

INDEXTYPE

Indextypes in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

JAVA_SOURCE

Java sources in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

JOB

Jobs in the selected schemas

SCHEMA_EXPORT

LIBRARY

External procedure libraries in the selected schemas

SCHEMA_EXPORT

MATERIALIZED_VIEW

Materialized views in the selected schemas

SCHEMA_EXPORT

MATERIALIZED_VIEW_LOG

Materialized view logs on tables in the selected schemas

SCHEMA_EXPORT

OBJECT_GRANT

Grants on objects in the selected schemas

SCHEMA_EXPORT

OPERATOR

Operators in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

PACKAGE

Packages (both specification and body) in the selected schemas, and their dependent grants and audits

SCHEMA_EXPORT

PACKAGE_BODY

Package bodies in the selected schemas

SCHEMA_EXPORT

PACKAGE_SPEC

Package specifications in the selected schemas

SCHEMA_EXPORT

PASSWORD_HISTORY

The password history for users associated with the selected schemas

SCHEMA_EXPORT

PROCEDURE

Procedures in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

REF_CONSTRAINT

Referential (foreign key) constraints on tables in the selected schemas

SCHEMA_EXPORT

REFRESH_GROUP

Refresh groups in the selected schemas

SCHEMA_EXPORT

SEQUENCE

Sequences in the selected schemas and their dependent grants and audits

SCHEMA_EXPORT

STATISTICS

Statistics on tables and indexes in the selected schemas

SCHEMA_EXPORT

SYNONYM

Private synonyms in the selected schemas

SCHEMA_EXPORT

TABLE

Tables in the selected schemas and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on)

SCHEMA_EXPORT

TABLE_DATA

Row data for tables in the selected schemas

SCHEMA_EXPORT

TABLESPACE_QUOTA

Tablespace quota granted to users associated with the selected schemas

SCHEMA_EXPORT

TRIGGER

Triggers on tables in the selected schemas

SCHEMA_EXPORT

TYPE

Types (both specification and body) in the selected schemas, and their dependent grants and audits

SCHEMA_EXPORT

TYPE_BODY

Type bodies in the selected schemas

SCHEMA_EXPORT

TYPE_SPEC

Type specifications in the selected schemas

SCHEMA_EXPORT

USER

User definitions for users associated with the selected schemas

SCHEMA_EXPORT

VIEW

Views in the selected schemas and their dependent objects (grants, constraints, comments, audits)

DATABASE_EXPORT

ASSOCIATION

Statistics type associations for objects in the database

DATABASE_EXPORT

AUDIT

Audits of SQL statements

DATABASE_EXPORT

AUDIT_OBJ

Audits on all objects in the database

DATABASE_EXPORT

CLUSTER

Clusters and their indexes

DATABASE_EXPORT

COMMENT

Comments on all objects

DATABASE_EXPORT

CONSTRAINT

Constraints (including referential constraints)

DATABASE_EXPORT

CONTEXT

Application contexts

DATABASE_EXPORT

DB_LINK

Private and public database links

DATABASE_EXPORT

DEFAULT_ROLE

Default roles granted to users in the database

DATABASE_EXPORT

DIMENSION

Dimensions in the database

DATABASE_EXPORT

DIRECTORY

Directory objects in the database

DATABASE_EXPORT

FGA_POLICY

Fine-grained audit policies

DATABASE_EXPORT

FUNCTION

Functions

DATABASE_EXPORT

* GRANT

Object and system grants

DATABASE_EXPORT

INDEX

Indexes (including domain indexes) on tables and clusters

DATABASE_EXPORT

INDEXTYPE

Indextypes and their dependent grants and audits

DATABASE_EXPORT

JAVA_SOURCE

Java sources and their dependent grants and audits

DATABASE_EXPORT

JOB

Jobs

DATABASE_EXPORT

LIBRARY

External procedure libraries

DATABASE_EXPORT

MATERIALIZED_VIEW

Materialized views

DATABASE_EXPORT

MATERIALIZED_VIEW_LOG

Materialized view logs

DATABASE_EXPORT

OBJECT_GRANT

All object grants in the database

DATABASE_EXPORT

OPERATOR

Operators and their dependent grants and audits

DATABASE_EXPORT

PACKAGE

Packages (both specification and body) and their dependent grants and audits

DATABASE_EXPORT

PACKAGE_BODY

Package bodies

DATABASE_EXPORT

PACKAGE_SPEC

Package specifications

DATABASE_EXPORT

PASSWORD_HISTORY

Password histories for database users

DATABASE_EXPORT

*PASSWORD_VERIFY_FUNCTION

The password complexity verification function

DATABASE_EXPORT

PROCEDURE

Procedures and their dependent grants and objects

DATABASE_EXPORT

PROFILE

Profiles

DATABASE_EXPORT

PROXY

Proxy authentications

DATABASE_EXPORT

REF_CONSTRAINT

Referential (foreign key) constraints on tables in the database

DATABASE_EXPORT

REFRESH_GROUP

Refresh groups

DATABASE_EXPORT

*RESOURCE_ COST

Resource cost information

DATABASE_EXPORT

RLS_CONTEXT

Fine-grained access-control driving contexts

DATABASE_EXPORT

RLS_GROUP

Fine-grained access-control policy groups

DATABASE_EXPORT

RLS_POLICY

Fine-grained access-control policies

DATABASE_EXPORT

ROLE

Roles

DATABASE_EXPORT

ROLE_GRANT

Role grants to users in the database

DATABASE_EXPORT

ROLLBACK_SEGMENT

Rollback segments

DATABASE_EXPORT

*SCHEMA (named object)

Database schemas including for each schema all related and dependent objects: user definitions and their attributes (default roles, role grants, tablespace quotas, and so on), objects in the schema (tables, view, packages, types, and so on), and their dependent objects (grants, audits, indexes, constraints, and so on). The NAME and NAME_EXPR filters can be used with this object type path name to designate the database schemas to be fetched.

DATABASE_EXPORT

SEQUENCE

Sequences

DATABASE_EXPORT

STATISTICS

Statistics on tables and indexes

DATABASE_EXPORT

SYNONYM

Public and private synonyms

DATABASE_EXPORT

SYSTEM_GRANT

System privilege grants

DATABASE_EXPORT

TABLE

Tables and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on)

DATABASE_EXPORT

TABLE_DATA

Row data for all tables

DATABASE_EXPORT

TABLESPACE

Tablespace definitions

DATABASE_EXPORT

TABLESPACE_QUOTA

Tablespace quota granted to users in the database

DATABASE_EXPORT

TRIGGER

Triggers on the database, on schemas, and on schema objects

DATABASE_EXPORT

TRUSTED_DB_LINK

Trusted links

DATABASE_EXPORT

TYPE

Types (both specification and body) and their dependent grants and audits

DATABASE_EXPORT

TYPE_BODY

Type bodies

DATABASE_EXPORT

TYPE_SPEC

Type specifications

DATABASE_EXPORT

USER

User definitions

DATABASE_EXPORT

VIEW

Views


Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OPERATION. SET_FILTER was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_FILTER are permitted.

  • INCONSISTENT_ARGS. The arguments are inconsistent. Possible inconsistencies include the following:

    • filter name not valid for the object type associated with the OPEN context

    • filter name not valid for the object_type_path

    • object_type_path not part of the collection designated by handle

    • filter value is the wrong datatype


SET_PARSE_ITEM Procedure

This procedure is used for both retrieval and submission. This procedure enables output parsing and specifies an object attribute to be parsed and returned.

Syntax

The following syntax applies when SET_PARSE_ITEM is used for object retrieval:

DBMS_METADATA.SET_PARSE_ITEM (
   handle       IN  NUMBER,
   name         IN  VARCHAR2,
   object_type  IN  VARCHAR2 DEFAULT NULL);

The following syntax applies when SET_PARSE_ITEM is used for XML submission:

DBMS_METADATA.SET_PARSE_ITEM (
   handle     IN NUMBER,
   name        IN VARCHAR2);

Parameters

Table 57-19 SET_PARSE_ITEM Procedure Parameters

Parameter Description

handle

The handle returned from OPEN (or OPENW).

name

The name of the object attribute to be parsed and returned. See Table 57-20 for the attribute object type, name, and meaning.

object_type

Designates the object type to which the parse item applies (this is an object type name, not a path name). By default, the parse item applies to the object type of the OPEN handle. When the OPEN handle designates a heterogeneous object type, behavior can be either of the following:

  • if object_type is omitted, the parse item applies to all object types within the heterogeneous collection

  • if object_type is specified, the parse item only applies to that specific object type within the collection

This parameter only applies when SET_PARSE_ITEM is used for object retrieval.


Table 57-20 describes the object type, name, and meaning of the items available in the SET_PARSE_ITEM procedure.

Table 57-20 SET_PARSE_ITEM: Parse Items

Object Type Name Meaning

All objects

VERB

If FETCH_XML_CLOB is called, no value is returned.

If FETCH_DDL is called, then for every row in the sys.ku$_ddls nested table returned by FETCH_DDL the verb in the corresponding ddlText is returned. If the ddlText is a SQL DDL statement, then the SQL verb (for example, CREATE, GRANT, AUDIT) is returned. If the ddlText is a procedure call (for example, DBMS_AQADM.CREATE_QUEUE_TABLE()) then the package.procedure-name is returned.

All objects

OBJECT_TYPE

If FETCH_XML_CLOB is called, an object type name from Table 57-11 is returned.

If FETCH_DDL is called and the ddlText is a SQL DDL statement whose verb is CREATE or ALTER, the object type as used in the DDL statement is returned (for example, TABLE, PACKAGE_BODY, and so on). Otherwise, an object type name from Table 57-11 is returned.

Schema objects

SCHEMA

The object schema is returned. If the object is not a schema object, no value is returned.

Named objects

NAME

The object name is returned. If the object is not a named object, no value is returned.

TABLE, TABLE_DATA, INDEX

TABLESPACE

The name of the object's tablespace or, if the object is a partitioned table, the default tablespace is returned. For a TABLE_DATA object, this is always the tablespace where the rows are stored.

TRIGGER

ENABLE

If the trigger is enabled, ENABLE is returned. If the trigger is disabled, DISABLE is returned.

OBJECT_GRANT, TABLESPACE_QUOTA

GRANTOR

The grantor is returned.

Dependent objects (including domain index secondary tables)

BASE_OBJECT_NAME

The name of the base object is returned. If the object is not a dependent object, no value is returned.

Dependent objects (including domain index secondary tables)

BASE_OBJECT_SCHEMA

The schema of the base object is returned. If the object is not a dependent object, no value is returned.

Dependent objects (including domain index secondary tables)

BASE_OBJECT_TYPE

The object type of the base object is returned. If the object is not a dependent object, no value is returned.

Granted objects

GRANTEE

The grantee is returned. If the object is not a granted object, no value is returned.


Usage Notes

These notes apply when using SET_PARSE_ITEM to retrieve objects.

By default, the FETCH_xxx routines return an object's metadata as XML or creation DDL. By calling SET_PARSE_ITEM you can request that individual attributes of the object be returned as well.

You can call SET_PARSE_ITEM multiple times to ask for multiple items to be parsed and returned. Parsed items are returned in the sys.ku$_parsed_items nested table.

For TABLE_DATA objects, the following parse item return values are of interest:

If Object Is NAME, SCHEMA BASE_OBJECT_NAME, BASE_OBJECT_SCHEMA
nonpartitioned table table name, schema table name, schema
table partition partition name, schema table name, schema
nested table storage table name, schema name and schema of top-level table (not the parent nested table)

Tables are not usually thought of as dependent objects. However, secondary tables for domain indexes are dependent on the domain indexes. Consequently, the BASE_OBJECT_NAME, BASE_OBJECT_SCHEMA and BASE_OBJECT_TYPE parse items for secondary TABLE objects return the name, schema, and type of the domain index.

See Also:

By default, the CONVERT and PUT procedures simply transform an object's XML metadata to DDL. By calling SET_PARSE_ITEM you can request that individual attributes of the object be returned as well.

Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OPERATION. SET_PARSE_ITEM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_PARSE_ITEM are permitted.

  • INCONSISTENT_ARGS. The attribute name is not valid for the object type associated with the OPEN context.


SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures

These procedures are used for both retrieval and submission. SET_TRANSFORM_PARAM and SET_REMAP_PARAM specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform.

Syntax

DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   value              IN VARCHAR2,
   object_type        IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   value              IN BOOLEAN DEFAULT TRUE,
   object_type        IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   value              IN NUMBER,
   object_type        IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_REMAP_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   old_value          IN VARCHAR2,
   new_value          IN VARCHAR2,
   object_type        IN VARCHAR2 DEFAULT NULL);

Parameters

Table 57-21 describes the parameters for the SET_TRANSFORM_PARAM and SET_REMAP_PARAM procedures.

Table 57-21 SET_TRANSFORM_PARAM and SET_REMAP_PARAM Parameters

Parameters Description

transform_handle

Either (1) the handle returned from ADD_TRANSFORM, or (2) the enumerated constant SESSION_TRANSFORM that designates the DDL transform for the whole session.

Note that the handle returned by OPEN is not a valid transform handle.

For SET_REMAP_PARAM, the transform handle must designate the MODIFY transform.

name

The name of the parameter.

Table 57-22 lists the transform parameters defined for the DDL transform, specifying the object_type it applies to, its datatype, and its meaning or effect. This includes its default value, if any, and whether the parameter is additive.

Table 57-23 describes the parameters for the MODIFY transform in the SET_TRANSFORM_PARAM procedure.

Table 57-24 describes the parameters for the MODIFY transform in the SET_REMAP_PARAM procedure.

value

The value of the transform. This parameter is valid only for SET_TRANSFORM_PARAM.

old_value

The old value for the remapping. This parameter is valid only for SET_REMAP_PARAM.

new_value

The new value for the remapping. This parameter is valid only for SET_REMAP_PARAM.

object_type

Designates the object type to which the transform or remap parameter applies. By default, it applies to the same object type as the transform. In cases where the transform applies to all object types within a heterogeneous collection, the following apply:

  • If object_type is omitted, the parameter applies to all applicable object types within the heterogeneous collection.

  • If object_type is specified, the parameter only applies to that object type.

This allows a caller who has added a transform to a heterogeneous collection to specify different transform parameters for different object types within the collection.


Table 57-22 describes the object type, name, datatype, and meaning of the parameters for the DDL transform in the SET_TRANSFORM_PARAM procedure.

Table 57-22 SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform

Object Type Name Datatype Meaning

All objects

PRETTY

BOOLEAN

If TRUE, format the output with indentation and line feeds. Defaults to TRUE.

All objects

SQLTERMINATOR

BOOLEAN

If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.

TABLE

SEGMENT_ATTRIBUTES

BOOLEAN

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

TABLE

STORAGE

BOOLEAN

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLE

TABLESPACE

BOOLEAN

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLE

CONSTRAINTS

BOOLEAN

If TRUE, emit all non-referential table constraints. Defaults to TRUE.

TABLE

REF_CONSTRAINTS

BOOLEAN

If TRUE, emit all referential constraints (foreign keys). Defaults to TRUE.

TABLE

CONSTRAINTS_AS_ALTER

BOOLEAN

If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.

TABLE

OID

BOOLEAN

If TRUE, emit the OID clause for object tables. Defaults to FALSE.

TABLE

SIZE_BYTE_KEYWORD

BOOLEAN

If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.

INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER, TABLESPACE

SEGMENT_ATTRIBUTES

BOOLEAN

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER

STORAGE

BOOLEAN

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

INDEX, CONSTRAINT, ROLLBACK_SEGMENT, CLUSTER

TABLESPACE

BOOLEAN

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TYPE

SPECIFICATION

BOOLEAN

If TRUE, emit the type specification. Defaults to TRUE.

TYPE

BODY

BOOLEAN

If TRUE, emit the type body. Defaults to TRUE.

TYPE

OID

BOOLEAN

If TRUE, emit the OID clause. Defaults to FALSE.

PACKAGE

SPECIFICATION

BOOLEAN

If TRUE, emit the package specification. Defaults to TRUE.

PACKAGE

BODY

BOOLEAN

If TRUE, emit the package body. Defaults to TRUE.

VIEW

FORCE

BOOLEAN

If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.

OUTLINE

INSERT

BOOLEAN

If TRUE, emit the INSERT statements into the OL$ dictionary tables that will create the outline and its hints. If FALSE, emit a CREATE OUTLINE statement. Defaults to FALSE.

Note: This object type is being deprecated.

All objects

DEFAULT

BOOLEAN

Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.

All objects

INHERIT

BOOLEAN

If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.

ROLE

REVOKE_FROM

Text

The name of a user from whom the role must be revoked. If this is a non-null string and if the CREATE ROLE statement grants you the role, a REVOKE statement is emitted after the CREATE ROLE.

Note: When you issue a CREATE ROLE statement, Oracle may grant you the role. You can use this transform parameter to undo the grant.

Defaults to null string.

TABLESPACE

REUSE

BOOLEAN

If TRUE, include the REUSE parameter for datafiles in a tablespace to indicate that existing files can be reused.

Defaults to FALSE.

CLUSTER, INDEX, ROLLBACK_SEGMENT, TABLE, TABLESPACE

PCTSPACE

NUMBER

A number representing the percentage by which space allocation for the object type is to be modified. The value is the number of one-hundreths of the current allocation. For example, 100 means 100%.

If the object type is TABLESPACE, the following size values are affected:

- in file specifications, the value of SIZE

- MINIMUM EXTENT

- EXTENT MANAGEMENT LOCAL UNIFORM SIZE

For other object types, INITIAL and NEXT are affected.


Table 57-23 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_TRANSFORM_PARAM procedure.

Table 57-23 SET_TRANSFORM_PARAM: Transform Parameters for the MODIFY Transform

Object Type Name Datatype Meaning

All objects

OBJECT_ROW

NUMBER

A number designating the object row for an object. The object in the document that corresponds to this number will be copied to the output document.

This parameter is additive.

By default, all objects are copied to the output document.


Table 57-24 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_REMAP_PARAM procedure.

Table 57-24 SET_REMAP_PARAM: Transform Parameters for the MODIFY Transform

Object Type Name Datatype Meaning

LIBRARY, TABLESPACE, DIRECTORY

REMAP_DATAFILE

Text

Objects in the document will have their filespecs renamed as follows: any filespec matching old_value will be changed to new_value. Filespecs should not be enclosed in quotes.

This parameter is additive.

By default, filespecs are not renamed.

Schema Objects, Dependent Objects, Granted Objects, USER

REMAP_SCHEMA

Text

Any schema object in the document whose name matches old_value will have its schema name changed to new_value.

Any dependent object whose base object schema name matches old_value will have its base object schema name changed to new_value.

Any granted object whose grantee name matches old_value will have its grantee name changed to new_value.

Any user whose name matches old_value will have its name changed to new_value.

This parameter is additive.

By default, schemas are not remapped.

TABLE, CLUSTER, CONSTRAINT, INDEX, ROLLBACK_SEGMENT, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, TABLESPACE_QUOTA

REMAP_TABLESPACE

Text

Objects in the document will have their tablespaces renamed as follows: any tablespace name matching old_value will be changed to new_value.

This parameter is additive.

By default, tablespaces are not remapped.


Exceptions

  • INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • INVALID_OPERATION. Either SET_TRANSFORM_PARAM or SET_REMAP_PARAM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_TRANSFORM_PARAM or SET_REMAP_PARAM are permitted.

  • INCONSISTENT_ARGS. The arguments are inconsistent. This can mean the following:

    • The transform parameter name is not valid for the object type associated with the OPEN context or for the transform associated with the transform handle.

    • The transform applies to all object types in a heterogeneous collection, but object_type is not part of the collection.

Usage Notes

XSLT allows parameters to be passed to stylesheets. You call SET_TRANSFORM_PARAM or SET_REMAP_PARAM to specify the value of a parameter to be passed to the stylesheet identified by transform_handle.

Normally, if you call SET_TRANSFORM_PARAMETER multiple times for the same parameter name, each call overrides the prior call. For example, the following sequence simply sets the STORAGE transform parameter to TRUE.

SET_TRANSFORM_PARAM(tr_handle,'STORAGE',false);
SET_TRANSFORM_PARAM(tr_handle,'STORAGE',true);

However, some transform parameters are additive which means that all specified parameter values are applied to the document, not just the last one. For example, the OBJECT_ROW parameter to the MODIFY transform is additive. If you specify the following, then both specified rows are copied to the output document.

SET_TRANSFORM_PARAM(tr_handle,'OBJECT_ROW',5);
SET_TRANSFORM_PARAM(tr_handle,'OBJECT_ROW',8);

The REMAP_TABLESPACE parameter is also additive. If you specify the following, then tablespaces TBS1 and TBS3 are changed to TBS2 and TBS4, respectively.

SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS1','TBS2');
SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS3','TBS4');

The order in which the transformations are performed is undefined. For example, if you specify the following, the result is undefined.

SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS1','TBS2');
SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS2','TBS3');

Note:

The number of remap parameters that can be specified for a MODIFY transform is limited to ten. That is, you can specify up to ten REMAP_DATAFILE parameters, up to ten REMAP_SCHEMA parameters and so on. Additional instances are ignored. To work around this, you can perform another DBMS_METADATA.ADD_TRANSFORM and specify additional remap parameters.

The GET_DDL, GET_DEPENDENT_DDL, and GET_GRANTED_DDL functions allow the casual browser to extract the creation DDL for an object. So that you can specify transform parameters, this package defines an enumerated constant SESSION_TRANSFORM as the handle of the DDL transform at the session level. You can call SET_TRANSFORM_PARAM using DBMS_METADATA.SESSION_TRANSFORM as the transform handle to set transform parameters for the whole session. GET_DDL, GET_DEPENDENT_DDL, and GET GRANTED_DDL inherit these parameters when they invoke the DDL transform.

Note:

The enumerated constant must be prefixed with the package name DBMS_METADATA.SESSION_TRANSFORM.


출처 : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBHHHBG
반응형

+ Recent posts