#【Oracle学习15】 Oracle存储结构
15.1 了解块中表行数据的存储
15.1.1 Oracle 数据存储模型
15.1.2 段、区间、块和行
- 一个表空间可能是多个数据文件.
- 一个表空间可包含多个段，一个段是一个或多个区间， 一个区间是位于一个数据文件的多个连续块。
- 表空间可以存储： 永久对象，临时对象或撤消段
SQL> select segment_type,count(1) from dba_segments group by segment_type order by segment_type;
SEGMENT_TYPE COUNT(1)
------------------------------------ ----------
CLUSTER 10
INDEX 1812
INDEX PARTITION 327
LOB PARTITION 39
LOBINDEX 588
LOBSEGMENT 588
NESTED TABLE 19
ROLLBACK 1
TABLE 1617
TABLE PARTITION 423
TABLE SUBPARTITION 32
SEGMENT_TYPE COUNT(1)
------------------------------------ ----------
TYPE2 UNDO 20
12 rows selected.
示例:
SQL> create table scott.newtab(c1 date) segment creation immediate;
Table created.
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where owner='SCOTT' and segment_name='NEWTAB';
TABLESPACE FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ---------- ---------- ----------
USERS 7 0 544 8 65536
-- 结果表示File_id=7, 有8个blocks,第一个block_id=544,此段只有一个区extent_id=0;
-- 强制再分一个区段(extent).
SQL> alter table scott.newtab allocate extent;
Table altered.
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where owner='SCOTT' and segment_name='NEWTAB';
TABLESPACE FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
---------- ---------- ---------- ---------- ---------- ----------
USERS 7 0 544 8 65536
USERS 7 1 552 8 65536
SQL> select tablespace_name,file_name from dba_data_files where file_id=7;
TABLESPACE FILE_NAME
---------- ----------------------------------------
USERS /u01/app/oracle/oradata/orcl/users01.dbf
15.2 创建和管理表空间
15.2.1 创建表空间
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
SQL>select file_id,file_name from dba_data_files order by file_id;
SQL>CREATE SMALLFILE TABLESPACE "JWTS"
DATAFILE '/u01/app/oracle/oradata/orcl/zjwts01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
LOGGING
DEFAULT NOCOMPRESS
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
SQL> select file_id,file_name from dba_data_files order by file_id;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf
2 /u01/app/oracle/oradata/orcl/jinlian_undo.dbf
3 /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 /u01/app/oracle/oradata/orcl/undotbs01.dbf
5 /u01/app/oracle/oradata/orcl/mytest.dbf
7 /u01/app/oracle/oradata/orcl/users01.dbf
8 /u01/app/oracle/product/12.2.0/db_1/dbs/example.dbf
9 /u01/app/oracle/oradata/orcl/zjwts01.dbf
示例:
SQL>CREATE SMALLFILE TABLESPACE newtbs
DATAFILE '/u01/app/oracle/oradata/orcl/newtbs01.dbf'
SIZE 10M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
create table newtab(c1 date) tablespace newtbs;
select extent_id,bytes from dba_extents where owner='SYSTEM' and segment_name = 'NEWTAB';
SQL> alter table newtab allocate extent;
Table altered.
SQL> alter tablespace newtbs offline;
Tablespace altered.
SQL> delete newtab; --出错
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/newtbs01.dbf';
SQL> alter tablespace newtbs read only; --出错
SQL> alter tablespace newtbs online; --ok
SQL> alter tablespace newtbs read only;
SQL> delete newtab; --ok
SQL> alter tablespace newtbs read write;
SQL> drop newtab;
ERROR at line 1:
ORA-00950: invalid DROP option
SQL> drop tablespace newtbs including contents and datafiles;
15.2.2 更改表空间
1)重命名表空间及数据文件
- 可在线修改表空间，但是重命名数据文件，必须处于脱机状态。
ALTER TABLESPACE oldname RENAME TO newname;
SQL> select tablespace_name,BLOCK_SIZE,STATUS from dba_tablespaces where tablespace_name like 'JWTS%';
TABLESPACE_NAME BLOCK_SIZE STATUS
-------------------- ---------- ------------------
JWTS 8192 ONLINE
SQL> select file_id,TABLESPACE_NAME,status,file_name from dba_data_files where file_name like '%jw%' order by file_id;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ --------------------------------------------------
9 JWTS /u01/app/oracle/oradata/orcl/zjwts01.dbf
SQL> alter tablespace JWTS rename to zjwts; --ok
SQL> alter tablespace ZJWTS offline;
SQL> host mv /u01/app/oracle/oradata/orcl/zjwts01.dbf /u01/app/oracle/oradata/orcl/zjwts.dbf;
-- Oracle12C后可以online直接move
SQL> alter tablespace ZJWTS online;
SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/zjwts01.dbf' to '/u01/app/oracle/oradata/orcl/zjwts.dbf';
2)使表空间联机或脱机
SQL> alter tablespace ZJWTS offline normal;
SQL> insert into sys.newtab values(sysdate); --error
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/zjwts01.dbf'
SQL> alter tablespace ZJWTS online;
3)设置表空间只读
SQL> alter tablespace ZJWTS read write;
SQL>alter tablespace ZJWTS read only;
SQL> insert into sys.newtab values(sysdate); --error
ORA-00372: file 9 cannot be modified at this time
ORA-01110: data file 9: '/u01/app/oracle/oradata/orcl/zjwts01.dbf'
4)重新调整表空间的大小
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/zjwts01.dbf' resize 120m;
$ll -h /u01/app/oracle/oradata/orcl/zjwts01.dbf
-rw-r-----. 1 oracle oinstall 121M Feb 20 06:11 /u01/app/oracle/oradata/orcl/zjwts01.dbf
15.2.3 删除表空间
- 若是表空间包括任何对象，将删除失败。除非加incluing contents。
drop tablespace tablespacename [including contents [and datafiles]] ;
SQL> drop tablespace ZJWTS ; --error
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL>drop tablespace ANY_TABS including contents and datafiles;
15.2.4 区间管理
- 字典管理
- 本地管理: 建议使用本地管理
SQL> create tablespace small_tabs datafile 'small_tabs_01.dbf' size 200m extent management local uniform size 256k;
SQL> create tablespace any_tabs datafile 'any_tabs_01.dbf' size 200m extent management local autoallocate;
SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MANAGEMENT SEGMENT_SPAC
------------------------------ -------------------- ------------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
ANY_TABS LOCAL AUTO
SMALL_TABS LOCAL AUTO
15.2.5 管理段空间
SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
15.2.6 OMF
OMF (Oracle-Managed Files ,Oracle管理文件)。要启用OMF，需要设置一些实例参数。
- DB_CREATE_FILE_DEST : 数据文件及联机重做日志文件位置
- DB_CREATE_ONLINE_LOG_DEST_x: 联机重做日志文件位置。
- DB_CREATE_FILE_DEST： 归档日志和RMAN备份文件位置.
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/orcl';
SQL> col name format a50;
SQL> select name,value from v$parameter where upper(name) like 'DB_CREATE%';
NAME VALUE
-------------------------------------------------- --------------------------------------------------
db_create_file_dest /u01/app/oracle/oradata/orcl
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
SQL>drop tablespace ANY_TABS including contents and datafiles;
SQL>create tablespace any_tabs datafile 'any_tabs_01.dbf' size 200m extent management local autoallocate;
SQL> select file_id,TABLESPACE_NAME,status,AUTOEXTENSIBLE,file_name from dba_data_files where TABLESPACE_NAME like 'AN%' order by file_id;
--/u01/app/oracle/product/12.2.0/db_1/dbs/any_tabs_01.dbf
--使用OMF
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/orcl';
SQL> create tablespace omfts;
SQL> select file_id,TABLESPACE_NAME,file_name from dba_data_files where TABLESPACE_NAME like 'OMF%' order by file_id;
SQL> select file_name,bytes,autoextensible,increment_by,maxbytes from dba_data_files where tablespace_name ='OMFTS';
FILE_NAME BYTES AUTOEX INCREMENT_BY MAXBYTES
-------------------------------------------------------------------------------- ---------- ------ ------------ ----------
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_omfts_h4vzbvqz_.dbf 104857600 YES 12800 3.4360E+10
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_omfts_h4vzbvqz_.dbf' resize 500m;
SQL> select tablespace_name,file_name,bytes,autoextensible,increment_by,maxbytes from dba_data_files where tablespace_name like 'OMFT%';
TABLESPACE_NAME FILE_NAME BYTES AUTOEX INCREMENT_BY MAXBYTES
-------------------- --------------------------------------------------------------------- ---------- ------ ------------ ----------
OMFTS /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_omfts_h4vzbvqz_.dbf 524288000 YES 12800 3.4360E+10
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_omfts_h4vzbvqz_.dbf' autoextend on next 100m maxsize 2g;
SQL> drop tablespace OMFTS including contents and datafiles;
示例:
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/orcl';
SQL> create tablespace manualsegs segment space management manual;
SQL> select file_id,tablespace_name,file_name,bytes,autoextensible,increment_by,maxbytes from dba_data_files where tablespace_name like 'MANU%';
SQL> select tablespace_name,segment_space_management from dba_tablespaces where tablespace_name like 'MANU%';
TABLESPACE_NAME SEGMENT_SPAC
-------------------- ------------
MANUALSEGS MANUAL
SQL> create table mantab (c1 number) tablespace manualsegs;
SQL> create index mantabi on mantab(c1) tablespace manualsegs;
--
SQL> create tablespace autosegs;
--移到其它的表空间
SQL> alter table mantab move tablespace autosegs;
SQL> alter index mantabi rebuild online tablespace autosegs;
SQL> col SEGMENT_NAME for a20;
SQL> select segment_name,tablespace_name from dba_segments where segment_name like 'MANTAB';
SEGMENT_NAME TABLESPACE_NAME
-------------------- ------------------------------------------------------------
MANTAB AUTOSEGS
--删除原空间
SQL> drop tablespace manualsegs including contents and datafiles;
SQL> alter tablespace autosegs rename to manualsegs;
SQL> select file_id,tablespace_name,file_name,bytes,autoextensible,increment_by,maxbytes from dba_data_files where tablespace_name like 'MANU%';
SQL> drop tablespace manualsegs including contents and datafiles;
15.3 Oracle 数据库服务自动管理空间的方法
15.3.1 段空间的分配
- 段在一个或多个区间中包含多个块。
- 段是对象的容器，但是没有段，对象也可存在。
- deferred_segment_creation : 为true，对象有数据时才创建段。
SQL> col table_name for a20;
SQL> col segment_created for a30;
SQL> select table_name,segment_created from user_tables;
TABLE_NAME SEGMENT_CREATED
-------------------- ------------------------------
NO_EMPS YES
TMP_EMP YES
DEPT_TEMP YES
TB NO
SQL>select index_name,segment_created from user_indexes;
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> conn hr/hr
SQL> show user
USER is "HR"
SQL> create table deferred_seg(c1 date) segment creation deferred;
SQL> create table immediate_seg(c1 date) segment creation immediate;
SQL> select segment_name,extents from user_segments where segment_name like '%SEG';
SEGMENT_NAME EXTENTS
-------------------- ----------
IMMEDIATE_SEG 1
SQL> select table_name,segment_created from user_tables where table_name like '%SEG';
TABLE_NAME SEGMENT_CREATED
-------------------- ------------------------------
IMMEDIATE_SEG YES
DEFERRED_SEG NO
--播入数据后(deferred_segment_creation=true),对象中有数据时才创建段。
SQL> alter session set deferred_segment_creation=true;
SQL> insert into deferred_seg values(sysdate);
1 row created.
SQL> select segment_name,extents from user_segments where segment_name like '%SEG';
SEGMENT_NAME EXTENTS
-------------------- ----------
DEFERRED_SEG 1
IMMEDIATE_SEG 1
SQL> select table_name,segment_created from user_tables where table_name like '%SEG';
TABLE_NAME SEGMENT_CREATED
-------------------- ------------------------------
IMMEDIATE_SEG YES
DEFERRED_SEG YES
15.3.2 自动管理段空间
- PCTFREE(percent free) : 控制块保留空间，一般是10%。若是超过此设置，就由Automatic Segment Space Management(ASSM) 位图归类于FULL.
- 若是块空间不足，将移到其它空间块，称为行迁移。由update操作引起,Insert,update不会引起行迁移。
15.4 使用压缩节省空间
压缩可提高查询性能，但可能导致性能下降。
- 基本表压缩:
- 高级行压缩:
- 混合列式压缩(Hybrid COlumnar Compression ,HCC):
create table ... compress basic ; create table ... row store compress advanced;
15.5 积极监控和管理表空间的使用
Automatic Workload Repository(AWR):
--临时表空间及UNDO表空间阀值
SQL> select object_name,warning_operator,warning_value ,critical_operator,critical_value from dba_thresholds where metrics_name='Tablespace Space Usage';
OBJECT_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE
-------------------- ------------------------ ---------------------------------------- ------------------------ ----------------------------------------
JINLIAN_UNDO DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
TEMP DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
UNDOTBS1 DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
UNDOTBS2 DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO_NOT_CHECK 0
GE 85 GE 97
--dba_hist_tbspc_space_usage
SQL> select rtime,name,tablespace_usedsize from v$tablespace v join dba_hist_tbspc_space_usage d on (v.ts#=d.tablespace_id) order by name,rtime desc;
15.6 使用 Segment Advisor
Segment Advisor ： 默认为每晚运行为自动调度任务。
SQL> select segment_name,segment_type,recommendations from table(dbms_space.asa_recommendations);
no rows selected
15.7 使用段收缩功能从表和索引中回收空间
- Shrink space可重定位高水位线(HWM)
- Shrink前需要允许行移动, alter table xx enable row movement;
- Shrink和压缩不兼容, alter table xx nocompress;
语法： ALERT TABLE TABLE_NAME SHIRNK
-- Shrink前需要允许行移动
SQL> alter table hr.dept enable row movement;
SQL> alter table hr.dept shrink space cascade;
15.8 管理可恢复空间的分配
ALERT SESSION ENABLE RESUMABLE TIMEOUT seconds_num;
alter system set resumable_timeout=60;
