Oracle系统表.doc

ORACLE系统表处理?1.取得指定用户的所有表名：? Java代码? SELECT?OWNER??AS?对象所有者,OBJECT_NAME?AS?表名,OBJECT_ID?AS?对象编号?from?dba_objects?where?owner?=?RAXNYB?AND?OBJECT_TYPE?=?TABLE?ORDER?BY?OWNER,OBJECT_TYPE;?? 或?? OWNER??AS?对象所有者,TABLE_NAME?AS?表名?from?DBA_TABLES?where?owner?=?RAXNYB??ORDER?BY?OWNER,TABLE_NAME;?? 2.取得指定用户的所有视图名称：? Java代码? SELECT?OWNER??AS?对象所有者,VIEW_NAME?AS?视图名称?from?DBA_VIEWS??where?owner?=?RAXNYB??ORDER?BY?OWNER,VIEW_NAME;?? oracle系统表查询?1.用户：? Java代码? select?username?from?dba_users;?? 改口令? Java代码? alter?user?spgroup?identified?by?spgtest;??? 2.表空间:? Java代码? select?*?from?dba_data_files;??? select?*?from?dba_tablespaces;//表空间??? select?tablespace_name,sum(bytes),?sum(blocks)?from?dba_free_space?group?by?tablespace_name;//空闲表空间??? select?*?from?dba_data_files?where?tablespace_name=RBS;//表空间对应的数据文件??? select?*?from?dba_segments?where?tablespace_name=INDEXS;??? 3.数据库对象? Java代码? select?*?from?dba_objects;??? ?? CLUSTER、DATABASE?LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE?BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。?? 4.表? Java代码? select?*?from?dba_tables;??? ?? select?extent_id,bytes?from?dba_extents?where?segment_name=CUSTOMERS?and?segment_type=TABLE?order?by?extent_id;//表使用的extent的信息。segment_type=ROLLBACK查看回滚段的空间分配信息?? ?? select?distinct?table_name?from?user_tab_columns?where?column_name=SO_TYPE_ID;??? 5.索引? Java代码? select?*?from?dba_indexes;//索引，包括主键索引?? select?*?from?dba_ind_columns;//索引列?? select?i.index_name,i.uniqueness,c.column_name?? from?user_indexes?i,user_ind_columns?c?? where?i.index_name=c.index_name?? and?i.table_name?=ACC_NBR;//联接使用?? 6.序列? Java代码? select?*?from?dba_sequences;?? 7.视图? Java代码? select?*?from?dba_views?? select?*?from?all_views?? text字段?可用于查询视图生成的脚本?? 8.聚簇? Java代码? select?*?from?dba_clusters?? 9.快照? Java代码? select?*?from?dba_snapshots?? 快照、分区应存在相应的表空间?10.同义词? Java代码? select?*?from?dba_synonyms?? //if owner is PUBLIC,then the synonyms is a public synonym.?//if owner is one of users,then the synonym