• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1255
  • Last Modified:

How to resize tablespace USERS

After run this command

SQL> select FILE_NAME,BYTES from dba_data_files;

FILE_NAME
-------------------------------------------------
     BYTES
----------
/u02/oradata/orcl/orcl/users01.dbf
9201254400

I see the size of datafile on tablespace USERS too large, but on it have any record with 10 tables, what happend and how to resize it for perfoment is good.
Pls help me!

Phapsu
0
phapsuxxx
Asked:
phapsuxxx
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
anand_2000vCommented:
alter database datafile '/u02/oradata/orcl/orcl/users01.dbf' resize 100m;
Of course use any size that you want...in M, K or just the bytes.
0
 
johnsoneSenior Oracle DBACommented:
To build on anand's post...

You cannot resize a file smaller than the last used block in the file.  Oracle will give you an error.  You can find the last block used in DBA_EXTENTS.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
actonwangCommented:
>>last used block
     sth like this . More accurately ,it might need to be "high-water mark in the file".    
     You can not reduce the file size below the high-water mark in the file.
0
 
Mark GeerlingsDatabase AdministratorCommented:
You may have to move at least some of the tables (and indexes, if any) out of the USERS tablespace to another tablespace, before you can reduce the size of the USERS tablespace.
0
 
anand_2000vCommented:
There maynot be a requirement for that as there seem to be only 10 tables in the tablespace.
But in any case give this command also

select sum(bytes)/(1024*1024) from dba_segments where tablespace_name='USERS';

If this is also coming to or is near 8775 then you cannot resize the tablespace but rather will have to add datafiles to provide more space for segments in the tablespace.
0
 
johnsoneSenior Oracle DBACommented:
I guess I should have been clearer and said last allocated block.

This query should give you the last allocated block in the file.

select block_id + blocks
  from dba_extents a
 where file_id = <fid> and
       block_id = (select max(block_id)
                     from dba_extents b
                    where a.file_id = b.file_id
                  );

<fid> would need to be replaced with the file id of the file you are trying to resize.  You can get it from DBA_DATA_FILES.

If you are using locally managed tablespaces the query may take a long time (depending on the size of your database).  This is one of the down sides of locally managed tablespaces, since the extent maps are stored in the file and not the data dictionary, every extent map in every file must be passed in order to satisfy the query.

This will give you the number of blocks.  To get the size in bytes, you need to mulitply by your block size.  Block size can be found with this query

select block_size
  from dba_tablespaces
 where tablespace_name = '<tspace>';

This should give you the information you need to get the file as small as possible.

Keep in mind there still can be empty blocks in the file, so the number you get may not be the same as the results of the query that anand gave.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now