SQL> DECLARE
2 Cursor C_DBFile IS
3 Select TableSpace_name
4, file_name
5, File_ID
6, Bytes
7 from sys.dba_data_files
8 WHERE STATUS! = 'Invalid'
9 ORDER BY TABLESPACE_NAME, FILE_ID;
10 CURSOR C_SPACE (v_file_id in number) IS
11 Select Block_ID, Blocks
12 from sys.dba_free_space
13 where file_id = v_file_id
14 Order by block_id DESC;
15 blocksize binary_integer;
16 FileSize Binary_INTEGER;
17 EXTSIZE BINARY_INTEGER;
18 Begin
19 Select Value
20 INTO Blocksize
21 from V $ Parameter
22 WHERE Name = 'db_block_size';
23 for c_rec1 in c_dbfile
24 LOOP
25 FileSize: = c_rec1.bytes;
26 <
>
27 for c_rec2 in c_space (c_rec1.file_id)
28 LOOP
29 EXTSIZE: = ((c_rec2.block_id - 1) * blocksize c_rec2.blocks * blocksize;
30 if extsize = filesis
31 THEN
32 FileSize: = (c_rec2.block_id - 1) * blocksize;
33 else
34 exit outside;
35 end if;
36 End loop outer;
37 if FileSize = C_Rec1.bytes
38 THEN
39 dbms_output.put_line ('TableSpace:'
40 || '' || c_rec1.tablespace_name || 'DataFile:' || c_rec1.file_name;
41 dbms_output.put_line ('can not be resize, no free space at end of file.')
42;
43 dbms_output.put_line ('.');
44 else
45 IF FileSize <2 * blocksize
46 THEN
47 dbms_output.put_line ('TableSpace:'
48 || '' || c_rec1.tablespace_name || 'DataFile:' || c_rec1.file_name;
49 dbms_output.put_line ('can be resize uptil:' || 2 * blocksize
50 || 'Bytes, Actual Size:' || C_REC1.BYTES || 'BYTES');
51 dbms_output.put_line ('.');
52 else
53 dbms_output.put_line ('TableSpace:'
54 || '' || C_REC1.TABLESPACE_NAME || 'DataFile:' || c_rec1.file_name); 55 dbms_output.put_line ('Can Be Resized Uptil:' || FileSize
56 || 'Bytes, Actual Size:' || c_rec1.bytes);
57 dbms_output.put_line ('.');
58 End IF;
59 end if;
60 End loop;
61 End;
62 /
TableSpace: drsys datafile: /usr/racle/data/oradata/cint208/drsys01.dbf
Can Be Resized Uptil: 4333568 BYtes, Actual Size: 5242880
.
TABLESPACE: INDX DATAFILE: /USR/OrCle/data/oradata/cint208/indx01.dbf
Can Be Resize Uptil: 16384 Bytes, Actual Size: 5242880 BYTES
.
TABLESPACE: RBS DataFile: /usr/racle/data/oradata/cint208/rbs01.dbf
Can Be Resize Uptil: 57155584 Bytes, Actual Size: 57671680
.
TableSpace: System DataFile: /usr/racle/data/oradata/cint208/system01.dbf
Can Be Resize Uptil: 280182784 Bytes, Actual Size: 283115520
.
TableSpace: Temp DataFile: /usr/oracle/data/oradata/cint208/temp01.dbf
Can Be Resize Uptil: 16384 Bytes, Actual Size: 5242880 BYTES
.
TABLESPACE: Testspace DataFile: /usr/oracle/data/oradata/cint208/testspace1.dbf
Can Be Resize Uptil: 16384 Bytes, Actual Size: 5242880 BYTES
.
TakeSpace: Tools DataFile: /usr/oracle/data/oradata/cint208/tools01.dbf
Can Be Resize Uptil: 16384 Bytes, Actual Size: 5242880 BYTES
.
TableSpace: Users DataFile: /usr/racle/data/oradata/cint208/USERS01.DBF
Can Be Resize Uptil: 23076864 Bytes, Actual Size: 23592960
.
PL / SQL Procedure SuccessFully Completed.
SQL>
SQL>
SQL>
