007.Oracle存储结构深入分析与管理

前端之家收集整理的这篇文章主要介绍了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;

猜你在找的Oracle相关文章