数据结构与表空间的概念及段的类型
02.表空间的定义与默认表空间微加介绍
system: sys/system
sysaux: 保存系统的组件和产品,作为system的后续表空间
temp:临时表空间,主要用于排序,运算等,使用完成后,会自动清除
undo:(undotbs1)用于存储还原信息的表空间,回滚表空间,通过他能解决数据不一致的问题。在任何时候都只使用一个undo表空间
user:用户表空间,系统默认表空间
03.段(segments)的定义/类型/了解段是怎么创建
table
cluster(组)
table partition(表分区)
index
index-organized table:存储在组织结构中的表,节省磁盘空间占用,加IO,减少缓存池
index partition:全局分区索引,本地分区索引。
lob
nested table
rollback:回滚段存放数据修改之前的值
temp segments:主要解决排序问题
段是怎么创建的
> create user itpux identified by iptux; 创建itpux用户,密码:iptux
> grant dba to itpux; 给itpux授权DAB权限
> create table itpux1(id number); 创建itpux表
> select * from itpux1; 查看itpux表
> select * from user_segments; 查看用户段
> insert into itpux1 (id) values (1); 插入数据
> commit; 提交
注:在10g时,不插入数据就会生成段,在11g时,不插入数据时,不会生成段。
想11g创建表时,自动生成段:需要创建表时添加 segment creation immediat 参数
> create table itpux2 (id number) segment creation immediate;
> select * from itpux2;
> select * from user_segments;
> create table itpux3 (id number primary key ) segment creation immediat;
创建带主键的表
> create table itpux3 (id number primary key ) segment creation immediate;select * from user_segments;
> select * from user_segments;
创建大字段表
> create table itpux4 (a int primary key,b clob,z blob ) segment creation immediate;
> select * from user_segments;
查看块大小(在sqlplus执行)
> show parameter db_block
> insert into itpux1 select rownum from dba_tables where rownum <=10000;
> select * from user_segments;
04.段(segments)分配情况对比(普通表/分区表/大对象表)
05.区
truncate table itpux1 删除表
truncate table itpux2
truncate table itpux3
truncate table itpux4
truncate table:
有时候我们会需要清除一个表格中的所有资料。要达到者个目的,
一种方式是我们在 sql DROP 那一页 看到 的 DROP TABLE 指令。
不过这样整个表格就消失,而无法再被用了。另一种方式就是
运用 TRUNCATE TABLE 的指令。在这个指令之下,表格中的资料会完全消失,
可是表格本身会继续存在。
drop table itpux1; 删除表
drop table itpux2;
drop table itpux3;
drop table itpux4;
select * from user_recyclebin;
purge recyclebin;
如何查看是否开启回收站功能
show parameter recyclebinon:
表示表空间启用的回收站功能,建议所有数据都开启这个功能,百利而无一害!
备注:该参数可以设置成session级别打开,也可以设置成system级别,不用重启就可以生效
如何不经过回收站直接删除并释放所占用空间?
sql> drop table cube_scope purge
备注:此命令相当于truncate+drop操作,一般不建议这么操作!、
如何将回收站recyclebin中的对像还原?
sql> flashback table cube_scope to before drop
表名可以是回收站系统的dba_recyclebin.object_name也可以是dba_recyclebin.original_name
但是此时问题来了,我已经用备份的DDL语句重建了一个新的表,这个时候再用此命令还原显然会
报错,这个时候怎么办呢,只能还原成一个别名,具体操作命令是
sql> flashback table cube_scope to before drop rename to cube_scope_old
既然恢复了删除前的表中数据,现在只能从cube_scope_old中的数据插入cube_scope中
sql> insert into cube_scope select * from cube_scope_old t
成功恢复了数据,是不是可以收工了?没有,还有什么忘记做了?想想?
注意:如果将表drop掉,那么索引也被drop掉了,用这种方法把表找回来了,但是你的
索引呢?你的约束呢?表恢复后一定要将表上的索引重建建立起来(切记),索引丢了最多
影响性能,约束没了可能会造成业务数据混乱(一定要注意)
如何手工清除回收站中的对像?
sql> purge table orabpel.cube_scope_old --清除具体的对像
注意:如果此时是DBA用户操作其它用户数据,清除回收站中的表时要加上用户名,否则报表不在回收站中
sql> purge tablespace ORAPEL --清除指定的表空间对像
sql> purge tablespace ORAPEL user orabpel --删除表空间指定用户下的所有对像
show recyclebin为什么没有数据呢?
首先们需要明白一点,recyclebin是user_recyclebin的同义词,如此你当前的登陆用户是system此时运用
show recyclebin是没有数据据的
如果同一对像多次删除怎么在recyclebin中识别?
dba_recyclebin中对每删除一个对像都会以BIN$进行命名,同时会有相应的dropscn、createtime、droptime可以跟据这些对像进行定位,然后进行恢复
ORACLE空间利用原则
1. 使用现有的表空间的未使用空间
2. 如果没有了空闲空间,则检查回收站,对于回收站的对象按照先进先出的原则,对于最先删除的对象
oracle在空间不足之时会最先从回收站删除以满足新分配空间的需求
3. 如果回收站也没有对象可以清理,则检查表空间是否自扩展,如果自扩展则扩展表空间,然后分配新空间
4.如果表空间非自扩展,或者已经不能自扩展(到达最大限制),则直接报表空间不足错误,程序终止
DROP掉的对像是不是都会经过回收站?
以下几种drop不会将相关对像放进回收站recyclebin中
* drop tablespace :会将recyclebin中所有属于该tablespace的对像清除
* drop user :会将recyclebin中所有属于该用户的对像清除
* drop cluster : 会将recyclebin中所有属于该cluster的成员对像清除
* drop type : 会将recyclebin中所有依赖该type对像清除
另外还需要注意一种情况,对像所在的表空间要有足够的空间,不然就算drop掉经过recyclebin由于空间不足oracle会自动删除的哦(切记)!
sql> purge recyclebin --清空整个回收站
select * from user_extents; 区视图
create table itpux1 as select * from v$datafile;
select * from user_extents where segment_name='ITPUX1' 注:名称需要大写
存储过程
begin
insert into itpux1 select * from v$datafile;
for i in 1..8 loop
insert into itpux1 select * from v$datafile;
commit;
end loop;
end;
select count (*) from itpux1;
回收区
delete from itpux1;
truncate table itpux1;
06.数据块(database block )的介绍及oracle是如何
数据块格式 :块头(Header)、表目录(Table Directory)、行目录(Row directroy)、自由空间(Free space)、行数据(Row Data)
数据块内容:Header、 Free space 、data
水位的概念:
07.数据块(database block)的基本结构分析
数据块的基本结构4部分:数据块头,事务槽ITL,数据,数据块尾
分析:
以下在sqlplus里执行:
select name from v$datafile;
create tablespace itpuxblock datafile '/oradata/db01/itpuxblock.dbf' size 20M;
select name from v$datafile;
create table itpuxblock(id number,name varchar2(4)) tablespace itpuxblock;
insert into itpuxblock values(1,'a');
insert into itpuxblock values(2,'b');
insert into itpuxblock values(3,'c');
commit;
select * from itpuxblock;
select rowid,
dbms_rowid.rowid_relative_fno(rowid)
rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno
from sys.itpuxblock;
none> oradebug setmypid
none> oradebug tracefile_name;
none> alter system dump datafile 6 block 139; 注:6和139由select rowid查询的结果取得
scn: 0x0000.00000000 \\前两个字节0000表示 scnwrap,后面四个字节,表示scn base
> show parameter db_block_checksum
DROP TABLESPACE itpuxblock INCLUDING CONTENTS AND DATAFILES; 删除表空间
xid=undo segments number + 事务表 slot number +wrap,(undo段号+undo槽号+ undo槽号的覆盖次数)
Uba=回滚块地址(undo文件号+数据块号)+回滚序列号+回滚记录号
select xidusn,xidslot,xidsqn,ubafil,ubablk,ubaasqn,ubarec from v$transaction
xidusn
xidslot
xidsqn
ubafil
ubablk
ubaasqn
ubarec
flag状态:
c :已提交,锁已被清除
b :undo记录包含的撤消ITL条目
u :快速提交,锁还没清除
t :active ao csc
--- :表示该事务是活动的
fb:
--H-FL--
-KHDFLPN
K- cluster key
H- head for row piece
D-delete row
F-first data piect
L- last data
P- first column cintinues from prevIoUs row
N- last column cintinues in next pice
08.BBED分析块结构与BBED修改数据块
0801.BBED安装
将以下5个文件(从10G)拷贝到11g相应目录:
$ORACLE_HOME/rdbms/lib/ssbbded.o
$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/mesg/bbedus.msg
$ORACLE_HOME/rdbms/mesg/bbedus.msb
$ORACLE_HOME/rdbms/mesg/bbedar.msb
再:
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
password:blockedit
0802 使用介绍
blocksize=8192
listfile=filelist.txt
mod=edit
more filelist.txt
none> select file#,name,bytes from v$datafile;
FILE# NAME BYTES
---------- ---------------------------------------- ----------
1 /oradata/db01/system01.dbf 796917760
2 /oradata/db01/sysaux01.dbf 608174080
3 /oradata/db01/undotbs01.dbf 99614720
4 /oradata/db01/users01.dbf 5242880
5 /oradata/db01/example01.dbf 363069440
6 /oradata/db01/itpuxblock.dbf 20971520
执行:
bbed parfile=bbed.par
断电后报 ORA-01200 解决办法
原因:因为控制文件记录的值不一样
> select blocks,file# from v$datafile;
通过BBED把控制文件的值,改回与原来相同的值
> alter session set events 'immediate trace name file_hdrs level 10';
>select to_char(原来数值,'xxxxxx') from dual;
>select to_char(现在数值,'xxxxxx') from dual;
BBED> find /x 现在数据 找到现在坏块9600
BBED> modify /x 原来数值
BBED> sum apply
表
表分类:
Regular table(规则表)、
Partitioned table (分区表)、
index-organized table (组织表)、
Cluster( 组表)
示例:
create table itpux001
(a int,
b varchar2(4000) default rpad('*',4000,'*'),
c varchar2(4000) default rpad('*','*')
)
构建类型:单一类型,关系型,字符型
Extended Rowid fromat:
1-6位:Data object number 数据对象编号
7-9位:Relative file number
9-15位:Block number
16-18位:Row number
查看行信息:行号,文件号,对像号,块号
select dbms_rowid.rowid_object(rowid) obj_id,
dbms_rowid.rowid_relative_fno (rowid) rfile_id,
dbms_rowid.rowid_block_number (rowid) block_id,
dbms_rowid.rowid_row_number (rowid) row_id,
dbms_rowid.rowid_to_absolute_fno (rowid,'SYS','ITPUXBLOCK') file#
from sys.itpuxblock;
select * from sys.itpuxblock;
select dump (rowid,16) from sys.itpuxblock;
创建表空间时参数:
pctfree 数值 :表示表空间的极限值,空隙空间小于该数值时,块标记为空
存储参数:
initial :当前段的大小
next :下一个段的大小
minextents :最小段数量
maxextents unlimited :最大段数量
12.临时表的功能与应用
临时表,只对当前连接有效,临时表是不建立索引
数据复杂时用临时表,不复杂时用视图,在仅仅查询数据时用游标
临时表,不支持大字段。
临时表可以创建视图,索引,触发器
事务级临时表
create global temporary table temp_transaction
on commit delete rows as
select * from sys.itpuxblock;
注:on commit delete rows :表示创建事务级表
select * from temp_transaction;
会话级临时表
create global temporary table temp_session
on commit preserve rows as
select * from sys.itpuxblock;
注:on commit preserve rows 表示创建的是session 级别
select * from temp_session;
create global temporary table 表名;
insert into temp_session select * from sys.itpuxblock;
insert into temp_transaction select * from sys.itpuxblock;
commit;
13.表的行迁移与行链接
row migration 行迁移:数据块扩展放不下后,用指针指向下一个块
row chaining :如果一条记录的某个列很长,oracle就会把分为不同的部分。
表的日常操作
001.修改表的参数
002.表的改变
移动表
alter table itpuxblock move tablespace itpuxblock;
003.清空表
truncate table itpuxblock
注:如果表被其它引用,是无法清除
004.删除表
drop table itpuxblock;
drop table itpuxblock cascade constraints; 如查表被其它表关联,则用此命令
005.删除某列数据
alter table itpuxblock drop column comments cascade constraints checkpoint 1000;
注:checkpoint 1000 这个参数,执行1000,创建一个检查点,提升性能
逻辑分类:
单列或多列
唯一索引和非唯一索引
函数索引
Doman
物理分类:
B-TREE(B树索引)
Bitmap(位图索引)
15.索引使用规则与日常操作过程
001.创建一个普通的索引(B-TREE)
create table itpux (id int,sex char(1),name char(10));
select * from itpux;
begin
for i in 1..1000
loop
insert into itpux values(i,'M','itpux');
end loop;
commit;
end;
select count(*) from itpux;
create index itpux_idx1 on itpux(id);
create index itpux_idx2 on itpux(sex,name);
select object_name,object_type from user_objects
查看索引是否被使用
SYS@db01> conn itpux/iptux;
ITPUX@db01> set autotrace traceonl
ITPUX@db01> select * from iptux;
ITPUX@db01> select * from itpux where id >15 and id<18;
ITPUX@db01> select * from itpux where id =15
ITPUX@db01> select * from itpux where sex='M' and name='itpux';
002.创建一个位图索引(Bitmap)
create bitmap index my_bit_idx on itpux(sex);
select object_name,object_type from user_objects;
003.创建索引的规则
01.索引不要创建得过多
02.可以为索引放在不同的表空间
03.最好使用同样大小的块,最好块大小是5块的整数倍
04.创建索引的时,可以不让他产生日志
004.修改索引
alter index ITPUX_BIT_IDX storage (next 2M); 修改索引参数
alter index itpux_idx1 rename to itpux_index_id;
alter index itpux_index_id deallocate unused; 释放索引空间
005.重新创建索引
定期重建索引
alter index 索引名 rebulid
alter index itpux_index_id rebulid; 重建索引
重建索引的内部过程
a.锁表 (需要在非业务期间锁表)
b.创建新的临时索引
c.删除老的索引
d.把新的索引名重命名为老的索引
e.对表进行解锁
alter ... rebulid tablespace new_tbs; 重建索引到新的表空间(移动索引)
alter index rebulid online; 在线重新创建索引。
在线重新创建索引内部过程:
a.锁表 (需要在非业务期间锁表)
b.创建临时索引和索引组织表,用来存放on-going DML操作,普通表存放键值,IOT索引直接存放表中数据,on-going DML就是用户所做的一些增删改操作
c.对表进行解锁
d.从老的索引中创建一个新的索引
e.将IOT表的内容与新创建的索引合并
f.锁住表
g.再次将IOT表的内容更新到新的索引中,把老的索引干掉
h.把新的索引重命名为索引中的名字
i.对表进行解锁
006.索引碎片整理
alter index 索引名 coalesce;
007.删除索引
drop index 索引名;
008.分析索引
analyze index orders_region_id_idx VALIDATE STRUCTURE;
select count(*) from index_stats; 查看分析索引表
select * from index_stats;
exec dbms_stats.gather_table_stat ('ITPUX','ITPUX'); 注:需要在数据库中执行
alter index 索引名 coalesce; 索引碎片整理
select * from index_stats;
表空间的日常维护与管理
16.DDL、DML、DCL的理解与表空间创建相关参数说明
DDL:数据库模式定义语言
create,drop,alter
DML:数据库操纵语言
insert,delete,update
DCL:数据库控制语言
grant,revoke
表空间创建过程
语法:
CREATE TABLESPACE 表空间名字 DATAFILE‘数据文件’ EXTENT MANAGEMENT {LOCAL {AUTOALLOCATE|UNIFORM [SIZE INTETER[KIM]]}}
CREATE TABLESPACE itpuxdat DATAFILE
'/oracle/oradata/db01/iptux01.dbf' SIZE 250M AUTOALLOCATE ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE --区的管理方式(本地)
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO --段的管理方式
FLASHBACK ON; --闪回打开
查看表空间信息
select * from dba_tablespaces
select * from v$tablespace
查看数据文件
select * from dba_data_files
select * from v$datafile
临时文件信息
select * from dba_temp_files
select * from v$tempfile
创建大表空间
create bigfile tablespace iptuxbig datafile '/oracle/oradata/db01/iptux05.dbf' size 1M;
表空间扩展与大小修改
alter tablespace itpux02 add datafile '/oracle/oradata/db01/iptux05.dbf' size 1M autoextend off;
alter tablespace temp1 add datafile '/oracle/oradata/db01/iptux05.dbf' size 1M autoextend off;
alter database datafile '/oracle/oradata/db01/iptux05.dbf' resize 2M;
alter database tempfile '/oracle/oradata/db01/iptux06.dbf' reszie 2M;
003.表空间重命名
alter tablespace itpux02 rename to itpux03;
删除表空间
drop tablespace02; 直接删除表空间,但不删除对应文件
drop tablespace02 including contents; 删除表空间对应的段
drop tablespace02 including contents and datafiles; 删除表空间的段和对应文件 (主要用这个)
drop tablespace02 including contens and cascade constraints; 删除表空间,和删除所有与该表空间相关的完整性约束条件。
更改表空间的模式:
读写模式
alter tablespace itpux01 read only;
alter tablespace itpux01 read write;
在11G时,可以开表的读写
alter table itpux read only;
alter table itpux read write;
在线与非在线模式
alter tablespace itpux01 offline;
alter tablespace itpux01 online;
指定表空间数据文件
alter database datafile '/oracle/oradata/db01/iptux03.dbf' offline;
alter database datafile '/oracle/oradata/db01/iptux03.dbf' offline for drop; 一般在非才华归档模式下进行
18.表空间的创建/日常维护管理/性能安全
查看表空间信息
dba_tablespaces
v$tablespace
CREATE TABLESPACE itpuxdat DATAFILE
'/oracle/oradata/db01/iptux01.dbf' SIZE 250M AUTOALLOCATE ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
--nologging 不写重做日志文件
--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 ... rebulid
物化视图的操
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE --区的管理方式(本地)
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO --段的管理方式
FLASHBACK ON; --闪回打开
alter tablespace itpuxdata logging; 写重做日志
alter tablespace itpuxdata logging; 不写重做日志
select tablespace_name,logging from dba_tablespaces;
19.表空间的性能与
show parameter db_block_size;
show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
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
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
alter system set db_32k_cache_size=1M scope=both;
show parameter db_32k_cache_size
create tablespace itpux08 datafile '/oracle/oradata/db01/iptux08.dbf' size 2M autoextend off blocksize 32K;
select dbms_Metadata.get_ddl('TABLESPACE','ITPUSX08') from dual;
20.表空间的迁移(数据文件与数据对象)
---移动数据文件
001.在线(数据库打开的情况下)
a.确认数据文件状态是offline状态,否则要修改为offline状态
b.host copy/cp 移动数据文件
c.通过 alter tablespace 重新命名表空间信息
换存储时会用到如下:
d.drop tablespace itpux01 including contents and datafiles;
--1.查看表空间,创建表空间
select* from dba_data_files
create tablespace itpux01 datafile '/oradata/db01/itpux01.dbf' size 2M autoextend off ;
-- 确认数据文件状态是offline状态,否则要修改为offline状态
select * from dba_data_files where tablespace_name='ITPUX01';
alter tablespace itpux01 offline;
select * from dba_data_files where tablespace_name='ITPUX01';
--3.host copy/cp 移动数据文件
host copy /oradata/db01/itpux01.dbf /oradata/db01/itpux001.dbf --在sqlplus里执行
--如果是unix/linux
host cp /oradata/db01/itpux01.dbf /oradata/db01/itpux001.dbf --在sqlplus里执行
--4.通过 alter tablespace 重新命名表空间信息
alter tablespace itpux01 rename datafile '/oradata/db01/itpux01.dbf' to '/oradata/db01/itpux001.dbf';
--5.修改为表空间状态为online
alter tablespace itpux01 online;
shutdown immediate;
b.host copy 移动数据文件
host cp /oradata/db01/itpux01.dbf /oradata/db01/itpux001.dbf
c.通过 alter tablespace 重新命名表空间信息
alter tablespace itpux01 rename file '/oradata/db01/itpux01.dbf' to '/oradata/db01/itpux001.dbf';
d.打开数据库
alter database open;
e.检查状态
select * from dba_data_files where tablespace_name='ITPUX01';
移动表或对像到其它表空间
--a.检查状态
create tablespace itpux01 datafile '/oradata/db01/itpux01.dbf' size 2M autoextend off ;
create tablespace SCDATA01 datafile '/oradata/db01/scdata01.dbf' size 2M autoextend off ;
select* from dba_data_files
create table itpux.table01 (id number(12),c_data date)
insert into itpux.table01 values(1,sysdate);
insert into itpux.table01 values(2,sysdate);
insert into itpux.table01 values(3,sysdate);
insert into itpux.table01 values(4,sysdate);
insert into itpux.table01 values(5,sysdate);
create index idx_table01_id on itpux.table01('id')
--tbs:USERS
select * from dba_segments where owner='ITPUX' and segment_name in ('TABLE01','IDX_TABLE01_ID')
--type:index and table
select * from dba_objects where owner='ITPUX' and index_name='IDX_TABLE01_ID'
select * from dba_indexes where owner='ITPUX' and object_name in ('TABLE01','IDX_TABLE01_ID')
--把users表空间itpux用户的对象迁移到itpux01表空间里
--b.移动对象到别一表空间
alter table itpux.TABLE01 move tablespace itpux01;
alter index itpux.IDX_TABLE01_ID rebuild tablespace itpux01;
--c.检查信息
--d.通过重建索引的方法移动到别一个表空间
--e.再检查确认
--如果有大字段
alter table itpux.table01 move lob(data) store as (tablespace itpux01);
22.oracle自动管理OMF
none> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- -------
db_create_file_dest string
none> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string 定义重做日志文件和控制文件的创建路径
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
none> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string 定义快速恢复区的默认位置
db_recovery_file_dest_size big integer 0
23.sytem与sysaux表空间满了怎么办
---扩展表空间
system表空间满了
1.检查非sys,system用户的数据是否存在system表空间
select * from dba_segments where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM')
2.存在用户outln的数据
3.查询占用空间最大的对象
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
4. 查到对象表占用最大的(这里为IDL_UB)
5.扩展tablespace
alter tablespace system add datafile '/oracle/oradata/db01/system02.dbf' size 10M autoextend off;
6.检查表空间
---分析问题
sysaux表空间满了,把没用的迁移走
1.检查非sys,system用户的数据是否存在sysaux表空间
select * from dba_segments where tablespace_name='SYSTEM' and segment_name like '%MNR%';
select * from v$sysaux_occupants where occupant_name='LOGMNR';
2.移动
exec sys.dbms_logmnr_d.set_tablespace('USERS') 注:需要在sqlplus里执行
3.验证
select * from dba_segments where tablespace_name='SYSAUX' and segment_name like '%MNR%'
select * from v$sysaux_occupants where occupant_name='LOGMNR'
select * from dba_segments where tablespace_name='SYSAUX' and segment_name like '%MNR%'
4.还原
exec sys.dbms_logmnr_d.set_tablespace('SYSAUX') 注:需要在sqlplus里执行
select * from v$sysaux_occupants where occupant_name='LOGMNR'
select * from dba_segments where tablespace_name='SYSAUX' and segment_name like '%MNR%'
24.临时表空间满的处理方法
000.查看临时文件
select * from dba_temp_files;
select * from dba_tablesspaces;
select * from v$tempfile;
alter tablespace temp add tempfile '/oracle/oradata/db01/iptux02.dbf' size 10M autoextend off;
alter dtatbase tempfile '/oracle/oradata/db01/iptux02.dbf' resize 20M ;
003.shrinking 释放临时表空间
alter tablespace temp shrink space keep 收缩大小;
alter tablespace temp shrink tempfile'/oracle/oradata/db01/iptux02.dbf';
25.默认临时表空间TEMP的过大处理办法
001.检查状态和信息
select * from dba_tablesspaces where contens='TEMPORARY'
select username,temporary_tablesapce from dba_users;
select name from v$tempfile;
002.创建一个新的临时表空间
create temporary tablespace temp2 tempfile '/oracle/oradata/db01/temp2.dbf' size 10m;
003.修改默认tmep表空间为新的临时表空间
alter database default temporary tablespace temp2;
select * from dba_tablesspaces where contens='TEMPORARY'
--alter user iptux temporary tablespace temp2;
004.查状态和信息
select * from dba_tablesspaces where contens='TEMPORARY'
select username,temporary_tablesapce from dba_users;
select name from v$tempfile;
005.删除原来默认的临时表空间
drop tablespace temp including contents and datafile;
006.查状态和信息
select * from dba_tablesspaces where contens='TEMPORARY'
select username,temporary_tablesapce from dba_users;
select name from v$tempfile;
007.重建原来的临时表空间
create temporary tablespace temp tempfile '/oracle/oradata/db01/temp1.dbf' size 20m;
008.修改默认tmep表空间为新的临时表空间
alter database default temporary tablespace temp;
select * from dba_tablesspaces where contens='TEMPORARY'
--alter user iptux temporary tablespace temp2;
009.查状态和信息
select * from dba_tablesspaces where contens='TEMPORARY'
select username,temporary_tablesapce from dba_users;
select name from v$tempfile;
010.删除蹭转换的的临时表空间
drop tablespace temp2 including contents and datafile;
如果无法删除 http://www.itpux.com/thread-3023-1-1.html
oracle查看当时有哪些地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 '111,12345';
011.查状态和信息
select * from dba_tablesspaces where contens='TEMPORARY'
select username,temporary_tablesapce from dba_users;
select name from v$tempfile;