Oracle常用命令

1、查共有多少个表空间
select * from Dba_Tablespaces;

2、查询所有用户对应有哪些表空间的权限
select a2.username,a1.privilege from dba_sys_privs a1 , user_role_privs a2 where a1.privilege = ‘表空间名’ and a1.grantee =a2.granted_role;

3、查询某个用户下面有哪个表空间
select distinct a.tablespace_name from dba_segments a where a.owner =‘用户名’;

4，查看数据库具体版本；
select * from product_component_version;

5.显示当前连接用户
show user

6.查看表空间存储情况
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name=‘表空间名’ and segment_type=‘TABLE’ group by tablespace_name,segment_name;

7.创建表空间
create tablespace schooltbs datafile ‘D:\oracle\datasource\schooltbs.dbf’ size 10M autoextend on;

8.删除表空间
drop tablespace schooltbs[including contents and datafiles];

9.查询表空间基本信息
select *||tablespace_name from DBA_TABLESPACES;

10.创建用户
create user lihua
identified by lihua
default tablespace schooltbs
temporary tablespace temp;

11.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;

12.将表Table_A的查询权限赋给用户USER_A
GRANT SELECT ON Table_A to User_A;

13.增删改查都开启权限的语句：
grant select,update,delete,insert on Table_A to USER_A;