Logical El structure-tablespace

Logical El Logical Structure-Tablespace: A logical concept. It is the container of a data file and the logical organization of a data file in the database. Data files are stored on the operating system, and can also be raw devices (ASM ). Common tablespaces of www.2cto.com are: 1. The system tablespace is created when the system tablespace database is created. It must contain basic component information of the database, such as the base table. It mainly stores base tables and data dictionaries. In fact, when you modify the structure of tables, indexes, and other database objects, you are operating tables in the system tablespace. Such as the alter statement; 2. the introduced system is too large for the 10 Gb auxiliary tablespace of sysaux. some functional components (such as sqlplus tools) are removed and placed in sysaux, which belongs to the auxiliary tablespace of the system tablespace. The advantages of independence can reduce the load on the SYSTEM tablespace ., the problem of SYSTEM tablespace fragmentation caused by repeated creation of related objects and components can be avoided. View the component information and size in the sysaux tablespace Select OCCUPANT_NAME, SPACE_USAGE_KBYTES from V $ SYSAUX_OCCUPANTS; 3. undotbs rollback tablespace is used for transaction rollback, image Block 4 before saving change, temporary temp tablespace storage temporary data disk sorting or temporary table 5, sample data table space provided by users and exampleoracle 6. User data table space user-created 1. Table space type view the tablespace type idle> select TABLESPACE_NAME, CONTENTS from dba_tablespaces; include: Permanent tablespace temporary tablespace rollback tablespace 1. Permanent tablespace management is divided into: small file tablespace (one tablespace can store 1023 files each 4 MB * block_size) this type of basic applications are created by default. It is also a large file tablespace (one tablespace can store one file and manage four GB * block_s ). Ize) is rarely used, is not conducive to IO dispersion, is prone to competition, is easily limited by FS 1), view: select tablespace_name, file_id, file_name, ceil (bytes/1048567) | 'M' MB from dba_data_files; 2) create a small tablespace file System: create tablespace mytbs datafile '/PATH/file. dbf 'size 10 m; bare device: create tablespace rawtbs datafile'/dev/raw/raw1' size 4095 M 3) create bigfile tablespace bigtbs datafile '/u01/oracle/oradata/beijing/bigtbs01.dbf' size 5 M; select tablespace_na Me, bigfile from dba_tablespaces; 2. TEMPORARY tablespace stores the intermediate result of sorting or TEMPORARY table view: select tablespace_name, contents from dba_tablespaces where CONTENTS = 'temporary '; create TEMPORARY tablespace: create temporary tablespace NAME tempfile ''size 10 M; modify the user's temporary tablespace: select temporary_tablespace from dba_users where username = 'Scott '; alter user SCOTT temporary tablespace temp2; modify the default temporary tablespace of the database: alter database default temporary tablespace temp; 3. roll back the tablespace UNDO table empty Rollback segment is stored between them. 1. system rollback segment the system creates an object for the system tablespace to provide rollback 2. Non-system rollback segments users create objects for the user tablespace to provide rollback 3. Delayed rollback segments when the system creates an abnormal offline tablespace automatic creation we cannot manage the function of the rollback tablespace: 1. transaction rollback: uncommitted transactions can be rolled back. transaction recovery: When the database crashes, the disk is incorrectly restored to the top 3 of the transaction. read consistency: The queried records are occupied by transactions and are switched to the rollback segment to find and change the image 4. flash back data: It is idle to create a historical data rollback segment from the rollback segment. Data is generated only when a transaction is generated. Create an UNDO tablespace: create undo tablespace undo2 datafile '/u01/oracle/oradata/ora10g/undo02.dbf' size 10 M; management style: automatic or manual SQL> show parameter undo NAME TYPE VALUE types ----------- interval undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1 undo_management = AUTO rollback tablespace segment Segment segment management mode, automatic management: the Administrator only needs to prepare enough tablespace capacity. oracle will automatically manage the number of extended rollback segments. Only one UNDO tablespace undo_tablespace can be used only in automatic management mode. Specifies which UNDO tablespace to use. View the review segment: select segment_name, tablespace_name, status from dba_rollback_segs; the name of the rollback segment is automatically retrieved from the system rollback segment and cannot be managed by the DBA. the rollback data is not affected when the tablespace is switched: SQL> alter system set undo_tablespace = undo2; undo_retention = 900: the time when the old image remains in the rollback segment after submission. Unforced rollback holding time. (If the rollback space is insufficient, the old image will be overwritten.) Forced Persistence: however, it requires a large amount of space and should be used with caution. (Supported starting from 10 Gb) alter tablespace UNDOTABS1 retention guarantee; select tablespace_name, RETENTION from dba_tablespaces; manage UNDO manually: Change Method SYS @ beijing> show parameter rollback; name type value should ----------- begin fast_start_parallel_rollback string LOWrollback_segments stringtransactions_per_rollback_segment integer 5 by default, one rollback segment can be maintained for five transactions. Do not put too many transactions to generate rollback segment header contention because To find the data in the block location in the record segment in the field header, you must change the field header to manual SQL> alter system set undo_management = manual scope = spfile; SQL> startup force is not automatically managed because undo_management, so it takes effect only after a restart. View undo segments: select SEGMENT_NAME, TABLESPACE_NAME, STATUS from dba_rollback_segs; manual creation of rollback segments will automatically expand the size, so you do not need to specify create rollback segment rbs1 tablespace undotbs1; modify the STATUS: alter rollback segment rbs1 online; I will explain in detail about rollback segments. 5. view the status of the tablespace: select tablespace_name, status from dba_tablespaces; modify the status: alter tablespace mytbs read write/read only/offline; 1. Online (read/write) status 2, Readonly (read-only) status read-only tablespace can only select query and drop delete objects cannot be modified (INTSERT update delete truncate) system undotbs temp contains the tablespace of the active transaction cannot be read-only, the read-only tablespace cannot modify table data, but can delete the table structure because the table results are stored in the data dictionary and the data dictionary is in the system tablespace. 3. The offline (offline) state can only delete objects (drop) and cannot select dml truncate. The system tablespace + default temporary + tablespace containing active transactions cannot be offline, offline DBAs can move the data file path. Offline means to freeze the select name, checkpoint_change # open_scn, last_change # stop_scn from v $ datafile to lock the SCN at a certain time. Result: NAME OPEN_SCN STOP_SCN restart ----------/u01/oracle/oradata/ora10g/system01.dbf 475662/u01/oracle/oradata/ora10g/undotbs01.dbf 475662/u01/oracle/oradata/ora10g/sysaux01.dbf 475662/u01/oracle/oradata/ora10g/users01.dbf 476689 476689/u01/oracle/oradata/ora10g/limit 476652 6. View tablespace resizing tablespace size: select tablespace_name, sum (bytes/1048576) curr_MB, sum (MAXBYTES/1048576) MAX_MB from consumer group by TABLESPACE_NAME; Result: TABLESPACE_NAME CURR_MB MAX_MB ----------------- ------------ SYSAUX 240 limit 25 32767.9 844 USERS 25 32767.9844 SYSTEM 480 32767.9844 MYTBS 10 10 if the maximum size is null, it cannot increase. Query the remaining size of the current allocation: SQL> select TABLESPACE_NAME, sum (bytes/1048576) free_mb from dba_free_space group by TABLESPACE_NAME; Result: TABLESPACE_NAME FREE_MB --------------- ---------- UNDOTBS1 4.0625 SYSAUX. 875 USERS 24.5625 SYSTEM 6.875 MYTBS 10.875 resizing method: 1. Enable Automatic File growth of alter database datafile ''autoextend on next 10 M maxsize 4G; alter database datafile ''autoextend off; 2. add the new file alter tablespace uses add datafile ''size 5 M; alter tablespace uses drop datafile ''; /* 10 Gb to delete data. The first file cannot be deleted. */3. Modify the size of the existing file. alter database datafile ''resize 15 M; alter database datafile ''resize 10 M; Delete tablespaces and data files 1. Delete tablespaces and their CONTENTS together with drop tablespace mytbs including contents; 2. Delete the tablespace and its files together to delete drop tablespace mytbs including contents and datafiles. Note: when creating an index or primary key and unique constraints, you must store the specified index location in the production database. You must separate the table and index to store the created index and the constraint with the index, specify the index storage location SQL> create index t1_ind on t1 (empno) tablespace mytbs; SQL> ALTER TABLE T1 MODIFY (ENAME constraint T1_pk primary key using index tablespace mytbs );