`

(8) Managing Tablespaces and Data files

 
阅读更多

  • SQL> select * from v$tablespace;
    SQL> select * from v$datafile;
    SQL> select * from v$tempfile;
    SQL> select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;
    SQL > alter tablespace add datafile 'xxx' size 100m;
    (1) System tablespace
    (2) Non-System tablespace

  • Create Tablespace
    SQL> create tablespace kong
              datafile 'xxx' size 100m
              extent management dictionary
              default storage(
                initial 100k
                next 100k
                pctincrease 10)
               offline;

               SQL> create tablespace kong
                         datafile 'xxx' size 100m
                         extent management local uniform size 256K(autoallocate);
                         autoextend on next 5M maxsize 200m(unlimited);
               (1) Locally Managed tablespaces  (推荐使用)
                        Free extends recorded in bitmap
                        Reduce contention on data dictionary table
                        No undo generated when space allocation
                        No coalescing required
               (2) Dictionary-managed tablespaces
                       Free extends recorded in data dictionary tables
                       Each segment stored in the tablespace can have different storage clause
                       coalescing required

  • Undo Tablespace
    SQL> create undo tablespace undo1 datafile 'xxx' size 40m;
    SQL> show parameter undo_management;
  • Temporary Tablespace
    Used for sort operations
    Cannot contain any permanent objects
    SQL> create temporary tablespace kong4
              tempfile='xxx' size 5m extent mangement local;
    SQL> alter database default temporary tablespace=kong4;
  • Offline status
    SQL> alter tablespace userdata offline(online)
  • Tablespace Read Only
    SQL> alter tablespace userdata read only;
    SQL> alter tablespace userdata rename 'xxx';
  • Dropping Tablespace
    SQL> drop tablespace userdata including contents and datafiles;
  • Resizing Tablespace
    (1) automatically      (AUTOEXTEND ON)
    (2) Manually            SQL> alter database datafile 'xxx' resize 200m;
    (3) add file               SQL> alter tablespace kong add datafile 'xxx' size 100m;
  • Moving Data Files    SQL> alter tablespace userdata rename 'xxx' to 'xxx1'
分享到:
评论
发表评论

文章已被作者锁定,不允许评论。

相关推荐

Global site tag (gtag.js) - Google Analytics