Step 6: Understand the Oracle tablespace status

I like Oracle tablespace very much. I also like to sum up the experience and lessons on Oracle tablespace status in my work. Let's talk about this in detail.

1. query the Oracle tablespace status

 
 

  
  
select tablespace_name,status from dba_tablespaces; 
 
 

2. Change the Oracle tablespace status

 
 

  
  
alter tablespace book offline/online/read only/read write; 
 
 

Offline/online/read-only/read/write

3. Move data files

 
 

  
  
select tablespace_name,file_name from dba_data_files;  
  
  
alter tablespace book offline; 
 
 

Move the file to the new directory.

 
 

  
  
alter tablespace book rename datafile '/u01/oradata/oracle8i/sales01.dbf' to '/u02/oradata/oracle8i/sales01.dbf'; 
 
 

4. Modify the online/offline attributes of data files

In archivelog Mode

 
 

  
  
alter database datafile '/u01/oradata/oracle8i/sales01.dbf' offline; 
 
 

Noarchivelog Mode

 
 

  
  
alter database datafile '/u01/oradata/oracle8i/sales01.dbf' offline drop; 
 
 

5. Convert the data dictionary tablespace to the local management tablespace

 
 

  
  
select tablespace_name,extent_management,allocation_type from dba_tablespace;  
  
  
exec dbms_space_admin.tablespace_migrate_to_local('book') 
 
 

System tablespace and temporary tablespace temp cannot be converted

 
 

  
  
exec dbms_space_admin.tablespace_migrate_from_local('book')  
 
 

6. delete a tablespace

 
 

  
  
drop tablespace student including contents; 
 
 

Including contents deletes tablespaces and entities

 
 

  
  
create table test(id number(3)) tablespace test_tablesapce; 
 
 

The table space status in Oracle is described above. Here I will share my understanding and discuss it with you.

1. How to unlock Oracle in a database
2. Five key points of Oracle password management
3. Unlock an Oracle user on the command line
4. Database security risks caused by default Oracle Administrators
5. Three operations of the VB. net undo tablespace