Table space related commands and query SQL

Table space related commands and query SQL
the
1. Delete the tablespace and data files
drop tablespace TS_GLOBAL_01 including contents and datafiles;
2. Delete data files offline www.2cto.com
alter database datafile '/oradata/DTCDB/TS_INDEX_02' offline drop;
3. Automatic rollback tablespace file deletion
alter tablespace undotbs add datafile '/oradata/DTCDB/undotbs_01.dbf';
the
4. Create an automatic rollback tablespace file
create undo tablespace undotbs02 datafile '/oradata/DTCDB/undotbs_02.dbf' size 10M;
5. Query segment, data file information
select distinct t1.owner, /*t1.segment_name,*/ t1.segment_type, t1.segment_subtype, t2.*
from dba_segments t1, dba_data_files t2
where t1.tablespace_name = t2.tablespace_name
–and t2.file_name = '/opt/oracle/11g/dbs/TS_INDEX_01'
and t2.tablespace_name = & # 39; TS_INDEX_01 & # 39;
order by t1.owner, t2.file_name
www.2cto.com
select t.file# ,t.status,t1.file_name from v$datafile t ,dba_data_files t1 where t.file# = t1.file_id and t.STATUS='RECOVER';
the
6. Empty the global recycling manager
purge dba_recyclebin;
7. Query all segment information
select t.owner, sum(t.bytes) / 1024 / 1024 / 1024 as size_g
from dba_segments t
–where t.segment_type = 'TABLE'
group by t.owner
order by t. owner;
8. Query the physical space occupied by the tablespace file
select t. tablespace_name,
trunc(t.bytes / 1024 / 1024 / 1024, 3) as size_g,
t.file_name,
t.online_status
from dba_data_files t
where t.tablespace_name like & # 39; TS_% & # 39;
and t.bytes / 1024 / 1024 / 1024 > 1
order by t.tablespace_name, size_g desc, t.file_name;
the
9. Query the status information of the data file
select file#,name,status,enabled,bytes/1024/1024 M from v$datafile t
where t.NAME='/oradata/DTCDB/TS_INDEX_02'
10. Query database system file information www.2cto.com
select * from sys.file$
11. Database file recovery operation
shutdown abort;
a. Successful recovery
startup mount;
recover datafile '/oradata/DTCDB/TS_PARTITION_A_000';
alter database open;
b. Recovery failed
startup mount;
alter database datafile '/oradata/DTCDB/TS_PARTITION_A_000' offline drop;
alter database open;
the
the
Author AttaGain