sqlplus常用命令整合

下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。

1.         用户

查看当前用户的缺省表空间

SQL>SELECT username,default_tablespace FROM user_users;

 

查看当前用户的角色

SQL>SELECT * FROM user_role_privs;

 

查看当前用户的系统权限和表级权限

SQL>SELECT * FROM user_sys_privs;

SQL>SELECT * FROM user_tab_privs;

 

2.         表

查看用户下所有的表

SQL>SELECT * FROM user_tables;

 

查看名称包含lob字符的表

SQL>SELECT object_name,object_id FROM user_objects

WHERE INSTR(object_name,’LOG’) > 0;

 

查看某表的创建时间

SQL>SELECT object_name,created FROM user_objects

WHERE object_name = UPPER(‘&table_name’);

 

查看某表的大小

SQL>SELECT SUM(bytes)/(1024*1024) as ‘size(M)” FROM user_segments

WHERE segment_name = UPPER(‘&table_name’);

 

查看放在ORACLE的内存区里的表

SQL>SELECT table_name,cache FROM user_tables

WHERE INSTR(cache,’Y’) > 0;

 

3.         索引

查看索引个数和类别

SQL>SELECT index_name,index_type,table_name FROM user_indexes

ORDER BY table_name;

 

查看索引被索引的字段

SQL>SELECT * FROM user_ind_columns

WHERE index_name = UPPER(‘&index_name’);

 

查看索引的大小

SQL>SELECT SUM(bytes)/(1024*1024) as “size(M)” FROM user_segments

WHERE segment_name = UPPER(‘&index_name’);

 

4.         序列号

查看序列号，last_nmuber是当前值

SQL>SELECT * FROM user_sequences;

 

5.         视图

查看视图的名称

SQL>SELECT view_name FROM user_views;

 

查看创建视图的select语句

SQL>SET view_name,text_length FROM user_views;

SQL>SET long 2000;说明：可以根据视图的text_length值设定set long的大小

SQL>SELECT text FROM user_views WHERE view_name = UPPER(‘&view_name’);

 

6.         同义词

查看同义词的名称

SQL>SELECT * FROM user_synonyms;

 

7.         约束条件

查看某表的约束条件

SQL>SELECT constraint_name,constraint_type,search_condition,r_constraint_name

FROM user_constraints WHERE table_name = UPPER(‘&table_name’);

 

SQL>SELECT c.constraint_name,c.constraint_type,cc.collumn_name

FROM user_constraints c,user_cons_columns cc

WHERE c.owner = UPPER(‘&table_owner’) AND c.table_name = UPPER(‘&table_name’)

AND c.owner = cc.owner AND c.constraint_name = cc.constraint_name

ORDER BY cc.position;

 

8.         函数和存储过程

查看函数和存储过程的状态

SQL>SELECT object_name,status FROM user_objects WHERE object_type = ‘FUNCTION’;

SQL>SELECT object_name,status FROM user_objects WHERE object_type = ‘PROCEDURE’;

 

查看函数和存储过程的源代码

SQL>SELECT text FROM all_source WHERE owner = user AND name = upper(‘&plsal_name’);

 

 

查看数据库的SQL

1.       查看表空间的名称及大小

SQL>SELECT t.tablespace_name,ROUND(SUM(bytes/(1024*1024)),0) ts_size

FROM dba_tablespaces t,dba_data_files d

WHERE t.tablespace_name = d.tablespace_name

GROUP BY t.tablespace_name;

 

2.       查看表控件物理文件的名称及大小

SQL>SELECT tablespace_name,file_id,file_name,ROUND(bytes/(1024*1024),0) total_space

FROM dba_data_files

ORDER BY tablespace_name;

 

3.       查看会滚名称及大小

SQL>SELECT segment_name,tablespace_name,r.status,(initial_extent/1024) InitialExtent,

(next_extent/1024) NextExtent,max_extents,v.curext CurExtent

FROM dba_rollback_segs r,V$rollstat v

WHERE r.segment_id = v.usn(+)

ORDER BY segment_name;

 

4.       查看控制文件

SQL>SELECT name FROM v$controlfile;

 

5.       查看日志文件

SQL>SELECT member FROM v$logfile;

 

6.       查看表控件的使用情况

SQL>SELECT SUM(bytes)/(1024*1024) AS free_space,tablespace_name

FROM dba_free_space

GROUP BY tablespace_name;

 

SQL>SELECT a.tablespace_name,a.bytes Total,b.bytes Used,c.bytes Free,

(b.bytes*100)/a.bytes “% Used”,(c.bytes*100)/a.bytes “% Free”

FROM sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c

WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name = c.tablespace_name;

 

7.       查看数据库对象

SQL>SELECT owner,object_type,status,count(*) count# FROM all_objects

GROUP BY owner,object_type,status;

 

8.       查看数据库的版本

SQL>SELECT version FROM product_component_version

WHERE SUBSTR(product,1,6) = ‘Oracle’;

 

9.       查看数据库的创建日期和归档方式

SQL>SELECT created,log_mode FROM v$database;

 

ORACLE用户连接的管理

用系统管理员，查看当前数据库有几个用户连接：

SQL>SELECT username,sid,serial# FROM v$session;