Oracle資料庫中資料表空間的基本管理操作小結_oracle

DB儲存層次結構

（畫了個草圖，將就看一下...XD）

管理資料表空間

        -system 存放資料字典資訊，必須的，建立資料庫時第一個建立

        -sysaux 10g新，必須的，輔助分擔system的負荷，系統管理如oem等三方工具等

        -undo 儲存復原段資訊，提供交易回復功能

        -temp 存放使用者排序的臨時資料

        -index 存放使用者表上的索引資訊

        -other 不同使用者表資料

擷取資料表空間和資料檔案資訊

        資料表空間資訊：DBA_TABLESPACES        V$TABLESPACE
        資料檔案資訊：DBA_DATA_FILES        V$DATAFILE
        臨時資料檔案資訊： DBA_TEMP_FILE        V$TEMPFILE

建立資料表空間

    create [smallfile|bigfile] tablespace <identName> datafile '<path&name>' [extent management local uniform] size <n>k|m|g|t

        通常不需要
        確定使用bigfile還是smallfile，T層級以上的一般使用bigfile
        smallfile|bigfile 不加使用預設值，

select property_name,property_value from database_properties where property_name like '%TBS%';

可以更改預設值，alter database set default bigfile tablespace.'
        需要有dba_role,sysdba或者sysoper的相關許可權
        extent management local uniform本地管理資料表空間（LMT），使用bitmap描述空間分布情況，減少資料字典中的競爭，不需要為每個段單獨設定儲存參數。同版本下字典管理資料表空間（DMT）轉為LMT：

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

不同版本下DMT轉為LMT，在源機上exp匯出資料表空間，在目標機上建立表結構，執行imp加ignore=y參數忽略表結構匯入資料

        一個tablespace下可以容納1024個datafile

資料表空間狀態

    select tablespace_name,file#,v.status,v.enabled from dba_files d,v$datafile v where d.file_id=v.file#;    alter tablespace <tablespace_name> read only|read write|offline|online;

        tablespace                online                        read only
        system                        必須online                必須read write
        sysaux                        可以offline                必須read write
        undo                        必須online                必須read write

        唯讀資料表空間上的對象可以被刪除

        alter tablespace <old> rename to <new>;資料表空間重新命名，system/sysaux/database_properties中定義的預設使用者資料表空間/預設暫存資料表空間/undo中undo_tablespace定義的不能重新命名

資料表空間的大小

    alter database set default smallfile|bigfile tablespace;

        一個大表資料檔案可以包括4G個os blocks，小表資料檔案4m個os blocks

        自動擴張：

alter tablespace datafile '<path & name>'|file# autoextend on|off [next <size>|maxsize <size‪>];         select tablespace_name, file_name, autoextensible from dba_data_files;

        手動擴張：

alter tablespace datafile '<path & name>'|file# resize <size>;         alter tablespace datas add datafile '<path & name>' size <size>;

（增加資料表空間中的資料檔案)

資料表空間檔案的重新命名（歸檔模式）

        將需要重新命名資料檔案的資料表空間離線--->作業系統級移動資料檔案或改名--->執行

alter tablespace rename file '<old>' to '<new>';

--->將資料表空間Online

        mount階段--->作業系統級移動資料檔案或改名--->執行

alter tablespace rename file '<old>' to '<new>';--->startup

刪除資料表空間

        不能刪的：系統資料表空間/使用中的undo資料表空間/預設臨時和永久資料表空間

    drop tablespace <name> [including contents [and datafiles]];

system資料表空間

資料字典、定義資訊

管理：
        空間管理
                system中一般放單個資料檔案，設定自動擴充，或設定為bigfile

        備份
                必須在歸檔模式下open狀態備份，熱備(alter tablespace system begin backup;ho cp ?/system01.dbf  ?/bak/;alter tablespace system end backup;)，或者用rman(rman target /;backup tablespace system;)備份

        還原
歸檔有備份情況下，日誌完整，cp ?/bak/system01.dbf ?/;recover database;或者用rman：restore tablespace system;
歸檔有備份情況下，日誌不完整，不能保證資料不丟失

        不能離線不能唯讀不能重新命名

system auxiliary(sysaux)資料表空間

        10g新，輔助system，存放第三方工具

        可以離線不能唯讀不能刪除(startup migrate|downgrade時drop tablespace sysaux有時可以)不能重新命名

        查看

        select occupant_name,schema_name from v$sysaux_occupants;

        備份
                在歸檔模式下open狀態備份，熱備

(alter tablespace sysaux begin backup;ho cp ?/sysaux01.dbf ?/bak/;alter tablespace sysaux end backup;)

，或者用

rman(rman target /;backup tablespace sysaux;)

備份

        還原
                歸檔有備份情況下，日誌完整，cp ?/bak/sysaux01.dbf ?/;recover database;或者用rman：restore tablespace sysaux;
                沒有備份，offline狀態下，將資料移轉走，重建庫，再把資料移轉回來

UNDO資料表空間

作用
        事務的回退
                savepoint <n>; rollback to <n>;
        事務的提交
                DML|DDL                事務結束：人為commit DML提交，DDL自動認可
        閃回資料
                               
狀態查詢
        v$rollstat(所有的復原段)  v$rollname(當前正在使用的復原段)  dba_Segments  dba_rollback_Segs
        v$transaction

查看當前使用的undo資料表空間

    show parameter undo_tablespace;

建立

    create smallfile|bigfile undo tablespace <name> datafile <path&name> size <size>;

刪除

    drop tablespace undo including contents and datafiles;

 (不能刪除當前正在使用的資料表空間)

更改

        將預設的undo資料表空間重新命名以後，重啟資料庫之後Oracle會自動將參數修改為新的名字

估算undo資料表空間大小：

undo space=(undo_retention*undo_blocks_per_second * db_block_size) + db_block_size    show parameter undo_retention;--->undo_retention    show parameter db_block_size;--->db_block_size    select sum(undoblks)/sum((end_time - begin_time)*10800) from v$undostat;--->undo_blocks_per_second 

查詢當前undo資料表空間的大小：

max(block_id) * db_block_size    show parameter db_block_size;--->db_block_size

（單位k）

    select max(block_id) from dba_extents where file_id=2;--->block_id

備份（歸檔模式）
有備份：rman或者熱備
無備份：系統中是否有其他的undo，如果有，把損壞的離線，用其他的替換，然後啟動，刪除損壞的資料表空間並建立新的。如果沒有，設定參數undo_management=manual,使用隱藏參數讓undo離線(alter system set "_offline_rollback_segments"=true scope=spfile)，然後啟動，參看dba_rollback_Segs中搞的status中，損壞的資料表空間的段是否有recover，如果沒有，刪除損壞的資料表空間並建立新的；如果有，使用_corrupted_rollback_segments標記該段，然後刪除損壞的資料表空間並建立新的。最後還原上面的參數。