oracle12-简单使用

1、用户操作

select * from dba_users;
create user c##zhang identified by 123456;
alter user c##zhang identified by 123456;
grant connect,resource to c##zhang;
alter user c##zhang quota unlimited on users;
drop user c##zhang cascade;

2、用户

select * from user_users;
查看当前用户的角色
select * from user_role_privs;
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

3、表

查看用户下所有的表
select * from user_tables;
查看某表的创建时间
select * from user_objects where object_name=upper('test');
查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('test');

4、索引

查看索引个数和类别
select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段
select * from user_ind_columns where index_name=upper('&index_name');
查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');

5、序列号

查看序列号，last_number是当前值
select * from user_sequences;

6、视图

select * from user_views;

7、存储函数和过程

查看函数和过程的状态
select object_name,status from user_objects where object_type='FUNCTION'; select object_name,status from user_objects where object_type='PROCEDURE';
查看函数和过程的源代码
select text from all_source where owner=user and name=upper('&plsql_name');