查询表空间 删除表空间 oracle

3ru7q331l2

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
　　from dba_tablespaces t, dba_data_files d
　　where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;


select t.tablespace_name
　　from dba_tablespaces t
　　where t.tablespace_name in ('UEP4X_LOG_INDEX', 'UEP4X_INDEX' , 'UEP4X_FM_DATA', 'UEP4X_CAF_FM_INDEX','UEP4X_PM_INDEX')



select * from uep4x.ALLDB_BACKUP
select * from ALLDB_BACKUP


delete from ALLDB_BACKUP where DB_NAME='UEP4X_LOG_INDEX';
delete from ALLDB_BACKUP where DB_NAME='UEP4X_INDEX';
delete from ALLDB_BACKUP where DB_NAME='UEP4X_FM_DATA';
delete from ALLDB_BACKUP where DB_NAME='UEP4X_CAF_FM_INDEX';
delete from ALLDB_BACKUP where DB_NAME='UEP4X_PM_INDEX';

INSERT INTO ALLDB_BACKUP( DB_NAME,SPECIAL_BACKUP )VALUES ('UEP4X_LOG_INDEX',0);
INSERT INTO ALLDB_BACKUP( DB_NAME,SPECIAL_BACKUP )VALUES ('UEP4X_INDEX',0);
INSERT INTO ALLDB_BACKUP( DB_NAME,SPECIAL_BACKUP )VALUES ('UEP4X_FM_DATA',0);
INSERT INTO ALLDB_BACKUP( DB_NAME,SPECIAL_BACKUP )VALUES ('UEP4X_CAF_FM_INDEX',0);
INSERT INTO ALLDB_BACKUP( DB_NAME,SPECIAL_BACKUP )VALUES ('UEP4X_PM_INDEX',0);

select * from ALLDB_BACKUP a, dba_tablespaces b where a.DB_NAME='UEP4X_LOG_INDEX'  and b.tablespace_name='UEP4X_LOG_INDEX';
select * from ALLDB_BACKUP a, dba_tablespaces b where a.DB_NAME='UEP4X_LOG_INDEX'  and b.tablespace_name='UEP4X_LOG_INDEX';

select * from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_LOG_INDEX'  and a.DB_NAME not in (select b.tablespace_name from dba_tablespaces b);


select * from ALLDB_BACKUP a
　　where a.DB_NAME in ('UEP4X_LOG_INDEX', 'UEP4X_INDEX' , 'UEP4X_FM_DATA', 'UEP4X_CAF_FM_INDEX','UEP4X_PM_INDEX')

delete from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_LOG_INDEX'  and a.DB_NAME  not in (select b.tablespace_name from dba_tablespaces b);
delete from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_INDEX'  and a.DB_NAME  not in (select b.tablespace_name from dba_tablespaces b);
delete from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_FM_DATA'  and a.DB_NAME  not in (select b.tablespace_name from dba_tablespaces b);
delete from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_CAF_FM_INDEX'  and a.DB_NAME  not in (select b.tablespace_name from dba_tablespaces b);
delete from ALLDB_BACKUP a  where a.DB_NAME='UEP4X_PM_INDEX'  and a.DB_NAME  not in (select b.tablespace_name from dba_tablespaces b);


drop tablespace UEP4X_LOG_INDEX including contents