oracle 表空间操作语句备忘

表空间建立：

windows：

create temporary tablespace usertemp tempfile 'D:\oracle\data\sldb\usertemp.dbf' size 50m  autoextend on next 50m maxsize 20480m extent management local;
create tablespace tc logging datafile 'D:\oracle\data\sldb\tc.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tcidx logging datafile 'D:\oracle\data\sldb\tcidx.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tp logging datafile 'D:\oracle\data\sldb\tp.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tpidx logging datafile 'D:\oracle\data\sldb\tpidx.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create user dbtc identified by dbtc default tablespace tc temporary tablespace usertemp;
create user dbtp identified by dbtp default tablespace tp temporary tablespace usertemp;
grant connect,resource to dbtc;
grant connect,resource to dbtp;

linux:

create temporary tablespace usertemp tempfile '/data/sldb/usertemp.dbf' size 50m  autoextend on next 50m maxsize 20480m extent management local;
create tablespace tc logging datafile '/data/sldb/tc.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tcidx logging datafile '/data/sldb/tcidx.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tp logging datafile '/data/sldb/tp.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create tablespace tpidx logging datafile '/data/sldb/tpidx.dbf' size 50m  autoextend on  next 50m maxsize unlimited extent management local;
create user dbtc identified by dbtc default tablespace tc temporary tablespace usertemp;
create user dbtp identified by dbtp default tablespace tp temporary tablespace usertemp;
grant connect,resource to dbtc;
grant connect,resource to dbtp;

查看表空间使用情况：
select ddf.tablespace_name,
       dfs.freespace "剩余总量(M)",
       ddf.totalspace "总量(M)",
       dfs.freeblocks "剩余块数",
       ddf.totalblocks as 总块数,
       trim(to_char((dfs.freespace / ddf.totalspace) * 100, '999.00')) || '%' "剩餘空閒比例"
  from (select t.tablespace_name,
               sum(t.bytes) / 1024 / 1024 totalspace,
               sum(t.blocks) totalblocks
          from dba_data_files t
         group by T.tablespace_name) ddf,
       (select s.tablespace_name,
               sum(s.bytes) / 1024 / 1024 freespace,
               sum(s.blocks) freeblocks
          from dba_free_space s
         group by s.tablespace_name) dfs
 where ddf.tablespace_name = dfs.tablespace_name
 order by dfs.freespace / ddf.totalspace
--  and ddf.tablespace_name like 'TS%';

查看表空间路径：
select * from dba_data_files;
select file_name, autoextensible from dba_data_files;

手动扩大表空间：
alter database datafile '/data/oracle/oradata/sldb/sysaux01.dbf' resize 2000m