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

fufuh2o發表於2009-08-26
Oracle


實驗表空間,表 的 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章