Oracle Tablespace management Tablespace & amp; Data Files

Oracle Tablespace management Tablespace & Data Files [SQL] 1. Objectives Define the purpose of tablespaces and data files (the purpose of defining tablespaces and Data Files) create tablespaces www.2cto.com Manage tablespaces Create and manage tablespaces using Oracle Managed Files (OMF) Obtain tablespace information (retrieve tablespace information) ii. Tablespaces & Data Files Oracle stores data logically in tablespaces and physically in data files Tablespaces -- Can belong to only on database at a time -- Data files III. summary of Storage Hierarchy summary Storage Hierarchy logical Database-> Tablespace-> segment-> extent-> oracle data block Database-> Schema Physical Datafile-> OS block one Tablespace corresponds to multiple Data files one ORacle data block corresponds to multiple OS black is a multiple of OS blocks. 4. Types of Tablespaces 1. SYSTEM tablespace -- Create with the database -- Contains the data dicationary -- Contains the SYSTEM undo segment 2. Non-System tablespace -- Separate segments -- Eases space administration simplify space management -- Controls amount of space allocated to a user www.2cto.com, undo, temporary) 5. View tablespace Information 1. View tablespace select * from v $ tablespace; 2. View tablespace containing data select file_name, tablespace_name from dba_data_files; 6. Create Tablespaces create tablespace paul datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/paul01.dbf' size 20 m; VII. Two Methods: dictionary-mangage tablespace central centralized a locally managed standalone (9 adopts this method, but it is compatible with the previous method) after 9i, if the system tablespace is locally managed, the newly created method converts the Dictionary-mangage Method to the locally managed DBMS_SPACE_ADMIN.TABLESPCAE_MIGRATE_TO_LOCAL ('system') (seven steps in total) 8. Undo Tablespace (used for rollback) 1. Used to store undo segments 2. Cannot contain any other objects 3. Extents are locally managed 4. Can only use the DataFile and Extent management clses www.2cto.com create undo tablespace undo1 datafile '/u01 /app/oracle/oradata/undo01.dbf 'size 40 M; 9. Temporary Tablespace (preferably specified separately) 1. used for sort operation 2. can be shared by multiple user 3. cannot contain any permanent objects (cannot contain permanent information) 4. Locally managed extents recommended (the locally method is recommended) 5. Create temporary tablespace temp tempfile '/u01/app/oracle/oradata/temp01.dbf' size 20 M extent management local uniform size 4 M 1. Specify Temporary tablespace 2 when creating a database, create temporary tablespace, modify ① and create temporary tablespace mytemp1 tempfile '/u01/app/oracle/product/10.2.0/oradata/oamis/mytemp01.dbf' size 100 M extent management local; ② modify alter database default temporary tablespace mytemp1; temporary Tablespace Restriction 1. Dropped until after a new default is made available cannot be deleted 2. Taken offline cannot be offline 3. Altered to a permanent tablespace cannot be changed to permanent Tablespace 10. Read-only Tablespace 1. Causes a checkpoint (resulting in a checkpoint) 2. Data available only for read operation 3. Object can be dropped from tablespace (System Tablespace cannot be read-only, but only drop the Data dictionary when dropping) 4. Test the preceding three points: www.2cto.com ①. create tablespace shanxi datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi. dbf 'size 20 m extent management local uniform size 128 k; ② create a user. The default tablespace is shanxi create user fc identified by fc default tablespace shanxi ③. grant permissions to connect, resource to fc; ④. log on to conn fc/fc @ oamis; ⑤ create a table and add a record to submit create table t (id integer, name char (10 )); insert into t values (1, 'cloud'); commit; 6. Modify the tablespace read-only alter tablespace shanxi read only; 7. The test results cannot be added, deleted, or modified. Only drop Tablespace can be restored. The Tablespace write alter table shanxi read write can be restored. The eleven and Taking a Tablespace Offline table space statuses (read, write, offline, online) 1. Not avaliable for data access 2. Tablespace that cannot be taken offline ①. system tablespace www.2cto.com ②. Tablespaces whit active undo segment () ③ Default temporary tablespace (temporary tablespace is not the Default offline) 2. Test the table space offline ①. create table t2 (id integer, name char (10) when creating a table )); insert into t2 values (1, 'aaa'); insert into t2 values (2, 'bbb '); commit; insert into t2 values (3, 'ccc '); ② offline table store shanxi (sys user) alter tablespace shanxi offline; (tablespace offline) alter tablespace shanxi online cannot be queried at this time; (tablespace online) at this time, you can set the settings in 12 and Changing Storage Settingg (Changing Storage settings). 1. You can set the settings in three levels (Database-level, tablespace-level, and segament-level) 2. Storage setting for locally managed tablespaces cannot be altered; 13. Resizing a TableSpace (adjust the tablespace size) A TableSpace can be resized: 1. Changing the size of a data file (Changing the size of the data file) ①. Automatically using autoextend (automatic change, one pace, one limit) three methods: create database, create tablespace, alter tablespace... add datafile Example: Create Tablespace user_data datafile = '/u01/oradata/userdata01.dbf' size = 200 M autoextend on next 10 m maxsize 500 M; (can be applied to create database or create tablespace) modify the tablespace (add one) at www.2cto.com) alter tablespace shanxi add datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi2.dbf' size 20 m autoextend on next 10 M maxsize 100 M; alter database datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi. dbf 'autoextend on next 10 m maxsize 100 M; query whether tablespace automatically grows select file_name, tablespace_name, autoextensible from dba_data_files; ②, Manually using Alter Database (manual change) (from small to large, and from large to small, it depends on the actual size of the database file) alter database datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi. dbf'resize 20 M; 2. Add a data file using Alter Tablespace (Add a new one) (this method is good, for example, one 100G and 10 10g, 10 of them will be concurrent and fast) alter tablespace shanxi add datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf' size 5 M; 3. query table space usage (this query takes some time) mainly including DBA_DATA_FILES and dba_free_space select f. tablespace_name,. total, u. used, f. free, round (u. used/. total) * 100) "% used", round (f. free/. total) * 100) "% Free" from (select tablespace_name, sum (bytes/(1024*1024) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round (sum (bytes/(1024*1024) used from dba_extents group by tablespace_name) u, (select tablespace_name, round (sum (bytes/(1024*1024 ))) free from dba_free_space group by tablespace_name) f WHERE. tablespace_name = f. tablespace_name and. tablespace_name = u. tablespace_name; www.2cto.com temporary tablespace in DBA_TEMP_FILES find fourteen, Methods for moving data file (move data file) 1. Alter Tablespace premise ①, Tablespace must be offline ②, Targer data file must exist step ①, alter tablespace shanxi offline; ② bytes in dba_data_files is empty offline ③ Change shanxi3.dbf to the upper-level alter tablespace shanxi rename datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf 'to'/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf '; ④ The path changed through dba_data_files query 2. Alter Database prerequisites: ①. database must be mounted ②. targer data file must exist; Step ①. Shut down the Database shutdown immediate; ② move the data file or copy the mv shanxi3.dbf oamis/③. Open the database to the mount status and start up mount; 4. Execute the following command alter database rename file '/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf' to '/u01/app/oracle/product/10.2.0/oradata/ oamis/shanxi3.dbf '; ⑤ modify the database to the open state: alter database open; 6. query the dba_data_files file. See path 15. Dropping Tablespace You cannot drop a tablespace if it: -Is the System tablespace-Has active segment including contents drops the segments including contexts and datafile deletes data files cascade constraints drops all referential integrity constraints command: drop tablespace userdata including contents and datafiles www.2cto.com Delete the tablespace shanxi and the file drop tablespace shanxi including contents and datafiles; sixteen, Get Tablespace information 1, Tablespace information -- dba_tablespaces -- v $ tablespace 2, Data file information dba_data_files v $ datafile 3, Temp file information dba_temp_files v $ tempfile author liyangfd