本文共 1415 字，大约阅读时间需要 4 分钟。

1.创建表空间

create tablespace yyydatafile '/u01/oracle/oradata/orcl/yyy01.dbf'size 50mautoextend onnext 50m maxsize unlimitedextent management local

2.创建用户

create user yyy identified by yyydefault tablespace yyytemporary tablespace tempprofile DEFAULT

3.授权权限

-- Grant/Revoke object privileges grant select on SYS.V_$SESSION to yyy;grant select on SYS.V_$SESSTAT to yyy;grant select on SYS.V_$STATNAME to yyy;-- Grant/Revoke role privileges grant connect to yyy;grant resource to yyy;-- Grant/Revoke system privileges grant create view to yyy;grant debug connect session to yyy;grant unlimited tablespace to yyy;

4.查询表空间的大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_sizeFROM dba_tablespaces t, dba_data_files dWHERE t.tablespace_name = d.tablespace_nameGROUP BY t.tablespace_name;

5.查看表空间对应的数据文件id和数据文件名称

SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_spaceFROM dba_data_filesORDER BY tablespace_name;

6.查看表空间的使用情况

SELECT a.tablespace_name,a.bytes/1024/1024 "total/m",b.bytes/1024/1024 "used/m",c.bytes/1024/1024 "free/m",Round((c.bytes * 100)/a.bytes,2) " FREE %"FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free cWHERE a.tablespace_name = b.tablespace_nameAND a.tablespace_name = c.tablespace_name;

7.更改表空间大小(dbf)

alter database datafile '数据文件路径' resize 大小;

8.删除表空间及关联关系

DROP TABLESPACE tablesapce_name including contents and datafiles cascade constraint;

转载地址：http://vudws.baihongyu.com/