Database Administration (MOSC)

MOSC Banner

HWM in table, space defrag issue

edited Nov 24, 2017 11:38AM in Database Administration (MOSC) 4 commentsAnswered

Team,

I have moved a table from DATA diskgroup to FRA Diskgroup using

alter table .. move tablespace ..;

however the space in the DATA diskgroup has not changed.

After table move, I used below command  to resize datafile, but no result for DATA diskgroup.

please help.

SELECT ceil( blocks*(a.BlockSize)/1024/1024) "Current Size",

   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Smallest Poss.",

   ceil( blocks*(a.BlockSize)/1024/1024) -

   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) "Savings",

   'alter database datafile '''|| file_name || ''' resize ' ||

      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || 'm;' "Command"

FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a

JOIN v$parameter p ON p.Name='db_block_size') a

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center