Oracle Table Space Management

   today is April 5, 2016, the previous study plan has not been implemented, found that they are still a weak willpower people! No matter how busy the future, all hope that they can put the study plan to persist, with empty mentality to re-learn oracle! refueling!    I'm going to go over it today. Oracle Tablespace Management, although I was developed by Oracle, I still have a heart that goes to DBA! Oracle disk space management in the highest logical layer is the table space, the next layer is a segment, a segment can only reside in a table space, the next layer of the segment is the disk area, a plurality of panel groups form a segment, the next layer is the data block, that is, the bottom of the table space: View information about the tablespace select  Tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;2:o Racle the default tablespace sysaux:system table space, which stores data objects other than the data dictionary, reducing the system space load Syytem: Storing data dictionaries, tables, views, Definition of Stored Procedure temp: Information about tables and indexes that are processed by SQL statements UNDOTBS1: The tablespace that holds the undo data, and typically the data object used by the application to view the type of data object stored in the table space and the Select segment _type,segment_name,owner from dba_segments from tablespace_name= ' USERS '; 3: Create tablespace When creating a tablespace, consider the following 1  create a small file table space, or a large file tablespace 2  is a partial-area management approach, or a traditional directory-area-management approach 3  manually manage tablespace   or auto-expand table Space 4   is a special tablespace for 0-period or undo-segment create tableaspace table_name datafile  ' \data\table_name.dbf '  size 10m (size)  extent management local (default is localization management)  uniform size 256k   Create large file table space Create bigfile tablespace name datafile  ' \data\name.dbf '  size 2G; Tablespace Maintenance 1: View the default tablespace select property_value, property_name from database_properties where  PROPERTY_NAME LIKE   '%tablespace '; 2: Change the default tablespace alter database default temprory  tablespace temp_name;alter database default  tablespace temp_name;3: Change the tablespace state alter tablespace tablespace_name read only/write (read-only \ Writable) Rename Alter tablespace  name1 rename to name2; Delete tablespace drop tablespace name1 including contents ( Delete data at the same time)  cascade constraints  (delete related constraint) 4: Maintain tablespace data File add data file Alter tablespace tablespace_name  add datafile  '/data/name2.dbf '  aize 10m autoextend on next 5m  maxsize unlimited; (automatic expansion, maximum unrestricted) delete files alter tablespace tablespace_name drop  datafile  '/data/name2.dbf '; 5: Management Undo Table Space Description:The Undo table space is used to hold the revocation information, and when the DML operation is performed,  oracle will write the data to the undo section and the undo segment resides in the Undo table space: Read-write, rollback, transaction recovery, flashback. Undo the initialization parameters of the Tablespace 1:undo_tablespace:  the undo tablespace to be used by the specified routines 2:undo_management:  the management mode for undo data   Auto is the automatic revocation management mode  manual for rollback segment Management undo_retention  data maximum retention time  900S  create undo Tablespace Create undo tablespace  undo_name datafile  '/data/name.dbf '  size 1G; add File Alter tablespace undo_name  add datafile  '/DATA/NAME2.DBF '; Toggle Undo Table Space Alter system set undo_tablespcae=undo_ name; Delete Drop tablespace undo_name;undo query operation (1) Undo space being used by the current routine show parameter undo_tablespace ;(2)   instance of all undo Tablespace select tablespace_name from dba_tablespace where contents= ' Undo ';(3)   View the undo Tablespace statistics Select to_char (begin_time, ' Hh24:mi:ss ')  as  start time, To_char (End_time, ' hh24 : Mi:ss ')  as  end time,undoblks as  number of fallback blocks  from v$undostat order by begin_ Time;6: Managing temporal tablespace queries for temporary tablespace information Select file_name,bytes,tablespace_name from dba_temp_files; Creating a temporary tablespace group Create temporary tablespace  tp1 tempfile  '/data/temp1.dbf '  size 10m tablespace group group1; create temporary tablespace tp2 tempfile  '/data/temp2.dbf '  size 10m  tablespace group group1; transfer to another group alter tablespace tp1 tablespace group  Group3; Assign a temporary table space to a specified user alter user name temporary tablespace group3; set the default temporary tablespace group alter  database orcl default temporary tablespace group3; Delete DROP TABLESPACE TP1  including contents and datafile;

Oracle Tablespace Management