Oracle 表空间管理

创建表空间GOOGLE_LOB

--创建表空间GOOGLE_LOB 用作大表的lob自动的存储区
CREATE TABLESPACE "GOOGLE_LOB" DATAFILE 
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_LOB1.DBF' SIZE 4G,
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_LOB2.DBF' SIZE 4G,
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_LOB3.DBF' SIZE 4G,
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_LOB4.DBF' SIZE 4G
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
;
--表空间增加文件
ALTER TABLESPACE GOOGLE_LOB ADD DATAFILE '/opt/oracle/oradata/GGTEST/datafile/GOOGLE_LOB5.DBF' SIZE 4G;

--示例。将表DEMO_T_1字段FILE_CONTENT 存储转移到 表空间GOOGLE_LOB 中
ALTER TABLE GOOGLE.DEMO_T_1 MOVE LOB(FILE_CONTENT) STORE AS (TABLESPACE GOOGLE_LOB);

创建索引表空间

--创建索引表空间
CREATE TABLESPACE "GOOGLE_IDX" DATAFILE 
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_IDX01.DBF' SIZE 4G
LOGGING ONLINE PERMANENT BLOCKSIZE 16K
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
;
--示例。 表DEMO_T_1主键存储到 表空间GOOGLE_IDX
alter index GOOGLE.PK_DEMO_T_1 rebuild tablespace GOOGLE_IDX;

创建临时表空间

--创建临时表空间
CREATE TEMPORARY TABLESPACE "GOOGLE_TEMP" TEMPFILE 
'/opt/oracle/oradata/GGTEST/datafile/GOOGLE_TEMP.DBF' 
SIZE 2G
AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 20M
;
--示例。将用户临时表空间调整为 GOOGLE_TEMP
ALTER USER GOOGLE TEMPORARY TABLESPACE GOOGLE_TEMP

删除表空间

--删除表空间
drop TABLESPACE GOOGLE_LOB INCLUDING CONTENTS AND DATAFILES;
drop TABLESPACE GOOGLE_IDX INCLUDING CONTENTS AND DATAFILES;
drop TABLESPACE GOOGLE_TEMP INCLUDING CONTENTS AND DATAFILES;

表空间的其他sql

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space
from dba_data_files order by tablespace_name;
--查询表空间使用情况：
select a.tablespace_name,a.bytes/1024/1024 "sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name (+)
order by ((a.bytes-b.bytes)/a.bytes) desc;
--////////////////////////////////////////////////////////////
-- 表空间扩展方法 
--////////////////////////////////////////////////////////////
--现有表空间路径65536 65536 1 1 
--方法一：增加表空间  增加数据文件个数
alter tablespace ESPS_2008  add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\XXXXXX.DBF' size 1000m;
--方法二：设置表空间自动扩展。
alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\XXXXXX.DBF' autoextend on next 100m maxsize 10000m;
--方法三：重新设计表空间。
alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\XXXXXX.DBF' resize 10000m;
alter tablespace ESPS_2008 rename to ESPS_20082;

--查询表空间使用明细
SELECT T.OWNER,
       T.SEGMENT_NAME,
       T.SEGMENT_TYPE,
       PARTITION_NAME,
       T.TABLESPACE_NAME,
       T.BYTES / 1024 / 1024 "sum MB"
  FROM DBA_SEGMENTS T
 WHERE TABLESPACE_NAME = 'SYSTEM'
 ORDER BY T.BYTES DESC;
--查看表空间下lob字段使用情况
SELECT A.OWNER,
       A.TABLE_NAME,
       A.COLUMN_NAME,
       B.SEGMENT_NAME,
       B.SEGMENT_TYPE,
       B.TABLESPACE_NAME,
       B.BYTES / 1024 / 1024 "sum MB"
  FROM DBA_LOBS A
  JOIN DBA_SEGMENTS B ON A.SEGMENT_NAME = B.SEGMENT_NAME
 WHERE B.TABLESPACE_NAME = 'SYSTEM'
 ORDER BY B.BYTES DESC;