1.数据字典：记载了数据库的系统信息，所有者为SYS用户，并且其数据被存放在SYSTEM表空间中。
数据字典是只读表和视图的集合。数据字典包括数据字典基表和数据字典视图；基表存储着数据库的基本信息，
普通用户不能直接访问；数据字典视图则是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典
视图取得系统信息，主要包括：USER_XXX、ALL_XXX、DBA_XXX三种类型。
1.1.USER_XXX
USER_XXX用于显示当前用户所拥有的所有对象，它只返回用户方案的所有对象。如：user_tables
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
MVIEW$_ADV_PARTITION
MVIEW$_ADV_INDEX
HELP
SQLPLUS_PRODUCT_PROFILE
REPCAT$_SITES_NEW
REPCAT$_EXTENSION
REPCAT$_INSTANTIATION_DDL
REPCAT$_EXCEPTIONS
REPCAT$_TEMPLATE_TARGETS
1.2.ALL_XXX
ALL_XXX用于显示当前用户可以访问的所有对象。它不仅返回当前用户方案的所有对象，
而且会返回当前用户可以访问的其他方案对象。
SQL> select owner,table_name from all_tables;
OWNER TABLE_NAME
------------------------------ ------------------------------
HR REGIONS
SYS ICOL$
SYS CON$
SYS UNDO$
SYS PROXY_ROLE_DATA$
SYS FILE$
SYS UET$
SYS IND$
1.3.DBA_XXX
DBA_XXX用于显示整个数据库范围内的详细系统信息，它会显示所有方案所拥有的数据库对象。
用户必须具有DBA角色或SELECT_CATALOG_ROLE角色。
SQL> select table_name from dba_tables where rownum <=5;
TABLE_NAME
------------------------------
ATEMPTAB$
MAP_OBJECT
CLUSTER_DATABASES
CLUSTER_NODES
CLUSTER_INSTANCES
2.常用数据字典
2.1.DICT
当前用户可访问的所有数据字典视图和动态性能视图，并给出了这些数据字典视图的作用。
SQL> select * from dict
2 where comments is not null and rownum <=5;
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
ALL_XML_SCHEMAS Description of all XML Schemas that user has privilege to reference
ALL_XML_SCHEMAS2 Dummy version of ALL_XML_SCHEMAS that does not have an XMLTYPE column
DBA_ROLES All Roles which exist in the database
DBA_PROFILES Display all profiles and their limits
USER_RESOURCE_LIMITS Display resource limit of the user
2.2.DICT_COLUMNS
用于显示数据字典视图每个列的作用。
SQL> select * from dict_columns
2 where comments is not null
3 and rownum <=5;
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ALL_XML_SCHEMAS OWNER Owner of the XML Schema
ALL_XML_SCHEMAS SCHEMA_URL Schema URL of the XML Schema
ALL_XML_SCHEMAS LOCAL Is this XML Schema local or global
ALL_XML_SCHEMAS SCHEMA The XML Schema document
ALL_XML_SCHEMAS INT_OBJNAME The internal database object name for the schema
2.3.DUAL
用于取得函数的返回值。
SQL> select user from dual;
USER
------------------------------
SYSTEM
2.4.GLOBAL_NAME
用于显示当前数据库的全名。
SQL> select * from global_name;
GLOBAL_NAME
-----------------------------
ORCL
2.5.IND
用于显示当前用户所拥有的所有索引，以及索引统计信息。
SQL> select *
2 from ind
3 where table_name = 'HELP';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- -------
HELP_TOPIC_SEQ NORMAL SYSTEM HELP TABLE UNIQUE DISABLED SYSTEM 2 255 16384 1 2147483645 1 1 10 YES 1 3 959 1 1 10 VALID 959 959 2004/3/10 0:2 1 1 NO N N N DEFAULT NO YES NO NO NO
2.6.OBJ
用于显示当前用户所拥有的所有对象。
SQL> select *
2 from obj
3 where rownum <=5;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
SYSCATALOG 2384 SYNONYM 2004/3/9 23 2004/3/9 23:5 2004-03-09:23:59:59 VALID N N N
CATALOG 2386 SYNONYM 2004/3/9 23 2004/3/9 23:5 2004-03-09:23:59:59 VALID N N N
TAB 2388 SYNONYM 2004/3/9 23 2004/3/9 23:5 2004-03-09:23:59:59 VALID N N N
COL 2391 SYNONYM 2004/3/9 23 2004/3/9 23:5 2004-03-09:23:59:59 VALID N N N
TABQUOTAS 2395 SYNONYM 2004/3/9 23 2004/3/9 23:5 2004-03-09:23:59:59 VALID N N N
2.7.SEQ
显示当前用户所拥有的所有序列。
SQL> select *
2 from seq
3 where rownum <=5;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
MVIEW$_ADVSEQ_GENERIC 1 4294967295 1 N N 50 1
MVIEW$_ADVSEQ_ID 1 4294967295 1 N N 20 1
LOGMNR_EVOLVE_SEQ$ 1 1E27 1 N Y 20 1
LOGMNR_SEQ$ 1 1E27 1 N Y 20 1
LOGMNR_UIDS$ 1 1E27 1 N Y 20 100
2.8.SYN
当前用户所拥有的同义词。
SQL> select * from syn;
SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYSCATALOG SYS SYSCATALOG
CATALOG SYS CATALOG
TAB SYS TAB
COL SYS COL
TABQUOTAS SYS TABQUOTAS
SYSFILES SYS SYSFILES
PUBLICSYN SYS PUBLICSYN
PRODUCT_USER_PROFILE SYSTEM SQLPLUS_PRODUCT_PROFILE
8 rows selected
2.9.TAB
当前用户所用于的表、视图和序列。
SQL> select *
2 from tab
3 where rownum <=5;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYSCATALOG SYNONYM
CATALOG SYNONYM
TAB SYNONYM
COL SYNONYM
TABQUOTAS SYNONYM
扫一扫
1251
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
