/ * Table space and data files * /
Advantages of multiple tablespaces: 1. Can separate data dictionaries to user data, avoiding the I / O conflicts generated by dictionary objects and user objects in the same data file. You can rebound data with user data Separate, avoiding permanent data loss due to hard disk damage 3. You can save the data files of the table space to different hard drives, and the average distribution of physical I / O operations 4. Can set a table space to offline Status or online status to back up and restore a part of the database 5. You can set a table space to a read-only state, thereby setting a portion of the database to read-only state 6. Can set a table for some species Space, such as temporary table space, etc. to optimize the use efficiency of tablespace 7. Better flexible to set table space limits for users
SYSTEM table space storage: 1. Database data dictionary 2. All PL / SQL programs source code and parsing code 3. Database object
The sum of the data files in all tablespaces cannot exceed the limitations of the maxdatafiles parameter specified when creating a database.
Local management table space: 1. Do not need to access the database during storage allocation, can improve the speed of the storage allocation operation 2. Avoid recursive phenomena generated in the storage management operation in the table space 3. Will not generate redo and revocation records 4. Simplify the management operation of DBA on table space 5. Reduce the dependence of the user's data dictionary
Table space of the dictionary management: Manage information in the tablespace is saved in the data dictionary, which generates a rolling and recording when the storage space is managed.
Local management method tablespace: Manage information in all storage space in the table space is saved in the bitmap of the header of the data file
Creating a table space in 9i is a local management method. 8i previous version, can only use the dictionary management method.
/ * Create a tablespace * / create table space (Dictionary management method) Create TableSpace DMUSERTBSDATAFILE 'i: /oacle/oradata/dmusertbs.dbf' size 50mextent management Dictionary;
Multiple data files (Dictionary management methods) Create TableSpace DMUSERTBSDATAFILE 'i: /oacle/oradata/dmusertbs01.dbf' size 50m, 'i: /oracle/oradata/dmusertbs02.dbf' size 50m, 'i: / oracle / ORADATA / DMUSERTBS03.DBF 'Size 50Mextent Management Dictionary;
Specifies the default storage parameters (dictionary management) create tablespace dmusertbsdatafile 'i: /oracle/oradata/dmusertbs.dbf' size 50Mdefault storage (initial 256K next 256K minextents 2 pctincrease 0 maxextents 4096) minimum extent 128Kloggingonlinepermanentextent management dictionary;
Create a table space (local management method)
1.Create TableSpace Lmusertbs DataFile 'i: /oracle/oradata/lmusertbs.dbf' Size 50m Extent Management Local Autoallocate; (Oracle assignment automatically, this is the default setting, in the Autoallocate mode, the smallest table space 64K)
2.Create TableSpace Lmusertbs DataFile 'i: /oracle/oradata/lmusertbs.dbf' size 50m Extent Management Local Uniform Size 512K; (All districts must have a uniform size, the biggest advantage of Uniformity is not in the table space Any storage debris will be generated. If there is no value to specify the size parameter after the UNIFORM keyword, the Size parameter will use 1MB as the default value) 3.Create TableSpace Lmusertbs DataFile 'i: /oracle/oradata/lmusertbs.dbf' size 50M EXTENT Management local uniform size 512k segment space management auto; (has a table space with automatic segment storage management mode)
Creating a temporary table space (Dictionary management method) A temporary table space can be shared by multiple database users, and Oracle only creates a temporal session for an instance, this temporal session is shared by all sort operations in the instance, but the temporary segment Each zone can only be used by one transaction. Temporary segment is created when the first sort operation is performed after the database starts.
Note: The temporary table space of the dictionary management can use ALTER TABLESPACE to make changes like normal tablespace. It is recommended to set the initial and next parameters to the same value, and should be the integer multiple of the initialization parameters sort_Area_size and db_block_size. pctincrease parameter should be set to 0create tablespace usertempdatafile 'i: /oracle/oradata/sort01.dbf' size 50Mextent management dictionarydefault storage (initial next 192K minextents 1 pctincrease 0 192K) temporary;
Create a temporary table space (local management method, the allocation management method of the area can only be uniform) in Oracle 9i, it is highly recommended to use the temporary table space of the local management mode instead of the dictionary management mode.
CREATE TEMPORARY TABLESPACE LMTEMP TEMPFILE 'I: /ORACLE/Ordata/lmtemp01.dbf' size 50m Extent Management Local Uniform Size 136K (must use the tempfile clause, TempFile is a temporary data file, the temporary data file cannot be used with ordinary data files ALTER DATABASE to create)
For temporary table spaces of local management, the ALTER TABLESPACE statement is just adding new temporary files for temporary tablespace. Add new temporary file ALTER TABLESPACE LMTEMP Add Tempfile 'i: /oracle/oradata/lmtemp02.dbf' size 50m (For tablespaces for local management, the role of the ALTER TABLESPACE statement is just to add new temporary files for temporary tablespace, and other modifications can be made to temporary tablespaces)
/ * Management Table Space * /
The management of the table space mainly includes several aspects of modifying the default storage parameters, manual consolidation debris, setting table space. Try to use fewer data files, because in some operating systems, you can open at the same time The number of system files will be restricted, thereby affecting the number of tablespaces at the same time.
Modify the default storage parameters of the tablespace ALTER TABLESPACE DUUSERTBSDEFAULT Storage (Next 128k maxExtents unlimited Pctincrease 20); (You cannot modify the initial and minelts parameters after the table space creation)
If the default storage parameters of the table space are not 0, Oracle will periodically launch the SMON background process, the memory fragments adjacent to the tablespace, if PctinCrease is 0, the SMON process does not perform a merge operation. If all of the districts in the table space With the same size, it is not necessary to manually combine storage debris. The tablespace of the local management method does not need to make a merge operation of the storage debris, because Oracle will automatically merge with bitmaps. Just need to manually merge in the tablespace of the dictionary management Storage fragmentation.
ALTER TABLESPACE DMUSERTBS COALESCE (if you use the Coalesce clause, you can't use any other clauses)
With the following query, you can see which storage fragments exists in the table space Users SQL> Select Block_ID, Bytes, Blocks 2 from dba_free_space 3 where tablespace_name = 'Users' 4 Order by block_id;
Change the availability of table space:
1.alter tableSpace User01 Offline Normal Oracle executes a checkpoint, writing a dirty cache block related to the table space in the SGA area into a data file, and then close the data file corresponding to the table space, the next time When the table space is restored to an online state, it is not necessary to perform database recovery.
2.alter TableSpace User01 Offline Temporary (Temporary Mode) Oracle does not check the status of each data file, even if some data files are not available, Oracle ignores these errors, and the next time the table space is restored to online When the state is required, database recovery may be required. If the data file is available, Oracle will write a dirty cache block associated with the tablespace into a data file.
3.alter tableSpace User01 Offline Immediate Oracle does not perform checkpoints, nor does it check the status of each data file, but directly set the data file that belongs to tablespaces into offline, the next time the table space is restored When it is online, database recovery is required. The database running in the NOARCHIVELOG mode is not allowed to switch to offline in this way.
Recovery table space is online status ALTER TABLESPACE USER01 online;
Set table space for read-only state alter tablespace user01 read only
Set table space for read-write status ALTER TABLESPACE User01 Read Write
Delete table space (excluding the corresponding data file) DROP TABLESPACE USERS INCLUDING Contents;
Delete tablespaces (including corresponding data files) DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES
Table Space Data Dictionary V $ TABLESPACE Control File Name and Number Information V $ DataFile Control File Name and Number Information V $ TEMPFILE All temporary data file Basic Information V $ Sort_SEGment instance created Sort zone information V $ sort_user Sort Unit User Usage Information DBA_TABLESPACES Database Name and Number Information DBA_SEGMENTS Table Space DBA_EXTENTS Table Space The information DBA_FREE_SPACE Table space DBA_DATA_FILES Data file That is, the information of the table space DBA_TEMP_FILES Temporary data file is the information of the table.
/ * Manage data files * / include add new data files to tablespace, change the size, name, or location of existing data files.
The initialization parameter db_files specifies the maximum number of data file information that can be saved in the SGA area, that is, the maximum number of data files that can be supported by an instance, which can modify it during the instance running process.
Setting data files for automatic growth
1. Create a table space LMUSERTBS to set the data file to automatic growth mode Create TableSpace Lmusertbs DataFile 'i: /oracle/oradata/lmusertbs01.dbf' size 50m Autoextend On Next 5m MaxSize 500M EXTENT Management local; (NEXT parameter specified each automatic The size of the growth, maxsize is the maximum size of the data file) 2. Add an automatic growth mode data file on the tablespace LMUSERTBS ALTER TABLESPACE LMUSERTBS ADD DATAFILE 'i: /oacle/oradata/lmusertbs02.dbf' size 50m Autoextend On Next 5M MaxSize 500M;
3. If the data file is already created, set it to automatic growth method ALTER DATABASE DATAFILE 'i: /oracle/oradata/dmusertbs01.dbf' autoextend on Next 5M MaxSize 500M;
4. Cancel the automatic growth mode of the existing data file ALTER DATABASE DATAFILE 'i: /oracle/oradata/dmusertbs01.dbf' AutoExtend OFF;
Handmade the size of the data file:
Increased data file DMUSERTBS01.DBF to 500MB
Alter Database DataFile 'i: /oracle/oradata/dmusertbs01.dbf' Resize 500M;
Change the availability of data files alone (database running in archiving mode): ALTER DATABASE DATAFILE 'i: /oracle/oradata/dmusertbs01.dbf' online; (online status) Alter Database DataFile 'i: /oracle/oradata/dmusertbs01.dbf 'offline; offline state)
Allowing the availability of data files (database running in non-archive mode): Alter Database DataFile '/u02/oracle/oradata/User01.dbf' offline DROP; use offline DROP to ensure that the data file is removed offline immediately
Change the name and location of the data file: a. Data file to be changed belong to the same table space
1. Set the tablespace containing the data file to offline State ALTER TABLESPACE USERS OFFLINE NORMAL
2. Rename or move data files in the operating system
3. Modify the name or location of the data file inside the database (with the ALTER TABLESPACE ... RENAME DATAFILE clause, the data file after the TO clause must exist) Change Name: Alter TableSpace Users Rename DataFile 'i: / Oracle / ORADATA / User01.dbf ',' i: /oacle/oradata/User02.dbf 'to' i: /oracle/oradata/Lmuser01.dbf ',' i: /oacle/oradata/lmuser02.dbf ';
Change location: ALTER TABLESPACE USERS RENAME DATAFILE 'I: /Oracle/Ordata/User01.dbf', 'i: /oracle/oradata/user02.dbf' to 'h: /oracle/oradata/user01.dbf', 'H: / Oracle/oradata/User02.dbf ';
4. Resend the table space to online status ALTER TABLESPACE USERS ONLINE5. Backup Control File
B. Data files to be changed to multiple tablespaces 1. Turn off the database 2. Rename or move data files in the operating system 3. Load the database, Startup Mount4. Modify the name or location of the data file inside the database (with alter Database ... rename file clause, the data file after the TO clause must exist) Alter Database Rename File 'i: /oracle/oradata/User01.dbf', 'i: /oracle/oradata/temp01.dbf' to 'i : /oracle/oradata/lmuser01.dbf ',' i: /oracle/oradata/lmtemp01.dbf ';
5.alter Database open; 6. Backup control file
Data File Data Dictionary DBA_DATA_FILES DBA_temp_files Database DBA_TEMP_FILES Database DBA_EXTENTS Description Information in the Table Space, including the data file of the area, the number of data files to the area DBA_FREE_SPACE table space
Delete data files for tablespaces (applicable: Don't carefully add a data file to a table space, or you have set the file size, so I want to delete it)
(Note: Oracle does not provide a way of deleting a table. To delete a data file like a view, the data file is part of the table space, so you can't "remove" table space. For the table space / data file, any offline, delete, First, a full backup of the database)
A: If the data file is the unique data file of the table space, you can simply delete the table space: Drop TableSpace
B: If your table space has multiple data files, you don't need the content in the table space, or you can easily re-generate the contents of the table space, you can use the Drop tablespace
C: If your table space has multiple data files, and you also need to retain content in other data files in the table space, you must first export all the contents in the table space. To determine those content in the table space, run: select owner, segment_name, segment_type from dba_segments where tablespace_name = '
EXPORT out of the content you want to keep. If Export ends, you can use Drop TableSpace TableSpace Including Contents. This permanently deletes the contents of the table space. Use the operating system command to physically delete the data file, recreate the table space according to the required data file, and put the data import to the table space.
note:
Alter Database DataFile
1. If you want to delete a data file because you assign an inappropriate file size, you can consider the resize command. 2. If you accidentally add a data file, this file has not allocated space, you can use
The ALTER DATABASE DATAFILE
-------------------------------------------------- -------------------------------------
For example, my Uses TableSpace has 2 data files: users01.dbf and users022.dbf, (database is non-archive mode) If I execute ALTER TABLESPACE OFFLINE; ALTER DATABASE DATAFILE USERS022.DBF offline DROP; if I am in operating system Delete Users02.dbf at level, then prompt the file next time, I estimate that this command is only modified at the ControlFile level, but Dictionary still records this file. How do I fully delete a data file in the table space?
Ask: in NOARCHIVELOG MODE, ALTER DATABASE DATAFILE ... OFFLINE DROP; you must add a DROP option, but the DROP option does not remove DataFile from Database. For DROP DATAFILE, you must delete the table space where DataFile is located. With the DROP option, DataFile retains in the data dictionary, the status is Recover or Offline. ALTER DATABASE DATAFILE ... OFFLINE DROP; Once Data File Offline, Oracle no longer accesses That DataFile, but its still is part of the table space. DataFile is marked as OFFLINE in ControlFile, when STARTUP, there is no comparison between SCN between ControlFile and DataFile (this also runs your STARTUP database, accompanied by a non-critical datafile loss). DataFile does not delete from ControlFile, giving you Recover the datafile. If you really want to delete user02.dbf, you can use Transport TableSpace feature, or export Object you want to save, re-establish a New TableSpace
