| |
 |
|
Oracle Tips by Burleson |
Punch a View from Oracle
In this exercise you will need to use the
dbms_metadata package to extract the view definition for the
DBA_OBJECTS view.
As we may know, the DBA_OBJECTS view is owned
by the SYS user and consists of a complex SQL join of internal X$
tables.
Your task is to invoke dbms_metadata to punch
the source code for the SYS.DBA_OBJECTS view.
ANSWER
set pagesize
0
set long 90000
connect
system/manager
spool t.lst
SELECT
DBMS_METADATA.GET_DDL('VIEW','DBA_OBJECTS','SYS') from dual;
Here is the output:
CREATE OR
REPLACE FORCE VIEW "SYS"."DBA_OBJECTS" ("OWNER", "OBJECT_NAME", "SUB
OBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED",
"LAST_DDL
_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY")
AS
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3,
'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9,
'PACKAGE',
11, 'PACKAGE BODY', 12,
'TRIGGER',
13, 'TYPE', 14, 'TYPE
BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA
RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX
SUBPARTITION',
39, 'LOB PARTITION', 40, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43,
'DIMENSION',
44, 'CONTEXT', 47, 'RESOURCE
PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA
DATA',
57, 'SECURITY PROFILE',
'UNDEFINED'),
o.ctime,
o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N')
from sys.obj$ o, sys.user$ u
where o.owner# =
u.user#
and o.linkname is
null
and (o.type# not in (1 /* INDEX - handled below
*/,
10 /* NON-EXISTENT
*/)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$i
where i.obj# =
o.obj#
and i.type# in (1, 2, 3, 4, 6, 7,
9))))
and o.name !=
'_NEXT_OBJECT'
and o.name !=
'_default_auditing_options_'
union all
select u.name, l.name, NULL, to_number(null),
to_number(null),
'DATABASE
LINK',
l.ctime, to_date(null), NULL, 'VALID','N','N',
'N'
from sys.link$ l, sys.user$
u
where l.owner# = u.user#
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology. |
