Table space-related commands and SQL queries

Table space commands and query SQL 1. Delete table space and data files drop tablespace TS_GLOBAL_01 including contents and datafiles; 2. Delete the data file www.2cto.com alter database datafile '/oradata/DTCDB/TS_INDEX_02' offline drop; 3. automatically roll back the tablespace file to delete alter tablespace undotbs add datafile '/oradata/DTCDB/undotbs_01.dbf ';
4. create an automatic rollback tablespace file create undo tablespace undotbs02 datafile '/oradata/DTCDB/undotbs_02.dbf' size 10 M; 5. select distinct t1.owner for query segments and data file information, /* t1.segment _ name, */t1.segment _ type, t1.segment _ subtype, t2. * from dba_segments t1, dba_data_files t2where t1.tablespace _ name = t2.tablespace _ name -- and t2.file _ name = '/opt/oracle/11g/dbs/TS_INDEX_01' and t2.tablespace _ name = 'ts _ INDEX_01 'order 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 ';
6. Clear the global recycle 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. ownerorder by t. owner; 8. query the physical space occupied by table space files. select t. tablespace_name, trunc (t. bytes/1024/1024/1024, 3) as size_g, t. file_name, t. online_status from dba_data_files twhere t. tablespace_name like 'ts _ % 'and t. bytes/1024/1024/1024> 1 order by t. tablespace_name, size_g desc, t. file_name;
9. query the status information of the data file select file #, name, status, enabled, bytes/1024/1024 M from v $ datafile twhere t. NAME = '/oradata/DTCDB/TS_INDEX_02' 10. query the database system file information www.2cto.com select * from sys. file $11. shutdown abort for database file recovery; a. startup mount for successful recovery; recover datafile '/oradata/DTCDB/TS_PARTITION_A_000'; alter database open; B. startup mount for failed recovery; alter database datafile '/oradata/DTCDB/TS_PARTITION_A_000' offline drop; alter database open; author AttaGain