oracle 数据表空间管理与维护
oracle 数据表空间管理与维护
2、 表空间的日常操作与维护管理
-----------------------------------------------------------------------
--2.1. 表空间创建
先查看目前的文件路径和空间使用情况：
select name from v$datafile;
asm: asmcmd lsdg
df -h
_asm_hbeatiowait
创建数据表空间:
CREATE TABLESPACE 表空间名 DATAFILE '数据文件' EXTENT MANAGEMENT {LOCAL{AUTOALLOCATE|UNIFORM [SIZE INTETER [KlM]]}}]
--ASM:58
CREATE TABLESPACE xxtbs01 DATAFILE '+dgdata01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 30720MLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 16K SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
CREATE TABLESPACE xxtbs02 DATAFILE '+dgdata02' SIZE 100M AUTOEXTEND OFF;
SQL> select name from v$datafile;
另外还有一个参数：
PERMANENT
该选项主要用于指定表空间的类型，permanent表示永久的，不写默认就是永久。
如果是其他类型，则写temporary或者undo
如果创建sysaux表空间，则必须指定extend managent类型和segment space management类型。
select * from dba_tablespaces where tablespace_name in('xxtbs01','xxtbs02');
SQL> select name from v$datafile;
CREATE TABLESPACE xxtbs01 DATAFILE '/oradata/xxdb/xxtbs0101.dbf' SIZE 100M AUTOEXTEND ON NEXT 10MMAXSIZE 30720M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTO ALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
CREATE TABLESPACE xxtbs02 DATAFILE '/oradata/xxdb/xxtbs0201.dbf' SIZE100M AUTOEXTEND OFF;
SQL> select name from v$datafile;
创建多个文件的表空间:
create tablespace xxtbs04 datafile
'/oradata/xxdb/xx0401.dbf' size 20m autoextend off,
'/oradata/xxdb/xx0402.dbf' size 20m autoextend off,
'/oradata/xxdb/xx0403.dbf' size 20M autoextend off;
#创建临时表空间
create temporary tablespace temp1 tempfile '/oradata/xxdb/temp1.dbf' size 10m autoextend off;
#创建undo表空间
create undo tablespace xxundo1 datafile '/oradata/xxdb/xxundo1.dbf' size 10m autoextend off;
#创建大文件表空间
create bigfile tablespace xxbig datafile '/oradata/xxdb/xxbig01.dbf' size 10M;
--2.2 表空间相关视图
表空间与数据文件相关视图：
V$TABLESPACE 表空间的标号和信息
DBA_TABLESPACE 表空间的标号和信息
USER_TABLESPACE 表空间的标号和信息
DBA_SEGMENT 表空间段的信息
USER_SEGMENT 表空间段的信息
DBA_EXTENTS 表空间的数据区的信息
USER_EXTENTS 表空间的数据区的信息
DBA_FREE_SPACE 表空间的空闲信息
USER_FREE_SPACE 表空间的空闲信息
V$DATAFILE 数据文件以及所属表空间的信息
DBA_DATA_FILES 数据文件以及所属表空间的信息
V$TEMPFILE 临时文件以及临时表空间的信息
DBA_TEMP_FILES 临时文件以及临时表空间的信息
V$TEMP_SPACE_HEADER 临时文件的空闲信息
V$SORT_SEGMENT 每个排序段的信息
V$SORT_USER 用户使用的临时排序信息
#查看表空间：
--动态视图。信息存在contraolfile中。信息在mount就能查询。
SQL> select * from v$tablespace;
--静态视图。
select tablespace_name,status from dba_tablespaces;
STATUS：Tablespace status:
ONLINE
OFFLINE
READ ONLY
ALLOCATION_TYPE
Type of extent allocation in effect for the tablespace:
SYSTEM
UNIFORM
USER
tablespace name
INCLUDED_IN_DATABASE_BACKUP指示使用backup database rman命令(是)或(否)在完整数据库备份中是否包含表空间。
FLASHBACK_ON 指示表空间是否参与闪回数据库惭怍(是)或(否)
ENCRYPT_IN_BACKUP YES加密在表空间级别开启
system：一旦设定该值，next_extent 将为空，只有 extents 值。该值是默认值。这个选项的最小是 64K
uniform：将标明所有的 extent 的大小将一致，temp 表空间只能采用这个方式；
以上两个情况的 extent 的大小将不一致；uniform 中的默认值为1M
user：一旦设定该值，就允许我们可以控制 next_extent 了。只有两种情况出现 users：一是该 ts 是数据字典管理的；另外一个是该ts 是从数据字典管理转移到 local 的如果是图片库比较 大，建议uniform size 1M --uniform 设 置 extent 每 次 分 配 的 大 小统一为1M( 如果是db_block_size=8k)
当自动分配时，发现开始第一个区分配 8 个块(64K), 到 17 区开始, 每个区分配 128个块(大小 1M).
- ORACLE 通过强制性的手段使本地管理表空间中的所有 Extent 是同样大小的, 尽管可能自定义了不同的存储参数.
- 在自动分配的本地管理的表空间中, 区间尺寸可能由以下尺寸组成 64K, 1M, 8M,64M 甚至是 256M.
但是不管多大, 都有一个通用尺寸 64k, 所以 64K 就是该表空间的位大小.
让数据库自动使用 AUTOALLOCATE 子句（缺省值 system）管理扩展数据块，也可以指定使用特定大小的统一扩展数据块来管理表空间（UNIFORM）。比如:
CREATE TABLESPACE xxtbs06 DATAFILE '/oradata/xxdb/xxtbs0601.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 30720M LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL
--AUTOALLOCATE
uniform size 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SEGMENT_SPACE_MANAGEMENT
(MANUAL) or bitmaps (AUTO)
Segment 管理方式有两种：（-segment 中的 block 管理)
- MSSM(Manual Segment Space Management)
- ASSM(Auto Systemt Space Management)
- MSSM(Manual Segment Space Management)
通过在 segment 的段头分配自由列表(freelist)来管理 block
通过两个参数 pctfree pctused 来管理 block 如何进出 freelist
pctfree 值表示预留多少%的 block 空间用于更新
pctused 值表示低于这个值是，block 会重新加入到 freelist 上通过 dba_tables,dba_indexes 查看 freelist，pctfree，pctused 等参数的设置
select * from dba_tables;
- ASSM(Auto Systemt Space Management)
#查看每个表空间有那些数据文件
#查看详细数据文件
set pagesize 200
col FILE_NAME format a60;
col TABLESPACE_NAME format a20;
select tablespace_name,file_name from dba_data_files;
--2.3 表空间的维护
1.表空间状态有下面几种状态：online,offline,read only，read write
select tablespace_name,status from dba_tablespaces;
select file#, ts#,name,status, enabled from v$datafile;
2.设置为脱机状态
alter tablespace xxtbs04 offline immediate; --默认
alter database datafile 10 offline
alter database datafile 10 offline drop --需要进行 recover 后才可以 online,非归档模式
脱机状态
正常模式（NORMAL）：
进入脱机时，必须保证该表空间的数据文件处于联机，ORACLE 会执行一个 CHECKPOINT,以便 SGA 区中的脏数据都能写入数据文件中。然后在关闭表空间的所有文件。下一次启动时候就不用进行数据库恢复了。
临时模式（TEMPORARY）：
不须保证该表空间的数据文件处于联机，如果某个数据文件不可用，则回忽略错误，进入 TEMPORARY 模式，ORACLE 会执行一个 CHECKPOINT，下一次启动时候可能需要进行数据库恢复
立即模式（IMMEDIATE）：
ORACLE 会执行一个 CHECKPOINT，直接将该表空间的所有数据文件都设置为脱机状态，恢复为联机时必须进行数据库恢复
recover datafile 4;
recover datafile 10;
recover datafile 11;
recover datafile 12;
用于恢复模式（FOR RECOVER）：
如果要对表空间进行基于时间的恢复，可以使用这模式，
3.设置为联机状态
alter tablespace xxtbs04 online;
alter tablespace xxtbs03 online;
alter tablespace xxtbs02 online;
alter database datafile 10 online
4.设置为只读状态
只读read-only 任何人无法写入数据，无法修改数据。
sql>alter tablespace xxtbs02 read only;
sql>alter tablespace xxtbs02 read write;
6.修改表空间名称
在oracle10g之前，表空间的名称是不能被修改的。
在oracle11g中，通过alter tablespace语句中使用rename子句，数据库管理员可以修改表空间的名称。
SQL> alter tablespace xxtbs02 rename to xxtbs022;
SQL> alter tablespace xxtbs022 rename to xxtbs02;
不能对system和sysaux表空间进行重命名，也不能对已经处于offine状态的表空间进行重命名。
7.修改表空间自动扩展打开
alter database datafile '/oradata/xxdb/xxtbs0201.dbf' autoextend on;
08.修改表空间自动扩展关闭
alter database datafile '/oradata/xxdb/xxtbs0201.dbf' autoextend off;
09.修改表空间的数据库文件大小
select name,bytes/1024/1024 from v$datafile;
select name,bytes/1024/1024 from v$tempfile;
alter database datafile '/oradata/xxdb/xxtbs0201.dbf' resize 25m;
alter database tempfile '/oradata/xxdb/temp01.dbf' resize 50M;
10.新增表空间的一个数据库文件
alter tablespace xxtbs02 add datafile '/oradata/xxdb/xxtbs0202.dbf' size 25m autoextend off;
alter tablespace TEMP1 add tempfile '/oradata/xxdb/temp012.dbf' size 25M autoextend off;
11.修改用户缺省表空间
SQL> alter user uxx default tablespace xxtbs02;
12.表空间配额查看
表空间不足与用户配额不足是两种概念。
表空间的大小是指的是实际的用户表空间的大小，而配额大小指的是用户指定使用表空间的大小
查看用户和表空间配额
查看所有用户表空间的配额情况
col USERNAME format a30
SELECT * FROM DBA_TS_QUOTAS;
查看当前用户表空间的配额情况
SELECT * FROM USER_TS_QUOTAS;
13.查询表空间空间使用情况sql脚本
ORA-01653: unable to extend table xxtbs by 128 in tablespace xx
脚本1：
set pagesize 200
col TS-name for a15
col f.tablespace_name format a15
col d.tot_grootte_mb format a10
col ts-per format a8
select upper(f.tablespace_name) "TS-name",
d.tot_grootte_mb "TS-bytes(m)",
d.tot_grootte_mb - f.total_bytes "TS-used (m)",
f.total_bytes "TS-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb *100,2),'990.99') "TS-per"
from (select tablespace_name,round(sum(bytes) / (1024 * 1024), 2) total_bytes,round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name= f.tablespace_name
order by 5 desc;
脚本2：
set lin 200
col TABLESPACE_NAME format a20
col FREE_SPACE format a20
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME= F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME= F.TABLESPACE_NAME(+)
ORDER BY 5 DESC;
脚本3：查看临时表空间空间大小
select d.tablespace_name,
space "sum_space(m)",
blocks sum_blocks,
used_space "used_space(m)",
round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
nvl(free_space, 0) "free_space(m)"
from (
select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) space,
sum(blocks) blocks
from dba_temp_files
group by tablespace_name) d,
(select tablespace_name,round(sum(bytes_used) / (1024 * 1024), 2) used_space,round(sum(bytes_free) / (1024 * 1024), 2) free_space from v$temp_space_header
group by tablespace_name) f
where d.tablespace_name= f.tablespace_name(+);
14.删除表空间
drop tablespace xxtbs04;
drop tablespace xxtbs04 including contents
drop tablespace xxtbs04 including contents and datafiles;
drop tablespace xxBIG including contents and datafiles cascade constraints;
15.表空间数据安全选项LOGGING与nologging
logging这个是默认的参数，指定表，视图，索引等的logging属性。
该数据行针对undo和temporary表空间不起作用。
在表空间级别设置的logging属性可以被表等对象自身的属性覆盖。
CREATE TABLESPACE xxtbs05 DATAFILE '/oradata/xxdb/xxtbs0501.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 20G LOGGING
--nologging 不写重做日志文件
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter tablespace xxtbs05 logging;
alter tablespace xxtbs05 nologging;
select tablespace_name,logging from dba_tablespaces;
nologging针对insert,update,delete无效，不管开不开，都要重写日志。
针对一般的dml也是忽略的。
以下内容是不用写重做日志的。
针对以下的dml有效。
direct path insert statements
direct path sql*loader
同时对以下ddl操作有效
create table ... as select ....
alter table ... move...
alter table ... add/merge/split/move/modify partition
create index
alter index ... rebuild
物化视图的操作
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW ... MOVE
CREATE MATERIALIZED VIEW LOG
ALTER MATERIALIZED VIEW LOG ... MOVE
16.表空间性能与非标准快大小的表空间创建
默认情况下创建的表空间使用的数据块大小是8K，数据库默认的数据块大小由db＿block＿size参数决定。
如果我们想要创建的表空间超出这个块大小，首先需要调整数据库的参数db＿nk＿cache＿size。
以创建数据库块大小为32K的表空间为例，展示一下这个调整过程。
create tablespace xxtbs06 datafile '/oradata/xxdb/xxtbs0601.dbf' size 20mautoextend off nologging;
alter tablespace xxtbs06 logging;
查看当前的数据库大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_block_size integer 8192
SQL> show parameter cache_size
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
创建数据库高速缓存区
alter system set db_32k_cache_size=1M scope=both;
show parameter db_32k_cache_size
create tablespace xxtbs08 datafile '/oradata/xxdb/xx0801.dbf' size 10mautoextend off blocksize 32K;
set long 100000
select dbms_metadata.get_ddl('TABLESPACE','xxtbs08') from dual
ERROR at line 1:
ORA-29339: tablespace block size 32768 does not match configured block sizescreate tablespace xxtbs09 datafile '/oradata/xxdb/xx0901.dbf' size 10mautoextend off blocksize 16K;
-----------------------------------------------------------------------
3.表空间与数据文件的迁移
-----------------------------------------------------------------------
--3.1 移动表空间中数据文件的路径
01.在线操作：
1.首先确定数据文件的状态要为offline
select tablespace_name,status,contents from dba_tablespaces;
select file_id,file_name,tablespace_name from dba_data_files where file_name like '%xxdb%' order by file_id;
--通过该语句查询数据文件的路径
2.将该表空间修改offline
alter tablespace xxtbs08 offline;
select tablespace_name,status,contents from dba_tablespaces;
--查看表空间状态确定修改成功
3.移动数据文件
--win
host copy c:\oradata\xxdb\xx01.dbf d:\oradata\xxdb\xx001.dbf
--unix/linux
host cp /oradata/xxdb/xx0801.dbf /oradata/xxdb/xx08001.dbf
.重新命名 该表空间的路径和名称
alter tablespace xxtbs08 rename datafile '/oradata/xxdb/xx0801.dbf' to '/oradata/xxdb/xx08001.dbf';
5.修改表空间的状态为online
alter tablespace xxtbs08 online;
6.检查表空间使用情况
select tablespace_name,status,contents from dba_tablespaces;
select file_id,file_name,tablespace_name from dba_data_files wheretablespace_name='xxtbs08';
方法2：
1.关闭db，启动到mount
shutdown immediate;
startup mount;
2.host copy/cp移动数据文件
#rm /oradata/xxdb/xx0801.dbf
host cp /oradata/xxdb/xx08001.dbf /oradata/xxdb/xx0801.dbf
3.通过alter database宠幸命名信息
alter database rename file '/oradata/xxdb/xx08001.dbf' to '/oradata/xxdb/xx0801.dbf';
4.打开数据库
alter database open;
5.检查状态
select file_id,file_name,tablespace_name,STATUS,ONLINE_STATUS fromdba_data_files where tablespace_name='xxtbs08';
--3.2 移动表或对象到别的表空间
a.检查信息与状态
b.移动对象(表)到另一个表空间
c.检查信息
d.通过重建索引的方法移动索引到另一个表空间
e.再检查确认
将xx迁移到xx01
create user xx identified by xx;
grant dba to xx;
create table xx.xxtable01(id number(12),c_data date);
insert into xx.xxtable01 values (1,sysdate);
insert into xx.xxtable01 values (2,sysdate);
insert into xx.xxtable01 values (3,sysdate);
insert into xx.xxtable01 values (4,sysdate);
insert into xx.xxtable01 values (5,sysdate);
commit;
create index xx.idx_xxtable01_id on xx.xxtable01('id');
--2.检查相关信息
col SEGMENT_NAME format a30
col TABLESPACE_NAME format a30
select segment_name,tablespace_name,extents,blocks from dba_segments where owner='xx';
查询索引或者表存放在那个表空间
col OBJECT_NAME format a30
col OBJECT_TYPE format a30
select object_id,object_name,object_type,status,created from dba_objectswhere owner='xx';
select index_name,table_name,tablespace_name,status from dba_indexes whereowner='xx';
--tbs:
select * from dba_segments where owner='xx' and segment_name in('xxtable01','IDX_xxtable01_ID')
select * from dba_indexes where owner='xx' andindex_name='IDX_xxtable01_ID';
--type:index and table
select * from dba_objects where owner='xx' and object_name in('xxtable01','IDX_xxtable01_ID')
alter table xx.xxtable01 move tablespace xxtbs02;
alter index xx.IDX_xxtable01_ID rebuild tablespace xxtbs02;
alter table xx.xxtable01 move lob(data) store as (tablespace xxtbs02);
select * from dba_segments where owner='xx' and segment_name in('xxtable01','IDX_xxtable01_ID')
select * from dba_indexes where owner='xx' andindex_name='IDX_xxtable01_ID';
select * from dba_objects where owner='xx' and object_name in('xxtable01','IDX_xxtable01_ID')
-----------------------------------------------------------------------
4.Oracle OMF管理数据文件
-----------------------------------------------------------------------
01.关于omf介绍
Oracle managed file 的缩写，简单的理解，就是 oracle 自己管理自己的文件
如果使用 Oracle 管理的文件，则不需要直接管理 Oracle DB 中的操作系统文件。可按照数据库对象而不是文件名指定操作。
数据库将根据需要，在内部使用标准文件系统接口创建或删除下列数据库结构的文件：
表空间
重做日志文件
控制文件
归档日志
块更改跟踪文件
闪回日志
RMAN 备份
02.如何查看当前系统是否使用了omf
show parameter db_create;
参数的值是空的，说明没有使用omf。
03.开始omf：
一共有3个参数
DB_CREATE_FILE_DEST 定义数据文件和临时文件默认文件系统目录的位置
DB_CREATE_ONLINE_LOG_DEST_n 定义重做日志文件和控制文件的创建位置
DB_RECOVERY_FILE_DEST 快速恢复区的默认位置
host mkdir -p /oracle/app/oracle/oradata2
alter system set db_create_file_dest='/oracle/app/oracle/oradata2';
04.使用omf方式
--asm:
oracle asm 默认就是采用了omf文件管理方式
create tablespace xxtbs08 datafile '+dgdata01' size 2m autoextend off;
--fs:
create tablespace xxomf01;
CREATE TABLESPACE xxomf02 datafile size 1M;
select tablespace_name,file_name,bytes/1024/1024 M from dba_data_files where tablespace_name='xxomf01';
select tablespace_name,file_name,bytes/1024/1024 M from dba_data_files where tablespace_name='xxomf02';
使用omf的数据库也可以按照正常的方式添加数据文件
alter tablespace xxomf02 add datafile '/oradata/xxdb/xxomf02.dbf' size 1M;
select tablespace_name,file_name,bytes/1024/1024 M from dba_data_files where tablespace_name='xxomf02';
05.恢复标准的文件管理方式
alter system set db_create_file_dest=' ';
create tablespace xxomf03;
5.system与sysaux表空间满了怎么办？
-----------------------------------------------------------------------
方案1：扩展表空间
向表空间中添加数据文件
方案2：分析问题
4.1 syste表空间满了怎么办？
--检查非sys,system用户的数据是否存在system表空间
--报错
select * from dba_segments where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM') order by owner;
--2.存在业务用户的数据
--3.查询占用这个表空间最大的对象
--all
select bytes / 1024 / 1024, segment_name, segment_type, owner from dba_segments where tablespace_name='SYSTEM' order by bytes / 1024 / 1024 desc-- top 10
--top10
select * from (select bytes / 1024 / 1024, segment_name, segment_type, owner from dba_segments where tablespace_name='SYSTEM' order by bytes / 1024 / 1024 desc) where rownum <11
--5.扩大tablespace
alter tablespace system add datafile '/oradata/xxdb/system02.dbf' size 10mautoextend off;
alter tablespace system add datafile '/oradata/xxdb/system03.dbf' size 200mautoextend off;
select upper(f.tablespace_name) "TS-name",
d.tot_grootte_mb "TS-bytes(m)",
d.tot_grootte_mb - f.total_bytes "TS-used (m)",
f.total_bytes "TS-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb *100,2),'990.99') "TS-per"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name= f.tablespace_name
order by 5 desc;
5.2 sysaux表空间满了怎么办
--1.查看占用空间大的对象
select * from (select bytes / 1024 / 1024, segment_name, segment_type, owner from dba_segments where tablespace_name='SYSAUX' order by bytes / 1024 / 1024 desc) where rownum <11
select * from dba_segments where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM') order by owner;
--2.检查非sys,system用户的数据是否存在sysaux表空间
select * from dba_segments where tablespace_name='SYSAUX' and segment_name like '%MNR%'
可以使用v$sysaux_occupants视图来查看sysaux表空间里的组件信息
select * from v$sysaux_occupants where occupant_name='LOGMNR'
--2.移动
举例：
将logminer从sysaux表空间，迁移到users表空间，在还原回来
exec sys.dbms_logmnr_d.set_tablespace('USERS')
--3.验证
--注意，这里占空的空间变成了0，数据迁移到了users表空间
select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTESfrom v$sysaux_occupants where occupant_name='LOGMNR';
select * from dba_segments where tablespace_name='SYSAUX' and segment_namelike '%MNR%'
select * from dba_segments where segment_name like '%MNR%'
--4.还原
exec sys.dbms_logmnr_d.set_tablespace('SYSAUX')
select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTESfrom v$sysaux_occupants where occupant_name='LOGMNR';
--大小又变回来了。
SQL> drop tablespace SYSAUX including contents and datafiles;
drop tablespace SYSAUX including contents and datafiles
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace B.SYSAUX 不能重命名
SQL> alter tablespace SYSAUX rename to DAVE;
alter tablespace SYSAUX rename to DAVE
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace C.不能将 SYSAUX 改成只读
SQL> alter tablesapce SYSAUX read only;
alter tablesapce SYSAUX read only
*
ERROR at line 1:
ORA-00940: invalid ALTER command
-----------------------------------------------------------------------
6.临时表空间满时的处理方法
-----------------------------------------------------------------------
001.增加临时文件
alter tablespace temp add tempfile '/oradata/xxdb/temp02.dbf' size 10mautoextend off;
002.修改临时文件
alter database tempfile '/oradata/xxdb/temp02.dbf' resize 12m;
Locally Managed：
--当排序操作完成， 占用的空间并没有释放，仅仅是将它标记为空闲，并可重用。
--可以使用 shrink 来释放没有使用的空间
-- shrink 是一个 online 的操作，不影响其他的查询。
alter tablespace temp shrink space keep 200m;
alter tablespace temp shrink tempfile '/oradata/xxdb/temp02.dbf';
-----------------------------------------------------------------------
7.默认临时表空间temp过大的处理办法
-----------------------------------------------------------------------
select * from dba_temp_free_space;
--1.检查信息与状态
select d.tablespace_name,
space "sum_space(m)",
blocks sum_blocks,
used_space "used_space(m)",
round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
nvl(free_space, 0) "free_space(m)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) space,
sum(blocks) blocks
from dba_temp_files
group by tablespace_name) d,
(select tablespace_name,
round(sum(bytes_used) / (1024 * 1024), 2) used_space,round(sum(bytes_free) / (1024 * 1024), 2) free_spacefrom v$temp_space_header
group by tablespace_name) f
where d.tablespace_name= f.tablespace_name(+);
select * from dba_tablespaces where contents='TEMPORARY';
select username, temporary_tablespace from dba_users;
select name from v$tempfile;
--2.创建一个新的临时表空间
create temporary tablespace temp2 tempfile '/oradata/xxdb/temp2.dbf' size 200m;
--3.修改默认的temp表空间为新的临时表空间
alter database default temporary tablespace temp2;
--alter user xx temporary tablespace temp2;
--4.检查信息与状态
select * from dba_tablespaces where contents='TEMPORARY';
select username, temporary_tablespace from dba_users;
select name from v$tempfile;
--5.删除原来的默认临时表空间
drop tablespace temp including contents and datafiles;
--6.检查信息与状态
select * from dba_tablespaces where contents='TEMPORARY';
--7.重建默认temp表空间
create temporary tablespace temp tempfile '/oradata/xxdb/temp01.dbf' size 200m;
--8.还原默认temp表空间
alter database default temporary tablespace temp;
--alter user xx temporary tablespace temp;
--9.检查状态和信息
select username, temporary_tablespace from dba_users;
select * from dba_tablespaces where contents='TEMPORARY';
select d.tablespace_name,
space "sum_space(m)",
blocks sum_blocks,
used_space "used_space(m)",
round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
nvl(free_space, 0) "free_space(m)"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) space,
sum(blocks) blocks
from dba_temp_files
group by tablespace_name) d,
(select tablespace_name,
round(sum(bytes_used) / (1024 * 1024), 2) used_space,round(sum(bytes_free) / (1024 * 1024), 2) free_spacefrom v$temp_space_header
group by tablespace_name) f
where d.tablespace_name= f.tablespace_name(+);
--10.删除中间转换的临时表空间
drop tablespace temp2 including contents and datafiles;
如果出现：
ERROR at line 1:
ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 isblocked due to sort segments
--sql
select se.username,
se.sid,
se.serial#,
su.extents,
su.blocks * to_number(rtrim(p.value)) as space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name='db_block_size'
and su.session_addr= se.saddr
and s.hash_value= su.sqlhash
and s.address= su.sqladdr
order by se.username, se.sid;
alter system kill session '2276,41013';
alter system kill session '3413,42903';
select * from dba_tablespaces where contents='TEMPORARY'
浙公网安备 33010602011771号