One of our DW environment is very dynamic in terms of space utilization and as client is planning to move production environment to Exadata they do not want to want to invest anything in existing SAN infrastructure which is Netapp. Priorities of work done by Data Analysts and Product creation team changes every other day and Data analysts are given quota on USERS tablespace and all Product creation team uses DATA tablespace. Both USERS and DATA tablespaces are mapped to same ASM Disk group. I could have used one tablespace only but have not done it so as I have seen Data Anaysts writing cartesian join and filling up entire tablespace/mount points and security. To cut long story short, I have to do manual tablespace managment for USERS and DATA depending upon requirement. Below is the script that will display data files that can be resized to release free space from data files above High Water Mark (HVM).
VDESAI@db > @freespace
TABLESPACE_NAME TOTAL_SPACE FREE_SPACE AUTO_GROW LARGEST_EXTENT USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS 181,733 62,208 305,274 11,200 119,525 66 168
....
TOTAL_SPACE FREE_SPACE USED_SPACE
----------- ----------- -----------
7,611,993 2,104,437 5,507,556
VDESAI@db > @release_tbs_space.sql
Please enter tablespace name : USERS
--alter database datafile '+DG_DATA_CSPROD/users_36.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_02.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_38.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_46.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_19.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_13.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;
--alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--frag size-1728m;
--alter database datafile '+DG_DATA_CSPROD/users_37.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_07.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_32.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_23.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_26.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_50.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;
--alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--frag size-1152m;
--alter database datafile '+DG_DATA_CSPROD/users_03.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_31.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_09.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;
--alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--frag size-2048m;
--alter database datafile '+DG_DATA_CSPROD/users_47.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;
--alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--frag size-6080m;
--alter database datafile '+DG_DATA_CSPROD/users_54.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_53.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_05.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_33.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_21.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_16.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_14.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_25.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_22.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_48.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_20.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_06.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_34.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_30.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_15.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_52.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_11.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_01.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_18.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_35.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_12.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;
--alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--frag size-1088m;
alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;
--alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--frag size-512m;
--alter database datafile '+DG_DATA_CSPROD/users_49.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_39.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_24.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_29.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_08.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_10.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_28.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_51.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_04.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;
--alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--frag size-1408m;
--alter database datafile '+DG_DATA_CSPROD/users_27.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_40.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_17.dbf' resize m--frag size0m;
Total space reclaimation:13568MB
VDESAI@db >
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;
Database altered.
VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;
Database altered.
VDESAI@db > @freespace
TABLESPACE_NAME TOTAL_SPACE FREE_SPACE AUTO_GROW LARGEST_EXTENT USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS 168,166 48,640 312,378 11,200 119,526 71 186
....
VDESAI@db > l
1 declare
2 cursor c1 is select tablespace_name,file_id,max(block_id) mbid from dba_extents where tablespace_name='&&tbs'
3 group by tablespace_name,file_id;
4 fs number;
5 fn varchar2(100);
6 ts number;
7 x number;
8 gtot number:=0;
9 tffs number:=0;
10 ffrags number:= 0;
11 begin
12 for v1 in c1 loop
13 select sum(bytes)/1024/1024 into fs from dba_free_space where tablespace_name = v1.tablespace_name and
14 file_id = v1.file_id and block_id>v1.mbid;
15 select file_name,bytes/1024/1024 into fn,ts from dba_data_files where file_id=v1.file_id and tablespace_name=v1.tablespace_name;
16 select sum(bytes/1024/1024) into tffs from
17 (select a.bytes/1024/1024 as bytes from dba_free_space a where file_id=v1.file_id and tablespace_name=v1.tablespace_name
18 union all
19 select 0.000001 as bytes from dual);
20 x:=(ts-fs)+64;
21 if ts>x then
22 dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' || round(x) || 'm' || '--total size' || round(ts) || 'm;' );
23
24 gtot := gtot + (ts-x);
25 end if;
26
27 ffrags := nvl(tffs - fs,0);
28 dbms_output.put_line('--alter database datafile ' || '''' || fn || '''' || ' resize ' || round(x) || 'm' || '--frag size' || round(ffrags) || 'm;');
29
30 end loop;
31
32 dbms_output.put_line('Total space reclaimation:' || gtot || 'MB');
33* end;
VDESAI@db >
