oracle實驗記錄（storage儲存引數(1)）

實驗表空間,表的 storage引數

init:第一個區大小
next:Linit後下個區大小
MINEXTENTS：這是要分配的最小區數。
MAXEXTENTS：這是要分配的最大區數。如果將MINEXTENTS 指定為一個大於1 的值，而表空間包含多個資料檔案，則這些區將分佈在不同的資料檔案中。
PCTINCREASE：這是NEXT 區及以後的區有關區大小增長的百分比
PCTFREE：指定表內每個資料塊中空間的百分比。PCTFREE 的值必須介於0 和99
之間。如果值為零，表示可以通過插入新行來填充整個塊。預設值為10。此值表示每
個塊中保留著10% 的空間，用於更新現有的行以及插入新行，每個塊最多可填充到
90%。
PCTUSED：指定為表內每個資料塊維護的已用空間的最小百分比。如果一個塊的已用
空間低於PCTUSED，則可在該塊中插入行。PCTUSED 的值為介於0 和99 之間的整
數，預設值為40。(segment management space manual時候控制塊是否在FREELIST上),pctfree加pctused要小於100

總得來說PCTFREE 就是告訴ORACLE 什麼時候把BLOCK從FREELIST拿走(自動段空間管理沒有FREELIST)拿走後即使空閒空間>PCTFREE也不會放入FREELIST(使用空間<90%時),只有當使用空間

initans:塊中事務槽數量 10G default 2個(表示同時有兩個事務可以對這個塊修改~~一個塊中可以存很多行)
MAXTRANS:最多可以設定多少預設值為255 10G 忽略
freelist(freelist groups):如果 segment management space manual空間分配使用都靠freelist(buffer busy waits class# 為4的時候是段頭爭用需要加大freelist default

1)當一個insert從freelist中插入一個快時候,此塊已經用空間>PCTUSED,oracle把它從freelist中移走, 當delete,update後,oracle檢查相關的塊如果已用空間

放入freelist中,若當前事務(update,delete釋放空間的事務)若該事務還要些入資料這這個塊首先使用,該事務未COMMIT時此塊要等此事務COMMIT後才能讓別的事務使用這個塊

的空間,
HWM下的塊才出現在FREELIST,如果FREELIST中為空那HWM就向上分配新塊並放入freelist中

***************(選自9I10G 程式設計藝術)
使用多個freelist時，有一個主freelist，還有一些程式freelist。如果一個段只有一個freelist，那麼主freelist和程式freelist就是這同一個自由列表。如果你有兩個

freelist，實際上將有一個主freelist和兩個程式freelist。對於一個給定的會話，會根據其會話ID的雜湊值為之指定一個程式 freelist。目前，每個程式freelist都只有很少的

塊，餘下的自由塊都在主freelist上。使用一個程式freelist時，它會根據需要從主freelist拉出一些塊。如果主freelist無法滿足空間需求，Oracle就會推進HWM，並向主

freelist中增加空塊。過一段時間後，主freelist會把其儲存空間分配多個程式freelist（再次說明，每個程式freelist都只有為數不多的塊）。因此，每個程式會使用一個進

程freelist。它不會從一個程式freelist到另一個程式freelist上尋找空間。這說明，如果一個表上有10個程式 freelist，而且你的程式所用的程式freelist已經用盡了該列表中

的自由緩衝區，它不會到另一個程式freelist上尋找空間，即使另外9 個程式freelist都分別有5塊（總共有45個塊），此時它還是會去求助主freelist。假設主freelist上的空間

無法滿足這樣一個自由塊請求，就會導致表推進HWM，或者如果表的HWM無法推進（所有空間都已用），就要擴充套件表的空間（得到另一個區段）。然後這個程式仍然只使用其

freelist上的空間（現在不再為空）。使用多個freelist時要有所權衡。一方面，使用多個freelist可以大幅度提升效能。另一方面，有可能導致表不太必要地使用稍多的磁碟空

間。你必須想清楚在你的環境中哪種做法麻煩比較小。
********************

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0004.02a.00000b76 0x008007de.032c.2a C---    0 scn 0x0000.005e656c
0x02   0x0004.026.00000b76 0x008007de.032c.29 C---    0 scn 0x0000.005e5e41

SQL> desc user_tables;
PCT_FREE~~~~~~~~~~~~~~~~~~儲存資訊
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS

segment management space AUTO時 freelist,freelistgroups ,pctUSED都不管用了因為不使用freelist了,用bitmap表示塊狀態了,不過pctfree還有作用還限制新行能否插入一

個塊中

