1、用户
查看当前用户的缺省表空间
|
1
|
SQL>
select
username,default_tablespace
from
user_users;
|
查看当前用户的角色
|
1
|
SQL>
select
*
from
user_role_privs;
|
查看当前用户的系统权限和表级权限
|
1
2
|
SQL>
select
*
from
user_sys_privs;
SQL>
select
*
from
user_tab_privs;
|
显示当前会话所具有的权限
|
1
|
SQL>
select
*
from
session_privs;
|
显示指定用户所具有的系统权限
|
1
|
SQL>
select
*
from
dba_sys_privs
where
grantee=
'EZXX'
;
|
2、表
查看用户下所有的表
|
1
|
SQL>
select
*
from
user_tables;
|
查看名称包含log字符的表
|
1
|
SQL>
select
object_name,object_id
from
user_objects
where
instr(object_name,
'LOG'
)>0;
|
查看某表的创建时间
|
1
|
SQL>
select
object_name,created
from
user_objects
where
object_name=
upper
(
'&table_name'
);
|
查看某表的大小
|
1
|
SQL>
select
sum
(bytes)/(1024*1024)
as
"size(M)"
from
user_segments
where
segment_name=
upper
(
'&table_name'
);
|
查看放在ORACLE的内存区里的表
|
1
|
SQL>
select
table_name,cache
from
user_tables
where
instr(cache,
'Y'
)>0;
|
3、索引
查看索引个数和类别
|
1
|
SQL>
select
index_name,index_type,table_name
from
user_indexes
order
by
table_name;
|
查看索引被索引的字段
|
1
|
SQL>
select
*
from
user_ind_columns
where
index_name=
upper
(
'&index_name'
);
|
查看索引的大小
|
1
|
SQL>
select
sum
(bytes)/(1024*1024)
as
"size(M)"
from
user_segments
where
segment_name=
upper
(
'&index_name'
);
|
4、序列号
查看序列号，last_number是当前值
|
1
|
SQL>
select
*
from
user_sequences;
|
5、视图
查看视图的名称
|
1
|
SQL>
select
view_name
from
user_views;
|
查看创建视图的select语句
|
1
2
3
|
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、同义词
查看同义词的名称
|
1
|
SQL>
select
*
from
user_synonyms;
|
7、约束条件
查看某表的约束条件
|
1
2
3
4
5
|
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.column_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、存储函数和过程
查看函数和过程的状态
|
1
2
|
SQL>
select
object_name,status
from
user_objects
where
object_type=
'FUNCTION'
;
SQL>
select
object_name,status
from
user_objects
where
object_type=
'PROCEDURE'
;
|
查看函数和过程的源代码
|
1
|
SQL>
select
text
from
all_source
where
owner=
user
and
name
=
upper
(
'&plsql_name'
);
|
9、查看建表语句
|
1
|
SELECT
DBMS_METADATA.GET_DDL(
'TABLE'
,
'TABLE_NAME'
)
FROM
DUAL; #(表名TABLE_NAME一定要来大写)
|
本文转自lq201151CTO博客，原文链接：http://blog.51cto.com/liuqun/2051386 ，如需转载请自行联系原作者
![[Oracle]索引](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1634956/20241107/b30f131fc2184af8a7b29e924db9a549.jpeg?x-oss-process=image/resize,h_160,m_lfit)