007.Oracle存储结构深入分析与管理
|
数据结构与表空间的概念及段的类型 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 使用介绍 more bbed.par添加如下代码： 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，创建一个检查点，提升性能 14.索引功能与分类 逻辑分类： 单列或多列 唯一索引和非唯一索引 函数索引 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.创建索引的时，可以不让他产生日志 05.SQL执行时，从后向前执行，把大的查询放到后面。 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 DQL：数据库查询语言 select 表空间创建过程 语法： 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; 表空间扩展与大小修改 001.添加数据文件， 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; 002.修改数据文件大小 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; 002.非在线（数据库关闭状态下） a.关闭数据库，启动到mount状态 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; 001.增加临时文件 alter tablespace temp add tempfile '/oracle/oradata/db01/iptux02.dbf' size 10M autoextend off; 002.修改临时文件 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; 找到相应的SQL后，kill掉这些sql语句 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; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |