How to resize tablespace USERS

Posted on 2006-05-10
Last Modified: 2008-02-26
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!

Question by:phapsuxxx
    LVL 142

    Accepted Solution

    LVL 14

    Assisted Solution

    alter database datafile '/u02/oradata/orcl/orcl/users01.dbf' resize 100m;
    Of course use any size that you M, K or just the bytes.
    LVL 34

    Assisted Solution

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

    Assisted Solution

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

    Assisted Solution

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

    Expert Comment

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

    Expert Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now