*************臨時表
SQL> create global temporary table test_temp2 (a int) ON COMMIT PRESERVE ROWS;

Table created.

SQL> insert into test_temp2 values(1);

1 row created.

SQL> select * from test_temp2;

A
----------
1

SQL> commit;~~~~~~~~~~~~~~~~~~~~~~~~~會話期間都存在即便commit 也還在
~
Commit complete.

SQL> select * from test_temp2;

A
----------
1
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> select * from test_temp2;

no rows selected

**********************************************

SQL> create global temporary table test_temp (a int) ON COMMIT delete ROWS;~~~~~~~~~~~~事務期間存在事務結束後是刪除

Table created.

SQL> insert into test_temp values(1);

1 row created.

SQL> select * from test_temp;

A
----------
1

SQL> commit;

Commit complete.

SQL> select * from test_temp;

no rows selected

**********
SQL> insert into test_temp2 values(1);

1 row created.

SQL> select * from test_temp2;

A
----------
1
SQL> select * from test_temp2;~~~~~~~~另一個會話看不到,只針對當前SESSION

no rows selected

SQL> select blocks,tablespace_name from dba_tables where table_NAME='TEST_TEMP2
';

BLOCKS TABLESPACE_NAME
---------- ------------------------------
~~~~~~~~~~~~~~~~~~~~~

SQL> execute dbms_stats.gather_table_stats('SYS','TEST_TEMP2');

PL/SQL procedure successfully completed.

SQL> select blocks,tablespace_name from dba_tables where table_NAME='TEST_TEMP2
';

BLOCKS TABLESPACE_NAME
---------- ------------------------------
0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~不佔空間(另外臨時表對優化有一定影響後面會介紹成本計算,card)

************************************
基本的操作:
alter table XX allocate extent (size 500K DATAFILE '') 手動分配區分配HWM 後的,並指定datafile(不能跨表空間)
alter table NN move XX 移動TABLESPACE,並且可以收縮HWM,index失效 9I報error,10G無
alter table NN COMPRESS 壓縮表單寫篇講壓縮表

unused
將列標記為刪除,但不會實際刪除空間~

SQL> create table t2 (a int,b int);

Table created.

SQL> declare
2 begin
3 for i in 1..200000 loop
4 insert into t2 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------

A NUMBER(38)
B NUMBER(38)

SQL> alter table t2 set unused column b;
alter table t2 set unused column b
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

SQL>

SQL> conn xh/a831115
Connected.
SQL>
SQL> create table t3 (a int,b int);

Table created.

SQL> declare
2 begin
3 for i in 1..200000 loop
4 insert into t3 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>

SQL> alter table t3 set unused column b;

Table altered.

SQL>
SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------

A NUMBER(38)~~~~~~~~~~~~~~~~~看不見標記UNUSED 的column

SQL> desc dba_unused_col_tabs;
Name Null? Type
----------------------------------------- -------- ----------------------------

OWNER                                     NOT NULL VARCHAR2(30)
TABLE_NAME                                NOT NULL VARCHAR2(30)
COUNT                                              NUMBER

SQL> select * from dba_unused_col_tabs;~標記為unused column的資訊

OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
XH T3 1

SQL> alter table t3 drop unused columns checkpoint 2000;真正的delete 每刪除2000ROWS 檢查點寫會一次

Table altered.

SQL> select * from dba_unused_col_tabs;~

no rows selected~~~~~~~~~~~~~~~~~~~~~~~~`

SQL> select * from dba_partial_drop_tabs;~~~~~~`如果drop時候 abort了,可以查到沒drop完成的表

no rows selected

SQL> select distinct sid from v$mystat;

SID
----------
136

SQL> declare
2 begin
3 for i in 1..600000 loop
4 insert into t3 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> desc t2;
Name Null? Type
----------------------------------------- -------- ----------------------------

A NUMBER(38)

SQL> select * from dba_partial_drop_tabs;

no rows selected

SQL> select * from dba_unused_col_tabs;

no rows selected

SQL> alter table t3 drop unused columns checkpoint 2000;
alter table t3 drop unused columns checkpoint 2000
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel~~~~~~~~~~~~~~~~~~執行期間重啟~造成沒全部刪除完成

SQL> startup force;
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size                   788672 bytes
Variable Size             145487680 bytes
Database Buffers          167772160 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL> conn xh/a831115
Connected.
SQL> select * from dba_unused_col_tabs;

OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
XH T3 1

SQL> select * from dba_partial_drop_tabs;

