Oracle 表空间专题

Oracle表空间专题

1.创建表空间

●--创建表空间完整语句

create smallfile tablespace "NEWTS"

datafile 'd:\app\oracle\oradata\orcl11g\newts01.dbf'

size 10m autoextend on next 10m maxsize 20m

logging

extent management local

segment space management auto

default nocompress;

2.更改表空间

●--使表空间脱机

alter tablespace tablespacename offline[normal | immediate | temporary];

●--使表空间只读

alter tablespace tablespacename[ read only | read write];

●--重命名表空间

alter tablespace tablespacename rename to tablespacenewname;

●--重命名数据文件

alter tablespace tablespacename offline; --首先使表空间脱机

HOST rename datafilename datafilenewname;--操作系统重命名数据文件名

alter database rename file datafilename to datafilenewname;--数据库重命名数据文件

alter tablespace tablespacename online; --使表空间联机

●--调整表空间的大小

alter database datafile filename resize n[m|g|t];--调整原来数据文件的大小alter tablespace tablespacename add datafile filename size n[m|g|t];--添加新的数据文件

alter dababase datafile filename autoextend on next n[m|g|t] maxsize n[m|g|t];--使数据文件自动扩展

3.删除表空间

●--删除表空间

drop tablespace tablespacename [INCLUDING CONTENTS [AND DATAFILES]];

4.段重组

1.Move表

Alter table move[];

执行完后，所以索引将失效，需要重建索引。

●重建无效对象

--查询无效对象

select owner,object_name,object_type from dba_objects where

status='INVALID';

--编译无效对象（无效对象在首次访问时自动尝试创建，如果不成功则返回错误消息,索引除外。索引必须手动重建）

alter compile;

--UNIX下批量编译脚本

SQL> @?/rdbms/admin/utlrp

--查询无效索引

select owner,index_name from dba_indexes where status='UNUSABLE';

--重建索引命令

alter index rebuild;

2.段收缩

●shrink;

alter table shrink space [cascade] [compact];

收缩操作的表须位于采用自动段空间管理功能和启用了行移动的表空间中，否则不能收缩。如果不满足这些条件，MOVE就是重组表的唯一方式。

Alter table enable row movement;

如果含有LOB数据见“3.LOB数据对象处理命令”。

●--清理回收站命令

purge recyclebin;

3.LOB数据对象处理命令

1.移动lob

alter table move

[tablespace]lob() store as (tablespace );

2.压缩、收缩lob

--对于securefile log

alter table modify lob(column_name) (compress);

alter table modify lob(column_name)(shrink space);

3.常用SQL

●--查询 table、tablespace、extent、datafile

select owner, --用户名

segment_name, --段名

partition_name, --分区名

segment_type, --段类型

tablespace_name, --表空间名

extent_id, --区间ID

file_id, --数据文件ID

block_id, --起始数据块ID

bytes, --区间的大小

blocks --块的个数

from dba_extents

where owner = 'APPS'

and segment_name = 'NEWTAB';

●--查询 datafile、tablespace

select file_id, --数据文件ID

bytes / 1024 / 1024 ,--分配的空间大小

status, --可用状态

online_status, --联机状态

autoextensible, --自动扩展

maxbytes, --最大可用空间

maxblocks, --块的最大数量

increment_by --每次增长的量

from dba_data_files

where tablespace_name = 'APPS_TS_MEDIA';

●--查询表空间详细信息

select t.tablespace_name name, --表空间名

d.allocated, --分配的数据文件当前大小,不包扩可扩展到的大小

https://www.360docs.net/doc/a814134056.html,ed, --不能回收的表空间中段占用的空间

f.free, --可用的空间

t.status, --联机状态

https://www.360docs.net/doc/a814134056.html,t, --数据文件的数量

t.contents, --表空间的类型

t.extent_management extman,--将区间分配到段的技术（扩展管理）

t.segment_space_management segman, --用于定位可执行插入的块的技术（段管理）

https://www.360docs.net/doc/a814134056.html,ed/d.allocated

from dba_tablespaces t,

(select sum(bytes) allocated, count(file_id) cnt

from dba_data_files

where tablespace_name = 'APPS_TS_MEDIA') d,

(select sum(bytes) free

from dba_free_space

where tablespace_name = 'APPS_TS_MEDIA') f,

(select sum(bytes) used

from dba_segments

where tablespace_name = 'APPS_TS_MEDIA') u

where t.tablespace_name = 'APPS_TS_MEDIA';

●--查询表空间信息

select tablespace_name --表空间名

block_size, --每个数据块的大小

initial_extent, --首次分配的区间大小

next_extent, --下一个分配的区间大小

status, --联机状态

contents, --表空间类型

logging, --为所有操作生成重做日志

bigfile--大文件，如果是BIGFILE,则无法为表空间增加第二个数据文件，但可以重调原来数据文件的大小

from dba_tablespaces;

●--查询表空间包含的段类型、及各自占用空间大小

select tablespace_name, --表空间名

owner, --用户名

segment_name, --段名

segment_type, --段类型

bytes/1024/1024 MB, --段占用的空间大小

partition_name --分区名

from dba_segments

where tablespace_name = 'APPS_TS_MEDIA'

--and segment_type like 'LOB%'

order by MB DESC,OWNER DESC;

●--查询锁表并解除锁定

方式一：PLSQL中解除锁

select sess.sid,

sess.serial#,

lo.oracle_username,

lo.os_user_name,

ao.object_name,

lo.locked_mode

from v$locked_object lo, dba_objects ao, v$session sess

where ao.object_id = lo.object_id

and lo.session_id = sess.sid;

杀掉锁

alter system kill session 'sid,serial#';

方式二：CRT中解除锁

select 'kill -9 ' || b.spid

from v$session a, v$process b

where a.paddr = b.addr

and https://www.360docs.net/doc/a814134056.html,ername is not null

and sid in (select kgllksnm

from x$kgllk

where kgllkhdl in

(select p1raw

from v$session_wait

where event like '%library cache%')); 查詢結果：

---xx-----------

kill -9 11684