当前位置：首页 > news >正文

实例讲解如何查找某个对象的定义情况

news 来源：原创 2024/5/19 6:24:11

[oracle@1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 3月 18 02:23:03 2011

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@WUGQ10G> create user wugq identified by wugq;

用户已创建。

sys@WUGQ10G> grant resource,connect,plustrace to wugq;

授权成功。

sys@WUGQ10G> connect wugq/wugq
已连接。
wugq@WUGQ10G> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
WUGQ UNLIMITED TABLESPACE NO

wugq@WUGQ10G> select * from user_role_privs;

USERNAME         GRANTED_ROLE        ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
WUGQ          CONNECT         NO YES NO
WUGQ          PLUSTRACE        NO YES NO
WUGQ          RESOURCE         NO YES NO

wugq@WUGQ10G> select count(*) from v$fixed_table;
select count(*) from v$fixed_table
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

wugq@WUGQ10G> connect / as sysdba
已连接。
sys@WUGQ10G> grant select on v_$fixed_table to wugq;

授权成功。

sys@WUGQ10G> connect wugq/wugq
已连接。

wugq@WUGQ10G> select count(*) from v$fixed_table;

COUNT(*)
----------
1383

wugq@WUGQ10G> connect / as sysdba
已连接。
sys@WUGQ10G> grant select any table to wugq;

授权成功。

scott@WUGQ10G> select count(*) from v$fixed_table;

COUNT(*)
----------
1383

scott@WUGQ10G> select count(*) from v_$fixed_table;
select count(*) from v_$fixed_table
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

scott@WUGQ10G> select count(*) from sys.v_$fixed_table;

COUNT(*)
----------
1383

scott@WUGQ10G> select * from user_role_privs;

USERNAME         GRANTED_ROLE        ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT          CONNECT         NO YES NO
SCOTT          DBA         NO YES NO
SCOTT          RESOURCE         NO YES NO

scott@WUGQ10G> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO

scott@WUGQ10G> select count(*) from v$fixed_table;

COUNT(*)
----------
1383

scott@WUGQ10G> select * from v_$fixed_table where rownum<2;
select * from v_$fixed_table where rownum<2
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

sys@WUGQ10G> select * from v$fixed_table where rownum<2;

NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA 4294950912 TABLE 0

sys@WUGQ10G> select * from sys.v_$fixed_table where rownum<2;

NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA 4294950912 TABLE 0

scott@WUGQ10G> set heading off echo off long 50000 pages 10000
scott@WUGQ10G> select * from v$fixed_view_definition where view_name='V$FIXED_TABLE';

V$FIXED_TABLE
select NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance')

scott@WUGQ10G

> select * from v$fixed_view_definition where view_name='GV$FIXED_TABLE';

GV$FIXED_TABLE
select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqf
viobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x
$kqfdt

scott@WUGQ10G> desc v$indexed_fixed_column
名称             是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME       VARCHAR2(30)
INDEX_NUMBER       NUMBER
COLUMN_NAME       VARCHAR2(30)
COLUMN_POSITION      NUMBER

scott@WUGQ10G> select * from v$indexed_fixed_column where table_name='X$KQFTA';

X$KQFTA 1 ADDR 0
X$KQFTA 2 INDX 0

scott@WUGQ10G> select * from v$fixed_view_defintion where view_name='GV$PX_PROCESS';
select * from v$fixed_view_defintion where view_name='GV$PX_PROCESS'
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

scott@WUGQ10G> select * from v$fixed_view_definition where view_name='GV$PX_PROCESS';

GV$PX_PROCESS
select a.inst_id, a.kxfpdpnam, decode(bitand(a.kxfpdpflg, 16), 0, 'IN USE', 'AVAILABLE'), b.pid, a
.kxfpdpspid, c.sid, c.serial# from x$kxfpdp a, V$PROCESS b, V$SESSION c where bitand(kxfpdpflg, 8)
!= 0 and a.kxfpdpspid = b.SPID and a.kxfpdpspid = c.PROCESS(+)

scott@WUGQ10G> select * from v$fixed_view_definition where view_name='V$PX_PROCESS';

V$PX_PROCESS
select SERVER_NAME, STATUS, PID, SPID, SID, SERIAL# from GV$PX_PROCESS where inst_id = USERENV('In
stance')

scott@WUGQ10G> desc dba_views
名称             是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
OWNER             NOT NULL VARCHAR2(30)
VIEW_NAME            NOT NULL VARCHAR2(30)
TEXT_LENGTH       NUMBER
TEXT        LONG
TYPE_TEXT_LENGTH      NUMBER
TYPE_TEXT       VARCHAR2(4000)
OID_TEXT_LENGTH      NUMBER
OID_TEXT       VARCHAR2(4000)
VIEW_TYPE_OWNER      VARCHAR2(30)
VIEW_TYPE       VARCHAR2(30)
SUPERVIEW_NAME       VARCHAR2(30)

scott@WUGQ10G> set heading off echo off long 1000000000 pages 10000
scott@WUGQ10G> select text from dba_views where view_name='DBA_USERS';

select u.name, u.user#, u.password,
       m.status,
       decode(u.astatus, 4, u.ltime,
    5, u.ltime,
    6, u.ltime,
    8, u.ltime,
    9, u.ltime,
    10, u.ltime, to_date(NULL)),
       decode(u.astatus,
       1, u.exptime,
       2, u.exptime,
       5, u.exptime,
       6, u.exptime,
       9, u.exptime,
       10, u.exptime,
       decode(u.ptime, '', to_date(NULL),
  decode(pr.limit#, 2147483647, to_date(NULL),
   decode(pr.limit#, 0,
     decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
       dp.limit#/86400),
     u.ptime + pr.limit#/86400)))),
       dts.name, tts.name, u.ctime, p.name,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
       u.ext_username
       from sys.user$ u left outer join sys.resource_group_mapping$ cgm
     on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
  cgm.value = u.name),
     sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
     sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type#=1
       and dp.resource#=1
       and pr.type# = 1
       and pr.resource# = 1

scott@WUGQ10G> select substr(table_name,1,20) tabname,
2 substr(column_name,1,20) column_name,
3 rtrim(data_type)||'('||data_length')' from dba_tab_columns
4 where owner='&username'
5 /
输入 username 的值: SCOTT
原值 4: where owner='&username'
新值 4: where owner='SCOTT'
rtrim(data_type)||'('||data_length')' from dba_tab_columns

scott@WUGQ10G> select dbms_metadata.get_ddl(upper('TABLE'),upper('EMP')) from dual;

CREATE TABLE "SCOTT"."EMP"
   ( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
   REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) 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"

scott@WUGQ10G>

scott@WUGQ10G> select dbms_metadata.get_ddl(upper('TABLE'),upper('DEPT')) from dual;

CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) 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"

scott@WUGQ10G>

scott@WUGQ10G> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;