OWNER TABLE_NAME
------------------------------ ------------------------------~~~~~~~~~~
XH T3
SQL> alter table t3 drop columns CONTINUE checkpoint 2000;

Table altered.
SQL> select * from dba_partial_drop_tabs;

no rows selected

SQL> select * from dba_unused_col_tabs;

no rows selected
~~~~~~~~~~~~~~~~~~~~~~~~~另外要是drop列有FOREIGN KEY 要帶
alter table XX set unused NN cascade constraints

SQL> create table t3 (a int, b int);

Table created.

SQL> alter table t3 set unused column a;

Table altered.

SQL> select column_name from dba_tab_columns where table_name='T3';

COLUMN_NAME
------------------------------
B

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
實驗關於建立表時候的一些storge 引數

先看
tablespace 級別

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> col file_name format a40;
SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ----------------------------------------
TEST                           D:\TEST.DBF
EXAMPLE                        E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EX
                               AMPLE01.DBF

USERS E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\US
ERS01.DBF

SYSAUX E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY
SAUX01.DBF

UNDOTBS1 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UN

TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
DOTBS01.DBF

SYSTEM E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY
STEM01.DBF

已選擇6行。

SQL> create tablespace test2 datafile 'd:\test2.dbf' size 10m extent management loc
al default storage(initial 1m next 2m pctincrease 0 minextents 10 maxextents 20);
create tablespace test2 datafile 'd:\test2.dbf' size 10m extent management local de
fault storage(initial 1m next 2m pctincrease 0 minextents 10 maxextents 20)
*
第 1 行出現錯誤:
ORA-25143: 預設儲存子句與分配策略不相容~~~~~~~~~~~~~~~當顯示寫上 extent management loc
al 的時候不允許有storage 引數設定

SQL> create tablespace test2 datafile 'd:\test2.dbf' size 10m default storage(initi
al 1m next 2m pctincrease 0 minextents 10 maxextents 20);~~~~~~~~~~~~~不顯示指定時候可以寫上 ,實際上本地管理後,這些引數已經廢棄

字典管理時才有效 :initial 初始區大小,next 下一個區大小,第3個區大小為上一個區大小+區大小*pctincrease(%) ,minextents 最小分配區,manextents最大分配區

表空間已建立。

SQL> select tablespace_name,initial_extent,next_extent,min_extents,pct_increase,ext
ent_management from dba_tablespaces where tablespace_name='TEST2';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE EXTENT_MAN
---------- -------------- ----------- ----------- ------------ ----------
TEST2 65536 1 LOCAL~~~~~~~~~~~~~~~~~~~~可以看出那些儲存引數都沒有使用

但是oracle 分析了這些引數

SQL> create tablespace test3 datafile 'd:\test3.dbf' size 10m default storage(initi
al 2m next 2m pctincrease 10 minextents 10 maxextents 20);

表空間已建立。

SQL> create tablespace test4 datafile 'd:\test4.dbf' size 10m default storage(initi
al 2m next 2m pctincrease 0 minextents 10 maxextents 20);

表空間已建立。

SQL> select tablespace_name,initial_extent,next_extent,min_extents,pct_increase,ext
ent_management,allocation_type from dba_tablespaces where tablespace_name='TEST4';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE EXTENT_MAN
---------- -------------- ----------- ----------- ------------ ----------
ALLOCATIO
---------
TEST4 2097152 2097152 1 0 LOCAL ~~~~~~~~~~~~~~~~~~~~~~初始2M 大小使用引數 oracle根據
UNIFORM initial 2m next 2m pctincrease 0 判斷區大小為一個統一分割槽

SQL> select tablespace_name,initial_extent,next_extent,min_extents,pct_increase,ext
ent_management,allocation_type from dba_tablespaces where tablespace_name='TEST3';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE EXTENT_MAN
---------- -------------- ----------- ----------- ------------ ----------~~~~~~~~~~~~oracle根據initial 2m next 2m pctincrease 10判斷為不一致所以oracle 使用
system(autoallocate)自動

ALLOCATIO
---------
TEST3 65536 1 LOCAL
SYSTEM

SQL> col segment_name format a30
SQL> select extent_id,bytes/1024/1024,blocks ,segment_name,tablespace_name from use
r_extents where segment_name='T1';

EXTENT_ID BYTES/1024/1024 BLOCKS SEGMENT_NAME TABLESPACE
---------- --------------- ---------- ------------------------------ ----------
0 2 256 T1 TEST4
SQL> alter table t1 allocate extent;~~~~~~~~~~分配區

表已更改。

SQL> select extent_id,bytes/1024/1024,blocks ,segment_name,tablespace_name from use
r_extents where segment_name='T1';

