表空间操作
1、命令创建表空间
create tablespace “TEST” datafile ‘/dm8/data/DAMENG/TBS01.DBF’ size 32
autoextend on next 1 CACHE = NORMAL;
2、表空间状态变更,脱机和在线
alter tablespace TEST offline; alter tablespace TEST online;
3、表空间修改大小
alter tablespace “TEST” resize datafile ‘TBS01.DBF’ to 64;
4、查看表空间数据文件有多少个
select FILE_NAME,STATUS,TABLESPACE_NAME from dba_data_files;
5、增加表空间数据文件
alter tablespace “TEST” add datafile ‘/dm8/data/DAMENG/TBS02.DBF’ size
64;
6、更换数据文件存储的位置
①:alter tablespace “TEST” offline;
②:select tablespace_name,status
from dba_tablespaces;
③:alter tablespace “TEST” rename datafile
‘/dm8/data/DAMENG/TBS02.DBF’ to ‘/dm8/tbs02.dbf’; ④:alter tablespace
“TEST” rename datafile ‘/dm8/data/DAMENG/TBS02.DBF’ to
‘/dm8/tbs01.dbf’;
⑤:alter tablespace “TEST” online;
7、临时表空间
select para_name,para_value from v$dm_ini where para_name like
‘TEMP%’;
8、Roll表空间
alter tablespace roll resize datafile ‘/dm8/data/DAMENG/ROLL.DBF’ to
256;
9、删除表空间
drop tablespace “TEST”;
用户管理操作
1、删除用户
级联删除(schema都会删除)
drop user test cascade;
只删除账号
drop user test;
2、查看test用户有哪些权限
select grantee,granted_role from SYS.DBA_ROLE_PRIVS where
grantee=‘TEST’;
3、查看public角色有哪些权限
SELECT GRANTEE,PRIVILEGE FROM SYS.DBA_SYS_PRIVS WHERE
GRANTEE=‘PUBLIC’;