I’m given a task to find the empty tablespaces.
I’ve checked dev server and majority of the tables’ stats were locked.
This solution takes time but works.
SELECT owner,count(1) FROM dba_tab_statistics where stattype_locked='ALL' group by owner;They are fixed for OCB execution plans. so statistics can not be updated.
So I’ve followed this method to find the empty tablespaces.
CREATE TABLE stats AS SELECT tablespace_name,owner,table_name,num_rows
FROM dba_tables WHERE 1=2;
DECLARE
val integer;
BEGIN
for i in (SELECT distinct table_name, owner,tablespace_name FROM DBA_tables WHERE temporary='N' and partitioned='NO' and tablespace_name not in ('SYSTEM','USERS','SYSAUX'))
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
for i in (SELECT distinct table_name, owner,tablespace_name FROM DBA_indexes WHERE temporary='N' and partitioned='NO' and tablespace_name not in ('SYSTEM','USERS','SYSAUX'))
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
for i in (SELECT distinct table_name, owner,tablespace_name FROM DBA_lobs WHERE partitioned='NO' and tablespace_name not in ('SYSTEM','USERS','SYSAUX'))
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
for i in (SELECT distinct table_name, table_owner owner,tablespace_name FROM DBA_tab_partitions WHERE tablespace_name not in ('SYSTEM','USERS','SYSAUX') )
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
for i in (SELECT distinct ind.table_name, ind.table_owner owner,ind.tablespace_name from dba_IND_PARTITIONS IP JOIN DBA_INDEXES Ind ON IP.INDEX_OWNER=Ind.OWNER AND IP.INDEX_NAME=Ind.INDEX_NAME WHERE IND.tablespace_name not in ('SYSTEM','USERS','SYSAUX') )
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
for i in (SELECT distinct table_name, table_owner owner,tablespace_name FROM DBA_lob_partitions WHERE tablespace_name not in ('SYSTEM','USERS','SYSAUX') )
LOOP
EXECUTE IMMEDIATE 'SELECT count(1) from ' || i.owner ||'.'|| i.table_name INTO val;
IF VAL=0 THEN
INSERT INTO stats VALUES (i.tablespace_name,i.owner,i.table_name,val);
commit;
END IF;
END LOOP;
END;after I’ve collected empty objects in stats table I’ve leveraged these tablespaces to start searching in dba_extents for the empty tablespaces.
CREATE TABLE TBSOBJ AS SELECT * FROM TBS_OBJ WHERE 1=2;
CREATE TABLE TBS_OBJ ( "TABLESPACE_NAME" VARCHAR2(128 BYTE), "OWNER" VARCHAR2(60 BYTE), "OBJ_NAME" VARCHAR2(128 BYTE), "OBJ_TYPE" VARCHAR2(60 BYTE), "ROWNUMS" NUMBER(*,0) );
declare
sql_stmt varchar2(1024);
row_count number;
v_table_name varchar2(50);
cursor get_tab is select tablespace_name, OWNER, SEGMENT_TYPE,COUNT(1) cnt from dba_extents where tablespace_name in (select distinct tablespace_name from stats)GROUP BY tablespace_name, OWNER, SEGMENT_TYPE order by 1;
begin
FOR get_tab_rec in get_tab LOOP
v_table_name := get_tab_rec.segment_type;
CASE WHEN get_tab_rec.SEGMENT_TYPE= 'INDEX' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select tablespace_name,owner,index_name,'INDEX',COUNT(1) FROM DBA_INDEXES WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name and temporary='N' and partitioned='NO' group by tablespace_name,owner,index_name,'INDEX' ;
commit;
DECLARE
cursor get_tbl is select tablespace_name , owner,table_name from DBA_INDEXES where TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec in GET_tbl LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec.OWNER ||'"."'|| tbl_rec.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec.tablespace_name,tbl_rec.owner,tbl_rec.table_NAME,'INDEX',row_count);
commit;
end if ;
end LOOP;
END;
when get_tab_rec.SEGMENT_TYPE= 'TABLE' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select tablespace_name,owner,table_name,'TABLE',COUNT(1) FROM DBA_TABLES WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name and temporary='N' and partitioned='NO' group by tablespace_name,owner,table_name,'TABLE' ;
commit;
DECLARE
cursor get_tbl is select tablespace_name , owner,table_name FROM DBA_tables WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec in GET_tbl LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec.OWNER ||'"."'|| tbl_rec.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec.tablespace_name,tbl_rec.owner,tbl_rec.table_NAME,'TABLE',row_count);
commit;
end if ;
end LOOP;
END;
when get_tab_rec.SEGMENT_TYPE= 'TABLE PARTITION' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select tablespace_name,TABLE_owner,table_name,'TABLE_PARTITION',COUNT(1) FROM dba_TAB_PARTITIONS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name group by tablespace_name,table_owner,table_name,'TABLE_PARTITION' ;
commit;
DECLARE
cursor get_tbl_prt is select tablespace_name , table_owner,table_name FROM dba_TAB_PARTITIONS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec_prt in get_tbl_prt LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec_prt.table_OWNER ||'"."'|| tbl_rec_prt.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec_prt.tablespace_name,tbl_rec_prt.table_owner,tbl_rec_prt.table_NAME,'TABLE PARTITION',row_count) ;
commit;
end if;
end LOOP;
END;
when get_tab_rec.SEGMENT_TYPE= 'INDEX PARTITION' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select I.tablespace_name,I.TABLE_owner,I.table_name,'INDEX_PARTITION',COUNT(1) from dba_IND_PARTITIONS IP JOIN DBA_INDEXES I ON IP.INDEX_OWNER=I.OWNER AND IP.INDEX_NAME=I.INDEX_NAME where IP.TABLESPACE_NAME=get_tab_rec.tablespace_name
group by I.tablespace_name,I.table_owner,I.table_name,'INDEX_PARTITION' ;
commit;
DECLARE
cursor get_tbl is select IP.tablespace_name , I.owner, I.table_name from dba_IND_PARTITIONS IP JOIN DBA_INDEXES I ON IP.INDEX_OWNER=I.OWNER AND IP.INDEX_NAME=I.INDEX_NAME where IP.TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec in GET_tbl LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec.OWNER ||'"."'|| tbl_rec.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec.tablespace_name,tbl_rec.owner,tbl_rec.table_NAME,'INDEX PARTITION',row_count) ;
commit;
end if;
end LOOP;
END;
when get_tab_rec.SEGMENT_TYPE= 'LOBSEGMENT' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select tablespace_name,owner,table_name,'LOB',COUNT(1) FROM DBA_LOBS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name and partitioned='NO' group by tablespace_name,owner,table_name,'LOB' ;
commit;
DECLARE
cursor get_tbl is select tablespace_name , owner,table_name FROM DBA_LOBS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec in GET_tbl LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec.OWNER ||'"."'|| tbl_rec.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec.tablespace_name,tbl_rec.owner,tbl_rec.table_NAME,'LOB',row_count);
commit;
end if ;
end LOOP;
END;
when get_tab_rec.SEGMENT_TYPE= 'LOB PARTITION' AND get_tab_rec.CNT>0 THEN
insert into tbs_obj select tablespace_name,TABLE_owner,table_name,'LOB_PARTITION',COUNT(1) FROM dba_LOB_PARTITIONS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name group by tablespace_name,table_owner,table_name,'LOB_PARTITION' ;
commit;
DECLARE
cursor get_tbl_prt is select tablespace_name , table_owner,table_name FROM dba_LOB_PARTITIONS WHERE TABLESPACE_NAME=get_tab_rec.tablespace_name ;
BEGIN
FOR tbl_rec_prt in get_tbl_prt LOOP
sql_stmt := 'SELECT COUNT(1) FROM "'|| tbl_rec_prt.table_OWNER ||'"."'|| tbl_rec_prt.TABLE_NAME||'"';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
if row_count>0 then
insert into tbsobj VALUES(tbl_rec_prt.tablespace_name,tbl_rec_prt.table_owner,tbl_rec_prt.table_NAME,'LOB PARTITION',row_count) ;
commit;
end if;
end LOOP;
END;
else null;
end case;
END LOOP;
END;and get the Full List of Empty Tablespaces.
select distinct tablespace_name from stats
minus
select distinct TABLESPACE_NAME FROM TBSOBJ
order by tablespace_name
;