EXTENT_ID BYTES/1024/1024     BLOCKS SEGMENT_NAME                   TABLESPACE
---------- --------------- ---------- ------------------------------ ----------
         0               2        256 T1                             TEST4
         1               2        256 T1                             TEST4

SQL> alter tablespace test4 add datafile 'd:\test4_2.dbf' size 10m;

表空間已更改。

SQL> alter table t1 allocate extent(size 1024k datafile 'd:\test4_2.dbf');

表已更改。

SQL> select extent_id,bytes/1024/1024,blocks ,segment_name,tablespace_name from use
r_extents where segment_name='T1';

EXTENT_ID BYTES/1024/1024     BLOCKS SEGMENT_NAME                   TABLESPACE
---------- --------------- ---------- ------------------------------ ----------
         0               2        256 T1                             TEST4
         1               2        256 T1                             TEST4
         2               2        256 T1                             TEST4 ~************小於uniform.
SQL> alter table t1 allocate extent(size 3m);~~~~~~~~~~~~~~分一個大於uniform的

表已更改。

SQL> col tablespace_name format a20
SQL> select extent_id,bytes/1024/1024,blocks ,segment_name,tablespace_name from use
r_extents where segment_name='T1';

EXTENT_ID BYTES/1024/1024     BLOCKS SEGMENT_NAME         TABLESPACE_NAME
---------- --------------- ---------- -------------------- --------------------
         0               2        256 T1                   TEST4
         1               2        256 T1                   TEST4
         2               2        256 T1                   TEST4
         3               2        256 T1                   TEST4~1
         4               2        256 T1                   TEST4~~~2 ~~分了2個區~~~~~~~~~oracle 對size做了分析如果>uniform值(2M) 則分多個extent
                                                                                  如果小於則分一個區大小為uniform值

SQL> select extent_id,file_id,block_id from dba_extents where segment_name='T1';

EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          9          9
         1          9        265
         2         10          9~~~~~~~~~~~~~~~~~~~~~~~~~datafile引數生效了

SQL> select file_id,file_name from dba_data_files where tablespace_name='TEST4';

   FILE_ID FILE_NAME
---------- ----------------------------------------
         9 D:\TEST4.DBF
        10 D:\TEST4_2.DBF
SQL>
小結:oracle會對storage中設定引數分析下,如果判斷為一致分割槽就用其中的initial引數值,如果為不統一(每個extents大小不一樣)就用使用default設定(自動大小)

在自動確定區大小的時候,default 前16個 64KB(最小),後面的1M,如果blocksize為 16KB 那麼 autoallocate時候最小為1M (maxextents,minextents未起作用)

SQL> select tablespace_name,initial_extent,next_extent,min_extents,pct_increase,ext
ent_management,allocation_type,max_extents from dba_tablespaces where tablespace_na
me='SYSTEM';

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE EXTENT_MAN
---------- -------------- ----------- ----------- ------------ ----------
ALLOCATIO MAX_EXTENTS
--------- -----------
SYSTEM 65536 1 LOCAL
SYSTEM 2147483645

說下system tablespace ,system 為local時候就不允許建立字典tablespace了(10G R2 default為 local) 另外system 為local後不允許作為default temporary tablespace使

用了,必須建立temporary tablespace (字典管理表空間時候若沒有temporary tablespace 那麼將用system 作為temporary tablespace使用 alert.log中會有警告)

簡單說下字典管理表空間就是用資料字典表管理每個分配釋放時候都會在表裡記錄(主要維護這兩個表uet$,fet$)
當發生資料擴充套件的分配與回收時，Oracle會更新資料字典內相應的表。Oracle也會在更新資料字典表時儲存相應的回滾資訊（rollback information）。因為資料字典表與回滾段

（rollback segment）都是資料庫的一部分，她們使用的空間如同其他資料庫物件一樣也必須進行空間管理操作,
在使用資料字典管理的表空間時，分配或回收方案物件（schema object）的資料擴充套件可能會導致資料字典表（data dictionary table）或回滾段（rollback segment）中也產生

分配或回收空間的操作，即稱為遞迴的空間管理操作

本地就是用 bitmap管理記錄datafile內 extent的狀態,當一個extent分配或釋放時候改變bitmap中相應extent的狀態 ,bitmap中每一位帶了一個區
SQL> desc dbms_space_admin包可以對錶空間有很多管理 ,可以多看看

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613317/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 （storage儲存引數(1)）

相關文章

oracle實驗記錄（storage儲存引數(1)）