ORACLE 表空间管理

 

sqlplus / as sysdba

--创建普通数据表空间(前提是目录已存在)
create tablespace tbs1 
datafile 'C:\ORADATA\DATAFILE\tbs1.dbf' size 100M 
extent management local 
segment space management auto;

col file_name format a50
set linesize 366

--查询表空间（名称大写）
select file_name,tablespace_name,autoextensible,bytes 
from dba_data_files 
where tablespace_name = 'TBS1' order by substr(file_name,-12);


--创建临时表空间
create temporary tablespace temp_tbs 
tempfile 'C:\ORADATA\DATAFILE\temp_tbs.dbf' size 100m;

select file_name,tablespace_name,autoextensible,bytes 
from dba_temp_files 
where tablespace_name = 'TEMP_TBS';


--创建回滚段表空间
create undo tablespace undo_tbs 
datafile 'C:\ORADATA\DATAFILE\undo_tbs.dbf' size 100m;

select file_name,tablespace_name,autoextensible,bytes/1024/1024 
from dba_data_files 
where tablespace_name = 'UNDO_TBS' order by substr(file_name,-12);


--查看系统表空间
select file_name,tablespace_name,autoextensible,bytes/1024/1024 
from dba_data_files 
where tablespace_name like '%SYS%' order by substr(file_name,-12);

--系统表空间与用户表空间都属于永久保留数据
select tablespace_name,contents from dba_tablespaces where tablespace_name in ('TBS1','TEMP_TBS','UNDO_TBS','SYSTEM','SYSAUX');

---为用户指定默认表空间
sqlplus / as sysdba
drop user ym301 cascade;
create user ym301 identified by ym301 default tablespace tbs1 temporary tablespace temp_tbs;

--授权（最大权限，用于测试）
grant dba to ym301;

--用户ym301登录
connect ym301/ym301;

--在表空间中建表（Segment）
create table t(id int) tablespace tbs1;
create table t2(id int);--不指定表空间，就在默认的表空间中创建


--查询数据字典，获取区extent信息（默认8个块，区大小64K）
select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T';


--插入数据
insert into t select rownum from dual connect by level <= 1000000;
commit;
select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T';

--查询数据字典，获取区segment信息
select segment_name, segment_type, tablespace_name,blocks,extents, bytes/1024/1024 from user_segments where segment_name = 'T';

--观察索引段
create index idx_id on t(id);
select segment_name, segment_type, tablespace_name,blocks,extents, bytes/1024/1024 from user_segments where segment_name = 'IDX_ID';
select count(*) from user_extents where segment_name = 'IDX_ID';


--调整块大小（默认8K）
--不能更改系统表空间、已建好的表空间
--查看参数
show parameter cache_size;

--设置块大小
alter system set db_16k_cache_size=100M;
show parameter 16k;
--建表空间，启用块
create tablespace tbs_16k blocksize 16k datafile 'C:\ORADATA\DATAFILE\tbs_16k.dbf' size 100M autoextend on extent management local segment space management auto;

select tablespace_name, block_size from dba_tablespaces where tablespace_name in ('TBS1','TBS_16K');

---设置区大小
create tablespace TBS2 datafile 'C:\ORADATA\DATAFILE\TBS2.dbf' size 100M extent management local uniform size 10M segment space management auto;

create table t2(id int) tablespace TBS2;

select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T2';

insert into t2 select rownum from dual connect by level <= 1000000;

--表空间原始空间大小
select sum(bytes)/1024/1024 from dba_data_files where tablespace_name = 'TBS2';


--表空间使用空间情况
select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TBS2';

--ORA-30009: Not enough memory for CONNECT BY operation

--增加数据文件，扩大表空间
alter tablespace TBS2 ADD DATAFILE 'C:\ORADATA\DATAFILE\TBS2_1.dbf' SIZE 50M;

select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files where tablespace_name = 'TBS2';

--修改表空间为自动扩展方式(autoextend on)
alter database datafile 'C:\ORADATA\DATAFILE\TBS2_1.dbf' autoextend on;

--删除表空间TBS2
drop tablespace TBS2 including contents and datafiles;

--建表空间时设置自动扩展
create tablespace TBS3 datafile 'C:\ORADATA\DATAFILE\TBS3.dbf' size 100M autoextend on extent management local segment space management auto;
--extent management local segment space management auto,Oracle 10g及以上版本，可省略。
--每次以64K增加，最大不超过5G
create tablespace TBS3 datafile 'C:\ORADATA\DATAFILE\TBS3.dbf' size 100M autoextend on next 64k maxsize 5G;

--查看当前回滚段表空间（默认UNDOTBS1）
show parameter undo;
--数据库有几个回滚段表空间
select tablespace_name, status from dba_tablespaces where contents = 'UNDO';
--分别查看各回滚段表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name in ('UNDOTBS1','UNDO_TBS') GROUP BY tablespace_name;

--切换回滚表空间（注意：回滚表空间可以新建多个，但是数据库当前使用的只能有一个）
alter system set undo_tablespace=UNDO_TBS scope=both;
show parameter undo;
--正在使用时，无法删除 undo tablespace 'UNDO_TBS' is currently in use 
drop tablespace UNDO_TBS;

--切换临时表空间（注意：临时表空间可以新建多个，可以同时使用）
--数据库有几个临时表空间
select tablespace_name, sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
--该用户使用的表空间
select default_tablespace, temporary_tablespace from dba_users where username = 'YM301';
--其他用户使用的表空间
select default_tablespace, temporary_tablespace from dba_users where username = 'SYSTEM';

--切换临时表空间
sqlplus / as sysdba;
alter user system temporary tablespace TEMP_TBS;
select default_tablespace, temporary_tablespace from dba_users where username = 'SYSTEM';
--不同用户使用临时表空间的情况
select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;

--切换所有用户到指定临时表空间
alter database default temporary tablespace TEMP_TBS;

--临时表空间组
create temporary tablespace temp1_1 tempfile 'C:\ORADATA\DATAFILE\temp1_1.dbf' size 100M tablespace group tmp_grp1;
create temporary tablespace temp1_2 tempfile 'C:\ORADATA\DATAFILE\temp1_2.dbf' size 100M tablespace group tmp_grp1;
create temporary tablespace temp1_3 tempfile 'C:\ORADATA\DATAFILE\temp1_3.dbf' size 100M tablespace group tmp_grp1;
select * from dba_tablespace_groups;

--临时表空间转移到组中
alter tablespace TEMP_TBS tablespace group tmp_grp1;
select * from dba_tablespace_groups;

--设置用户指定临时表空间组
alter user ym301 temporary tablespace tmp_grp1;

--查看用户指定的临时表空间
select temporary_tablespace from dba_users where username='YM301';

select username,session_num,tablespace from v$sort_usage;


--查看产生多少日志
select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';