How to find a definition of an object (V $

xiaoxiao2021-03-06 69

It is often encountered that the definition of an object is often encountered, and the following is discussed separately for different types of objects:

First, V $ view and X $ view

Ordinary users cannot access V $ view: SQL> conn lunar / lunar @ TEST1 is connected. SQL> SELECT * from user_sys_privs;

Username privilege admin_option ---------------------------------- ----------------------------------

SQL> SELECT * from user_role_privs;

Username granted_role admin_option default_role os_grand ----------------------------------------------- ---------------------------------- Lunar Connect No Yes Nolunar Resource No Yes Nopublic Plustrace No Yes NO

SQL> SELECT Count (*) from v $ fixed_table;

SELECT Count (*) from V $ fixed_table

ORA-00942: Table or view does not exist

Must be authorized: SQL> CONN / @ TEST1 AS SYSDBA is connected. SQL> Grant SELECT ON V_ $ FIXED_TABLE to LUNAR;

Authorized success. SQL> Conn Lunar / Lunar @ TEST1 is connected. SQL> Get an authorized normal user can only access the view of the V $ start, instead of directly accessing a view of the V_ $ start, because the V $ view is the public synonym of V_ $ view (public synonym) To access v_ $ must Bring SYS.V_ $, such as SQL> Select Count (*) from V $ Fixed_Table;

COUNT (*) ---------- 912

SQL> SELECT Count (*) from v_ $ fixed_table;

SELECT Count (*) from v_ $ fixed_table

ORA-00942: Table or view does not exist

SQL> SELECT Count (*) from sys.v_ $ fixed_table;

COUNT (*) ---------- 912

SQL>

You can also grant user select any Table permissions so that this user can access all V $: SQL> Grant Select Any Table To Lunar;

Authorized success.

SQL> SELECT * from user_role_privs;

Username privilege admin_option ---------------------------------- ---------------------------------- Lunar Select Any Table No

SQL> SELECT Count (*) from v $ fixed_table;

COUNT (*) ---------- 912

SQL> SELECT * FROM V $ fixed_table where rownum <2;

Name Object_id type Table_num ------------------------------------------------------------- --------- x $ kqfta 4294950912 TABLE 0

SQL> SELECT * FROM V_ $ FIXED_TABLE WHERE ROWNUM <2;

Select * from v_ $ fixed_table where rownum <2

ORA-00942: Table or view does not exist

SQL> Select * from sys.v_ $ fixed_table where rownum <2;

Name Object_id type Table_num ------------------------------------------------------------- --------- x $ kqfta 4294950912 TABLE 0

SQL>

By querying the V $ fixed_table view, we can see most V $ view and some x $ view (there are some Oracle unapproved views).

So what is the composition of these V $ view? By querying the V $ fixed_view_definition view, you can see that these V $ view creation statements SQL> CONN / @ Test1 AS SYSDBA is connected. SQL> Grant Select Any Table To Lunar;

Authorized success.

SQL> Conn Lunar / Lunar @ TEST1 is connected. SQL>

SQL> set heading off echo off long 50000 pages 10000SQL> 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' SQL>

Select Name, Object_ID, TYPE, TABLE_NUM from GV $ fixed_table where inst_id = uchesserenv ('instance ")

So what is the definition of this GV $ fixed_table view? SQL> 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, kqfviobj, 'VIEW', 65537 From x $ kqfvi union all select inst_id, kqfdtnam, kqfdtobj, 'table', 65537 from x $ kqfdt

SQL>

select inst_id, kqftanam, kqftaobj, 'TABLE', indx from x $ kqfta union all select inst_id, kqfvinam, kqfviobj, 'VIEW', 65537 from x $ kqfvi union all select inst_id, kqfdtnam, kqfdtobj, 'TABLE', 65537 from x $ KQFDT

This way we found the minimum level of the V $ view, that is, a V $ view consists of which X $ table. To find the index information of the underlying X $ table, you can query V $ indexed_fixed_column: SQL> Desc v $ indexed_fixed_columnname type nullable default comments ----------------------- ---------- -------------- Table_name varchar2 (30) y index_number number y colorn_name varcha2 (30) y colorn_position number y

SQL> For example: SQL> SELECT * FROM V $ indexed_fixed_column where table_name = 'x $ kqfta';

TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------- --------------- x $ kqfta 1 addr 0x $ kqfta 2 INDX 0SQL>

In general, the definition of V $ view and GV $ view is the same, only information for the instance ID included in the GV $ view, which is often used in the OPS or RAC, and there are also a few V $ view and GV $ view. the definition is a difference, such as GV $ PX_PROCESS and V $ PX_PROCESS: SQL> 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 ( )

SQL> 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 ( )

SQL> 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 ( 'Instance')

SQL> SELECT Server_Name, Status, PID, SPID, SID, Serial # from GV $ PX_PROCESS Where INST_ID = Userenv ('Instance')

Second, how does the composition of the data dictionary get a definition of a data dictionary table? SQL> DBA_VIEWSNAME TYPE NULLABLE DEFAULT Comments ---------------------------------- ---- ---- ----------------------------------------------- ------------ OWNER VARCHAR2 (30) Owner of the view VIEW_NAME VARCHAR2 (30) Name of the view tEXT_LENGTH NUMBER Y Length of the view text tEXT LONG Y View text TYPE_TEXT_LENGTH NUMBER Y Length of the type Clause of the object view type_text varcha2 (4000) Y TYPE CLAUSE OF THE OBJECT VIEW OID_TEXT_LENGTH NUMBER Y Length of The with Object Oid Clause of The Object View OID_TEXT VARCHAR2 (4000) Y WITH OBJECT OID clause of (30) Y Owner the object view VIEW_TYPE_OWNER VARCHAR2 of the type of the view if the view is an object view VIEW_TYPE VARCHAR2 (30) Y Type of the view if the view is an object View superview_name varchar2 (30) y name of the superview, if View is a subviewsql>

SQL> SET Heading Off Echo Off LONG 1000000000 Pages 10000SQL> 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 , 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, u.defschclass, u.ext_username from sys.user $ u, sys.ts $ DTS, SYS.TS $ TTS, SYS.PROFNAME $ P, SYS.USER_ASTATUS_MAP M, SYS.PROFILE $ P, 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 pra.Type # = 1 and pr.Resource # = 1sql>

Third, how do I find the definition of a user-defined table? Before Oracle 9i, you can use the following method: SQL> 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 = '& username' 5 / TabName Column_name RTRIM (DATA_TYPE) || ('

|| DATA_LE --------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------- ------------- Bonus ename varcha2 (10) Bonus Job Varchar2 (9) Bonus Sal Number (22) Bonus Comm number (22) Dept DEPTNO NUMBER (22) DEPT DNAME VARCHAR2 (14) DEPT Loc Varchar2 (13) Dummy Dummy Number (22) EMP Empno Numb EMP JOB VARCHAR2 (9) Emp Mgr Number (22) EMP HIREDATE DATE (7) Emp Sal Number (22) Emp Comm number (22) Emp Deptno Number (22) Salgrade Grade Number 22) Salgrade

Losal Number (22) Salgrade Hisal Number (22) 19 Rows SELECTED

SQL>

From Oracle 9i, you can use dbms_metata.get_ddl to find the definition of the object, for example:

SQL> @C: /TEMP/get_obj_sql.sqlSQL> set heading off echo off pages 10000 long 90000 object_type of input values: TABLE object_name of input values: EMP object_owner of input values: LUNAR original value 1: select dbms_metadata.get_ddl (upper ( '& Object_type'), Upper ('& Object_name'), Upper ('& Object_OWNER')) from DUAL New Value 1: SELECT DBMS_METADATA.GET_DDL (Upper ('Table'), Upper ('EMP'), Upper ('Lunar') From Dual

Create Table "Lunar". "EMPNO" Number (4,0) Not null enable, "ename" varcha2 (10), "Job" varchar2 (9), "Mgr" Number (4, 0), " Hiredate "Date," Sal "Number (7, 2)," Comm "Number (7, 2)," Deptno "Number (2,0)) PCTFREE 10 PCTUSED 40 IITRENS 1 MAXTRANS 255 NOCOMPRESS Logging Storage Storage 448576 Mineltnts 1 maxExtents 2147483645 Pctincrease 0 FreeElists 1 FreeElist Groups 1 Buffer_Pool Default) TableSpace "System"

SQL> Note that this query is required for temporary table space, so if there is not enough query, there will be problems: SQL> @c: /temp/get_obj_sql.sqlsql> set Heading Off Echo Off Pages 10000 long 90000 Enter Object_Type Found: TABLE input object_name values: DEPT input object_owner of: LUNAR original value 1: select dbms_metadata.get_ddl (upper ( '& OBJECT_TYPE'), upper ( '& oBJECT_NAME'), upper ( '& oBJECT_OWNER')) from dual new value 1 : SELECT DBMS_METADATA.GET_DDL (Upper ('Table'), Upper ('Dept'), Upper ('Lunar')) from Dualerror: ORA-25153: Temporary Table Space is empty ORA-06512: in "sys.dbms_lob", Line 424RA-06512: In "sys.dbms_metata", line 557ora-06512: in "sys.dbms_metadata", line 1221ra-06512: In line 1 unselected line

SQL> SQL> SELECT NAME FROM V $ TEMPFILE;

Unselected

SQL> SELECT NAME FROM V $ TABLESPACE;

Name ------------------------------ Systemundotbs1TempindXUsers

SQL> ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE 'D: /oracle92/oradata/test1/TEMP01.DBF' SIZE 10M; ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE 'D: /oracle92/oradata/test1/TEMP01.DBF' SIZE 10M * ERROR located Chapter 1: ORA-00940: Invalid Alter Command

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D: /Oracle92/oradata/test1/temp01.dbf' size 10m;

The table space has been changed.

DBMS_METADATA.GET_DDL can also be used to query the creation statement of other objects, the method is as follows; select dbms_metata.get_ddl ('object type', 'object name ",' username ') from DUAL

For example: Oracle @ cs_db02: / arch1 / lunar / Tools> get_obj_sql.sh procedure aa miscconnected.

Create or Replace Procedure "MISC". "Aa" isbegindelete from error_tip; end aa;

Oracle @ cs_db02: / arch1 / lunar / Tools>

转载请注明原文地址:https://www.9cbs.com/read-88133.html

9cbs

New Post(0)