Oracle 10g bigfile tablespace, smallfile table space

Oracle 10g bigfile Table Space Introduction  01. A Bigfile  table space contains a very large data file 02.  Smallfile tablespace and Bigfile table space can coexist in the same database 1. Create a bigfile tablespace     sql> create bigfile  tablespace  big01           datafile   '/ORACLE/ORADATA/ORCL/BIG01.DBF '  size 50M; tablespace created.1.2  View the database all Tablespace bigfile properties, BIG01 to Bigfile table space Sql> select tablespace_name,  BIGFILE from DBA_TABLESPACES; tablespace_name                 bigfile------------------------------ --------system                           noundotbs1                        nosysaux                          NOTEMP                            NOUSERS                            NOTEST                            nobig01                            YES  1.3  table Space test is a smallfile table space, now do a test resize  test Table Space Sql> alter tablespace test  resize 20m;alter tablespace  test  resize&nbsP;20m*error at line 1:ora-32773: operation not supported for smallfile  tablespace test This shows that small does not support table space resize, of course, we can through the alter database reszie datafile  To implement a resize2.bigfile tablespace file for a data file in the Smallfile tablespace   bigfile table space can contain only one file, it could be very large, we cannot add data files to the Bigfile table space        SQL> alter tablespace big01 add  2   datafile  '/oracle/oradata/orcl/big02.dbf '  size 10m;alter tablespace big01 add* Error at line 1:ora-32771: cannot add file to bigfile tablespace3 . Bigfile table Space addressing    bigfile the data file size of the tablespace is much larger than the Smallfile tablespace, benefiting from oracle 10g new addressing scheme      An rowid addressable object stored in a traditional smallfile tablespace uses 12 of these bytes        example:  rowid addressing          .  relative file# 3 bytes,  block# requires 6 bytes of the same rowid addressing in the Bigfile table space only requires 9 bytes, 9 bytes of storage block# in a unique file                     The     bigfile  table space has only one data file, so there is no need for another 3 bytes of related file#    new addressing scheme            allows up to 4G blocks of data in a single data file,          Data file size blocksize 2k data file maximum support for 8tb,blocksize 32k data files up to 128tb      Advantages of the  4.bigfile table space    A table space with only one data file is easier to manage, so the only table space becomes the snap-in           Modifying the expansion of table spaces  SQL> alter tablespace big01 autoextend on; Tablespace altered. sql> alter tablespace big01 autoextend off; tablespace altered.     Modify the Bigfile table space size online SQL> ALTER TABLESPACE BIG01  resize 2M; Tablespace altered.smallfile table space is less flexible than bigfile table space Sql> select tablespace_name, bigfile  from dba_tablespaces where tablespace_name= ' TEST '; tablespace_name                 big------------------------------ ---test                             Nosql> alter tablespace test  resize 20m;alter tablespace test   resize 20m*error at line 1:ora-32773: operation not supported The  for smallfile tablespace test5.bigfile table space supports the following storage management methods         --> ASM  (automatic storage management)        - -> a logical volume manager supporting striping/raid       --> dynamically extensible logical volumes     & nbsp;--> oracle managed files  (OMF)   6.  Modify the Bigfile type of database creation table Space  SQL>  select * from database_properties            where property_name= ' Default_tbs_type '; The default way to create table spaces for the current database is Smallfile tablespace property_name                   property_value                             description------------------------------ --------- ------------------------------- ------------------------------default_tbs_type                SMALLFILE                             &nbsP;    default tablespace type changing the database creation tablespace to Bigfile table space sql> alter  Database set default bigfile tablespace;database altered. sql> create tablespace big02 datafile  '/oracle/oradata/orcl/big02.dbf '  size  10M; Tablespace created. sql> select tablespace_name, bigfile from dba_tablespaces; tablespace_name                 big------------------------------ ---system                          noundotbs1                         NOSYSAUX                          notemp                            NOUSERS                            NOTEST                            NOBIG01                            YESBIG02                           YES7.  The Bigfile table space has a performance advantage over traditional smallfile table space launches, checkpoints, and DBWR operations. View the file number and relative file number of the database file sql> select file_name,  file_id, relative_fno from dba_data_files;    file_name                                                          file_id relative_ FNO------------------------------------------------------------ ---------- ------------/oracle/ oradata/orcl/big01.dbf                                          8         1024/ oracle/oradata/orcl/big02.dbf                                          9         1024/oracle/oradata/orcl/users01.dbf                                        4             4/oracle/oradata/orcl/sysaux01.dbf                                        3            3/oracle/oradata/orcl/ undotbs01.dbf                                     2             2/oracle/oradata/orcl/system01.dbf                                       1             1/oracle/oradata/orcl/system02.dbf                                       6             6/oracle/oradata/orcl/test02.dbf                                         7             7/oracle/oradata/orcl/test.dbf                                           5             5 Note:bigfile  table space has only one data file, Relative file number is 10248.bigfile  tablespace, smallfile table Space Sql> select tablespace_name,bigfile from dba_ Tablespaces where tablespace_name= ' TEST '; tablespace_name                 big------------------------------ ---test                            no1-  gets the information relative to the file number in the Smallfile table space from the ROWID of the table    sql> create table st0   (C number)  tablespace test;     table created. sql> insert into st0 values  (1);     1 row  CREATED.     SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO (rowID, ' SMALLFILE ') )  from st0;dbms_rowid. ROWID_RELATIVE_FNO (ROWID, ' smallfile ')------------------------------------------------                                                  5                              & NBsp;                                                                   2-  get information &nbsp of relative file number from rowID of table in Smallfile table space;: sql> create table bt01  (C number)  tablespace big01;      table created.     sql> insert into  bt01  values  (1);     1 row created. sql> select  DBMS_ROWID.ROWID_RELATIVE_FNO (rowID, ' bigfile ')  from bt01;     dbms_rowid. ROWID_RELATIVE_FNO (ROWID, ' bigfile ')      --------------------------------------------- -                                                 10249.bigfile tablespace support alter table move  support smallfile  Total table space object move to bigfile  tablespace Sql> select name, bigfile, table_name from dba_ tables t, v$tablespace v              where table_name= ' T11 '  and v.name=t.tablespace_name;NAME                             big table_name------------------------------ --- ---------------- --------------users                           no  t11 sql> alter table scott.emp  move tablespace big01; Table altered.  sql> select name, bigfile, table_name from dba _tables t, v$tablespace v  2             where table_name= ' T11 '  and v.name=t.tablespace_name;NAME                             big table_name------------------------------ --- ---------- --------------------big01                           YES T11  Feature Restrictions------------   new features only support       --> locally managed tablespaces Local Management table Space      --> with ASSM  (automatic segment space  Management)  ASSM Manage table spaces

This article is from the "O Record" blog, so be sure to keep this source http://evils798.blog.51cto.com/8983296/1420920