There is a logical storage unit in the oracle database that is known as tablespace. It is known as logical because it is not visible in the file system of the computer. It contains tables, indexes and many more. A database can have more than one tablespace. A tablespace contains at least on data file that is located in the file system of the oracle server. A tablespace builds a bridge between the file system and oracle database.
There are three types of tablespace in the oracle database that are as follow:
There are three types of tablespace in the oracle database that are as follow:
- Permanent tablespace
- Temporary tablespace
- Undo tablespace
Permanent Tablespace: It is used for the storing user defined data and objects like table, index etc permanently.
Temporary Tablespace: It is used to store user defined data and objects for a less time period.
Undo Tablespace: It is used to store before image data.
When a database is created in the oracle database then a system tablespace will be there. It is always ON until database is open. A database administrator can check the size of system tablespace by the follow script:
“@dbf_lst
Enter value for ts_name: system
old 8: where tablespace_name like upper('&ts_name'||'%')
new 8: where tablespace_name like upper('system'||'%')
continue> ”
How to Reduced the size of system tablespace: Sometimes size of system tablespace in oracle database increases randomly that affects the database performance. At this circumstances a database administrator can reduced the size of system tablespcae by export import method. To reduce the size of system tablespace export full database, recreate database and finally import full database.
How to repair tablespace: Corruption in the tablespace of oracle database can happen due o several reasons like virus attack, power failure, automatic system shutdown and so on. The best and effective solution to repair corrupted tablespace is restores from updated backup.
Restore tablespace without backup: In case, you don't have any backup of corrupted tablespace then you should take help from third party oracle database recovery software. Third party software performs very well in the most of tablespace corruption scenarios.
Temporary Tablespace: It is used to store user defined data and objects for a less time period.
Undo Tablespace: It is used to store before image data.
When a database is created in the oracle database then a system tablespace will be there. It is always ON until database is open. A database administrator can check the size of system tablespace by the follow script:
“@dbf_lst
Enter value for ts_name: system
old 8: where tablespace_name like upper('&ts_name'||'%')
new 8: where tablespace_name like upper('system'||'%')
continue> ”
How to Reduced the size of system tablespace: Sometimes size of system tablespace in oracle database increases randomly that affects the database performance. At this circumstances a database administrator can reduced the size of system tablespcae by export import method. To reduce the size of system tablespace export full database, recreate database and finally import full database.
How to repair tablespace: Corruption in the tablespace of oracle database can happen due o several reasons like virus attack, power failure, automatic system shutdown and so on. The best and effective solution to repair corrupted tablespace is restores from updated backup.
Restore tablespace without backup: In case, you don't have any backup of corrupted tablespace then you should take help from third party oracle database recovery software. Third party software performs very well in the most of tablespace corruption scenarios.
No comments:
Post a Comment