Oracle系统表查询

数据字典dict总是属于Oracle用户sys的。

1、用户：

　select username from dba_users;

改口令

　alter user spgroup identified by spgtest;

2、表空间：

　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、数据库对象：

　select * from dba_objects;

　CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、

　PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。

4、表：

　select * from dba_tables;

　analyze my_table compute statistics;->dba_tables后6列

　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、索引：　

　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、序列：

　select * from dba_sequences;

7、视图：

　select * from dba_views;

　select * from all_views;

text 可用于查询视图生成的脚本

8、聚簇：

　select * from dba_clusters;

9、快照：

　select * from dba_snapshots;

快照、分区应存在相应的表空间。

10、同义词：

　select * from dba_synonyms

where table_owner='SPGROUP';

//if owner is PUBLIC,then the synonyms is a public synonym.

　if owner is one of users,then the synonyms is a private synonym.

11、数据库链：

　select * from dba_db_links;

在spbase下建数据库链

　create database link dbl_spnew

　connect to spnew identified by spnew using 'jhhx';

　insert into acc_nbr@dbl_spnew

　select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';