oracle 表空間 資料檔案 筆記

mahe0207發表於2014-02-13
Oracle筆記
資料檔案命令
       Enabling and Disabling Automatic Extension for a Datafile：
            ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M  AUTOEXTEND ON NEXT 512K MAXSIZE 250M; 
            ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;

資料檔案調整大小： 
       ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;

歸檔模式下資料檔案離線： 
       ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
       離線後上線：
       recover datafile '/u02/oracle/rbdb1/stuff01.dbf';
       ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
       注：offline 和 offline drop 沒有什麼區別。 因為offline 之後多需要進行recover 才可以online。

非歸檔模式下資料檔案離線： 
       ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;
       注：離線後的資料檔案還殘留在資料字典中，須執行以下命令的其中一種，進行刪除
       1.ALTER TABLESPACE ... DROP DATAFILE 
       2.A DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES statement

離線後若online，需要recover(恢復的前提是online redo裡的資料還沒有被覆蓋掉)
       1.recover datafile '/u02/oracle/rbdb1/users03.dbf';
       2.alter database datafile '/u02/oracle/rbdb1/users03.dbf' online;

資料檔案重新命名步驟：
       1.ALTER TABLESPACE users OFFLINE NORMAL;
       2.作業系統內資料檔案重新命名
       3.ALTER TABLESPACE users
          RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf','/u02/oracle/rbdb1/user2.dbf'
          TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';

資料檔案調整位置：
       1.ALTER TABLESPACE users OFFLINE NORMAL;
       2.移動位置
       3.ALTER TABLESPACE users
          RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf','/u02/oracle/rbdb1/users02.dbf'
          TO '/u03/oracle/rbdb1/users01.dbf','/u04/oracle/rbdb1/users02.dbf';
       4.Back up the database

刪除資料檔案的注意事項：
       1.資料庫必須是開啟的
       2.資料檔案不為空，不能刪除
       3.如果必須刪非空的除資料檔案，則必須刪除其所在的表空間連同資料檔案
       4.不能刪除表空間的第一個資料檔案
       5.包含bigfile的表空間不能刪除資料檔案
       6.不能刪除只讀表空間的資料檔案
       7.不能刪除系統表空間的資料檔案
       8.本地管理的表空間的離線資料檔案不能刪除

       ORA-03263: cannot drop the first file of tablespace string
       Cause: Trying to drop the first datafile with which ts is created
       Action: Cannot drop the first datafile with which ts is created
       注：表空間建立時的第一個資料檔案不能刪除的。

資料檔案複製
        1.CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';
           CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';
        2.GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;
           GRANT READ ON DIRECTORY source_dir TO strmadmin;
           GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;
        3.CONNECT strmadmin/strmadminpw
        4.BEGIN
            DBMS_FILE_TRANSFER.COPY_FILE(
                source_directory_object       =>  'SOURCE_DIR',
                source_file_name              =>  'db1.dat',
                destination_directory_object  =>  'DEST_DIR',
                destination_file_name         =>  'db1_copy.dat');
           END;
            /

關於資料檔案大小：
        Oracle最大支援block為32k。每個datafile的最大容量為(2^22-1)個block(即4194303個block)。因為Oracle的內部ROWID使用22位2進位制數來儲存不同的block號
        單個datafile最大容量：(2^22-1)*8K=32G，(2^22-1)*16K=64G，(2^22-1)*32K=128G
        bigfile tablespace裡Oracle使用32位來儲存block號，即比之前增大了1024倍，其最大檔案大小從128G增加到128T。
    
關於系統中單個檔案的限制   
        windows中單個檔案最大限制：FAT12(8M)  FAT16(2G)  FAT32(4G)  NTFS(64GB)  NTFS5.0(2TB)  



表空間命令
        本地管理表空間（LMT）：
        字典管理表空間（DMT）：10G裡不能建立字典管理表空間的，只能匯入老版本的。

臨時表空間：
        一般用來排序和建立索引時使用；
        臨時表空間裡不存放實際的資料，不需要恢復、備份、記錄日誌；
        臨時表空間只能使是讀寫模式，而且只能為手動管理段空間模式；

        CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
        CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
        CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT AUTO;
        ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

Bigfile Tablespaces
        注意：Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
        Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
        Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.

        Creating：CREATE BIGFILE TABLESPACE bigtbs DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G...
        RESIZE：ALTER TABLESPACE bigtbs RESIZE 80G;
        AUTOEXTEND：ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

        提高分割槽表效能的一個方法：分割槽表每個分割槽放在單獨的表空間裡，這些表空間使用同一個bigfile資料檔案，這樣在查詢跨分割槽資料或較
多資料時，不至於跨越多個資料檔案，在檢索速度上，多表空間單個資料檔案（bigfile）的分割槽表>多表空間多個資料檔案（smallfile）分割槽
表>單表空間分割槽表。

Temporary Tablespaces
        相關檢視：V$TEMPFILE  DBA_TEMP_FILES   V$DATAFILE  DBA_DATA_FILES views
        Creating：CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE 
                        EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
        Resizing：ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
        Deleting：ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

        注意：臨時表空間不能設定為AUTOALLOCATE.
                  不能把本地管理的表空間變成本地管理的臨時表空間，只能使用 CREATE TEMPORARY TABLESPACE 建立一個本地管理的臨時表空間
        ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
        ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
        ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

        不能使臨時表空間離線，可以使它的檔案離線(V$TEMPFILE)

臨時表空間組
        Creating：CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M TABLESPACE GROUP group1;
                        ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

        ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

離線表空間選項
        NORMAL  表空間中的資料檔案都不能離線。離線時為所有資料檔案建立檢查點，online時不需要recover。
        TEMPORARY  為online的資料檔案建立檢查點，online時只需為以前離線的資料檔案做recover。
        IMMEDIATE  必須執行在歸檔模式，所有資料檔案都不做檢查點，online時需要為所有資料檔案做recover。

只讀表空間
        可以刪除表，索引等items，不能增加修改object，可以add或modify表或欄位的描述資訊
        不能用於活躍的undo表空間和系統表空間
        ALTER TABLESPACE flights READ ONLY;
        ALTER TABLESPACE flights READ WRITE;

        Renaming Tablespaces：ALTER TABLESPACE users RENAME TO usersts;
        Dropping Tablespaces：DROP TABLESPACE users INCLUDING CONTENTS;
        DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

字典管理表空間遷移到本地管理表空間
        EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');
SYSTEM表空間改成本地管理
        EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

表空間傳輸
        SELECT * FROM V$TRANSPORTABLE_PLATFORM;   --檢視可轉換的平臺
        使用限制：1.字符集一致
                          2.Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities,                 not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).


       查詢表空間是否包含XMLTypes：
            select distinct p.tablespace_name
            from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
            where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name and x.owner=u.username

        3.源目標平臺最低支援：1)same platform  source(8.0) target(8.0)
                                              2)different database block size  source(9.0) target(9.0)
                                              3)different platforms   source(10.0) target(10.0)

表空間管理：本地管理  字典管理
段管理：自動 點陣圖
區：UNIFORM   AUTOALLOCATE
塊：

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

相關文章