创建ORACLE表空间及管理

conn scott/123456 as sysdba   SCOTT用户作为管理员登录

create user zdz identified by zdz123;  创建用户zdz

alter user zdz identified by zhang123;  更改用户zdz登录密码

select username,default_tablespace from dba_users where username ='ZDZ';  查看所有用户的默认表空间

create tablespace zdz_tp datafile '/u01/app/oracle/oradata/jiagulun/zdz_tp.dbf' size 100M autoextend on;  创建表空间

alter user zdz default tablespace zdz_tp;  将zdz_tp表空间赋予给zdz用户

grant create session,create table,create view,create sequence,unlimited tablespace to zdz;  赋予zdz相关权限

conn zdz/zhang123;

select * from session_privs; 查看用户相关权限

PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE VIEW

CREATE SEQUENCE

//

create tablespace zdz_ts datafile '/u01/app/oracle/oradata/jiagulun/zdz_tp.dbf' size 100M autoextend on

查询表空间是否可自动扩展
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

drop tablespace zdz_ts including contents and datafiles;
不能删系统表空间/使用中的undo表空间/默认临时和永久表空间

添加数据文件到表空间
alter tablespace zdz_ts add datafile '/u01/app/oracle/oradata/JIAGULUN/datafile/zdz_ts2' size 10m;
select status,name from v$datafile;

更改表空间大小
alter database datafile '/u01/app/oracle/oradata/JIAGULUN/datafile/zdz_ts.dbf' resize 150m;

查询表空间使用情况
select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;


查询表空间使用情况
select
a.a1 tsname,--表空间名
c.c2 tstype,--类型
c.c3 extentmanag,--区管理
b.b2/1024/1024 tssizeM,--表空间大小
(b.b2-a.a2)/1024/1024 tsusedM,--已使用情况
substr((b.b2-a.a2)/b.b2*100,1,5) available--利用率
from
(select  tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3  from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;

tsname      tstype      extentmanag    tssizeM    tsusedM    available
UNDOTBS1    UNDO    LOCAL           30          8         26.66
SYSAUX        PERMANENT    LOCAL           250      244.25     97.7
USERS        PERMANENT    LOCAL           5      3.1875     63.75
SYSTEM        PERMANENT    LOCAL           480      476.5         99.27
EXAMPLE        PERMANENT    LOCAL           100      68.25         68.25
INDEX_TBS   PERMANENT    LOCAL           100      0.25         .25
ZDZ_TP        PERMANENT    LOCAL           100      0.125         .125

——————————————————————————————————————————————————
查询表空间物理文件名

select
b.file_name phycialname,
b.tablespace_name tsname,
b.bytes/1024/1024 sizeM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) available
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

phycialname                                                   tsname         sizeM     usedM    available
/u01/app/oracle/oradata/jiagulun/example01.dbf                EXAMPLE         100    68.25     68.25
/u01/app/oracle/oradata/jiagulun/index_tbs.dbf                  INDEX_TBS         100    0.25     .25
/u01/app/oracle/oradata/jiagulun/o1_mf_sysaux_dh9b1b9o_.dbf   SYSAUX         250    244.25     97.7
/u01/app/oracle/oradata/jiagulun/o1_mf_system_dh9b1b9c_.dbf   SYSTEM         480    476.5     99.27
/u01/app/oracle/oradata/jiagulun/o1_mf_undotbs1_dh9b1bcj_.dbf UNDOTBS1         30            8        26.66
/u01/app/oracle/oradata/jiagulun/o1_mf_users_dh9b1bcs_.dbf    USERS         5            3.1875     63.75
/u01/app/oracle/oradata/jiagulun/zdz_tp.dbf                  ZDZ_TP         100        0.125       .125