|
|
|
|
|
|
|
|
|
|
|
|
Re: Can't resize datafile: ORA-03297 [message #636134 is a reply to message #636133] |
Fri, 17 April 2015 07:50 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
|
|
|
Re: Can't resize datafile: ORA-03297 [message #636135 is a reply to message #636134] |
Fri, 17 April 2015 08:27 |
|
mark_valley
Messages: 9 Registered: April 2015
|
Junior Member |
|
|
Wow! Nice report!
VALUE
--------------------------------------------------------------------------------
8192
VALUE
--------------------------------------------------------------------------------
8192
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
+DATA/mytablespace/datafile/datafile2 2,231 32,767 30,536
+DATA/mytablespace/datafile/datafile3 1 8,192 8,191
+DATA/mytablespace/datafile/undotbs1.264.810056497 1,690 5,484 3,794
+DATA/mytablespace/datafile/datafile_aux.2568.877209477 12,774 13,490 716
+DATA/mytablespace/datafile/datafile_blob 5,891 6,548 657
+DATA/mytablespace/datafile/perfstat_ts.467.810768761 896 1,000 104
+DATA/mytablespace/datafile/sysaux.263.810056497 791 830 39
+DATA/mytablespace/datafile/users.261.810056497 7 40 33
+DATA/mytablespace/datafile/system.271.810056497 1,839 1,850 11
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
+DATA/mytablespace/datafile/datafile_idx.267.810057825 13,520 13,520 0
+DATA/mytablespace/datafile/datafile.262.810057775 32,767 32,767 0
--------
sum 44,081
11 rows selected.
|
|
|
|
|
|
Re: Can't resize datafile: ORA-03297 [message #636145 is a reply to message #636141] |
Fri, 17 April 2015 13:43 |
|
mark_valley
Messages: 9 Registered: April 2015
|
Junior Member |
|
|
Can I use those commands with a tablespace? I use it a lot with tables but never tried something like that.
alter database enable row movement;
SQL error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
alter tablespace MYTABLESPACE shrink;
SQL error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Well, I just finished to send all data to other tablespaces. My major problems was some columns with the deprecated "long" datatype. I created a new blob tablespace to help organize the things. Now data+blob tablespaces are occupying about 32GB.
Do you think it is more secure to set the old tablespace offline for some time before dropping it? Just to be sure that nothing will "go wrong"?
And thank you so much! I Dont't know if it is worthwhile to continue trying to figure out what is happening. If you are curious we can continue.
Regards!
|
|
|