数据库常用数据字典

1、Static Data Dictionary ViewsIn Trusted Oracle Server, each of the dictionary tables and views contains a column that indicates the label of each row in the table or view. Trusted Oracle also provides some additional dictionary tables and views, and some Oracle8 dictionary tables and views contain co

2、lumns that support compatibility with Trusted Oracle applications. See your Trusted Oracle documentation for more information about Trusted Oracle dictionary tables and views. Data Dictionary ViewsThe following is an alphabetical reference of the data dictionary views accessible to all users of an O

3、racle Server. Most views can be accessed by any user with the CREATE_SESSION privilege. The data dictionary views that begin with DBA_ are restricted. These views can be accessed only by users with the SELECT_ANY_TABLE privilege. This privilege is assigned to the DBA role when the system is initiall

4、y installed. ALL_ALL_TABLESThis view describes all of the tables (object tables and relational tables) accessible to the user. ALL_INDEXESThis view contains descriptions of indexes on tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE. This view supports p

5、arallel partitioned index scans. ALL_SEQUENCESThis view lists descriptions of sequences accessible to the user. ALL_TABLESThis view contains descriptions of relational tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE. ALL_TRIGGERSThis view lists trigger

6、information for triggers owned by the user, triggers on tables owned by the user, or all triggers if the user has the CREATE ANY TRIGGER privilege. ALL_USERSThis view contains information about all users of the database. ALL_VIEWSThis view lists the text of views accessible to the user. DBA_ALL_TABL

7、ESThis view displays descriptions of all tables (object tables and relational tables) in the database.DBA_CONSTRAINTSThis view contains constraint definitions on all tables.DBA_DATA_FILESThis view contains information about database files. DBA_EXTENTSThis view lists the extents comprising all segmen

8、ts in the database. DBA_FREE_SPACEThis view lists the free extents in all tablespaces. DBA_INDEXESThis view contains descriptions for all indexes in the database. To gather statistics for this view, use the SQL command ANALYZE. This view supports parallel partitioned index scans. DBA_JOBSThis view l

9、ists all jobs in the database. DBA_ROLESThis view lists all roles that exist in the database. DBA_ROLE_PRIVSThis view lists roles granted to users and roles. DBA_ROLLBACK_SEGSThis view contains descriptions of rollback segments. DBA_SEGMENTSThis view contains information about storage allocated for

10、all database segments. DBA_TABLESPACESThis view contains descriptions of all tablespaces. DBA_TRIGGERSThis view lists all triggers in the database. DBA_TS_QUOTASThis view lists tablespace quotas for all users. DBA_VIEWSThis view contains the text of all views in the database. DICTIONARYThis view con

11、tains descriptions of data dictionary tables and views.USER_TABLES This view contains a description of the user's own relational tables. To gather statistics for this view, use the SQL command ANALYZE. USER_TABLESPACES This view contains descriptions of accessible tablespaces. USER_TRIGGERS This

12、 view contains descriptions of the user's triggers. Dynamic Performance ViewsThe Oracle Server contains a set of underlying views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuousl

13、y updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated

14、or altered by the user. The file CATALOG.SQL contains definitions of the views and public synonyms for the dynamic performance views. You must run CATALOG.SQL to create these views and synonyms. V$ ViewsDynamic performance views are identified by the prefix V_$. Public synonyms for these views have

15、the prefix V$. Database administrators or users should only access the V$ objects, not the V_$ objects. The dynamic performance views are used by Enterprise Manager and Oracle Trace, which is the primary interface for accessing information about system performance. Suggestion: Once the instance is s

16、tarted, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted. Warning: Information about the dynamic performance views is presented for completeness only; this information does not imply a commitment to support these views in the futu

17、re. GV$ ViewsIn Oracle, there is an additional class of fixed views, the GV$ (Global V$) fixed views. For each of the V$ views described in this chapter (with the exception of V$CACHE_LOCK, V$LOCK_ACTIVITY, V$LOCKS_WITH_COLLISIONS, and V$ROLLNAME), there is a GV$ view. In a parallel server environme

18、nt, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view possesses an additional column named INST_ID with type integer. The INST_ID column displays the instance number from which the associated V$ view information was o

19、btained. The INST_ID column can be used as a filter to retrieve V$ information from a subset of available instances. For example, the query: SELECT * FROM GV$LOCK WHERE INST_ID = 2 OR INST_ID = 5 retrieves the information from the V$ views on instances 2 and 5. The GV$ views can be used to return in

20、formation on groups of instances defined with the OPS_ADMIN_GROUP parameter. Restrictions on GV$ Views GV$ views have the following restrictions: · The value of the PARALLEL_MAX_SERVERS parameter must be greater than zero on all instances mounting the database. · The OPS_ADMIN_GROUP parame

21、ter must be defined with at least one member for a query to successfully complete. V$BGPROCESS This view describes the background processes. V$BUFFER_POOLThis view displays information about all buffer pools available for the instance. The "sets" pertain to the number of LRU latch sets.V$C

22、ONTROLFILE This view lists the names of the control files. V$DATABASE This view contains database information from the control file. V$DATAFILE This view contains datafile information from the control file.V$DATAFILE_HEADER This view displays datafile information from the datafile headers. V$DBFILE This view lists all datafiles making up the database. This view is retained for historical compatibility. Use of V$DATAFILE is recommended instead.V$FILESTAT This view contains information about file read/write statistics. V$INSTANCEThis view displays the state of the c