[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How to resize tablespace USERS

After run this command

SQL> select FILE_NAME,BYTES from dba_data_files;


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!

5 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

>>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.
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.
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.
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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