DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Why a tablespace is empty while contains no data?

  1. #1
    Join Date
    Nov 2000
    Posts
    164
    Good afternoon all,

    I have 3 tablespaces, tblsp_1, tblsp_2, and tblsp_3. Tblsp_1 is a default tablespace for user1. When user1 was importaing tables, he got an error message

    'ORA-01658: unable to create INITIAL extent for segment in tablespace tblsp_2'

    After I added a datafile to tblsp_2, the import was able to continue. I checked the tables and tablespace from dba_tables, and found no tables are contained in the tblsp_2, yet there is no free space left in tblsp_2 neither. This is what I got,

    TABLESPACE_NAME Total Free Space
    ------------------------------ -------------
    TBLSP_1 248512512
    TBLSP_3 6291456


    This is what I have for all tablespaces,
    TABLESPACE_NAME FILE_NAME BYTES AT
    --------------- -------------------------------------------------- ---------- --
    TBLSP_1 /oradata/data/tblsp_1.dbf 840957952 NO
    TBLSP_2 /oradata/data/tblsp_2.dbf 419430400 NO
    TBLSP_3 /oradata/data/tblsp_3.dbf 368050176 NO
    TBLSP_2 /oradata/data/tblsp_2b.dbf 571473920 YS

    I don't understand why TBLSP_2 has no free space while it contains no tables? And why error 'ORA-01658' on TBLSP_2 during import, yet the data are imported to the user's default tablespace? Can anyone explain why?

    Thanks.

  2. #2
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    I don't understand why TBLSP_2 has no free space while it contains no tables?

    How did you check that it has no tables?

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  3. #3
    Join Date
    Nov 2000
    Posts
    164
    This is what I have for checking the tables,

    select table_name, tablespace_name
    from dba_tables
    where tablespace_name = 'TBLSP_2';

    And the result returns no rows selected.

  4. #4
    Join Date
    Jan 2002
    Posts
    148
    Can u show the out put for this.

    select segment_name,sum(bytes) from dba_extents where tablespace_name='' group by segment_name;

    Thanx
    Jr

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Run foll. SQL and see the result

    select *
    from dba_segments
    where tablespace_name = 'TBLSP_2';

    or

    select tablespace_name from dba_segments
    where segment_name = .

    See what tablespace name it shows for that table.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by samdba
    Run foll. SQL and see the result

    select *
    from dba_segments
    where tablespace_name = 'TBLSP_2';

    or

    select tablespace_name from dba_segments
    where segment_name = .

    See what tablespace name it shows for that table.

    Here, it has truncated after segment_name = as I used less than sign.

    Here is the actual text.

    select tablespace_name from dba_segments
    where segment_name = table_name, which you think is imported in TBLSP_2 tablespace
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Nov 2000
    Posts
    164
    Thank you all for the help.

    I have found why it's full. The tablespace does not contain any tables but it contains indexes. Thank you again for the help!

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Check folowing information about this tablespace:

    1) temporary segments:

    select TABLESPACE_NAME, sum(BYTES_USED), sum(BYTES_FREE)
    from V$TEMP_SPACE_HEADER
    group by TABLESPACE_NAME;

    2) u may also check sort segments:

    select TABLESPACE, sum(EXTENTS) "# of extents"
    from V$SORT_USAGE
    group by TABLESPACE;

    3) check users' accounts :

    select USERNAME, TEMPORARY_TABLESPACE, DEFAULT_TABLESPACE
    from dba_users
    -- where TEMPORARY_TABLESPACE = 'TBLSP_2';
    and then change TEMPORARY_TABLESPACE for all users that have
    TEMPORARY_TABLESPACE = 'TBLSP_2'

    4) try to use:

    alter tablespace TBLSP_2 offline TEMPORARY;
    and then
    alter tablespace TBLSP_2 online;

    5) probably u need execute:
    ALTER TABLESPACE TBLSP_2 COALESCE;

    When u collect all information u can deside what's happened with this tablespace.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width