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

Thread: EXP FAILING EXP-00003: no storage definition found for segment(3, 2)

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Posts
    3

    EXP-00001 Error

    I'm attempting to do an export of my entire database. I get this dreaded EXP-00003 error. It appears to be in a rollback segment.

    I recently converted all tablespaces except SYSTEM and TEMP to locally managed using the DBMS utility.

    Any ideas on what to do?


    SQL> select * from dba_segments where HEADER_FILE = 3 and HEADER_BLOCK = 2;

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    SYS

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    RBS0

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    ROLLBACK RBS

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    3 2 1048576 128 8 131072

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    131072 8 4096 0 1 1

    OWNER
    ------------------------------
    SEGMENT_NAME
    --------------------------------------------------------------------------------
    PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
    ------------------------------ ------------------ ------------------------------
    HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
    ----------- ------------ ---------- ---------- ---------- --------------
    NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
    ----------- ----------- ----------- ------------ ---------- ---------------
    RELATIVE_FNO BUFFER_
    ------------ -------
    3 DEFAULT



    SQL>



    [oracle@oracle /oracle04]$ exp

    Export: Release 8.1.6.1.0 - Production on Wed Oct 31 11:27:35 2001

    (c) Copyright 1999 Oracle Corporation. All rights reserved.


    Username: sys
    Password:

    Connected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production
    Enter array fetch buffer size: 4096 >

    Export file: expdat.dmp > bakup2.dmp

    (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 1

    Export grants (yes/no): yes > y

    Export table data (yes/no): yes > y

    Compress extents (yes/no): yes > y

    Export done in US7ASCII character set and US7ASCII NCHAR character set

    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    EXP-00003: no storage definition found for segment(3, 2)
    EXP-00000: Export terminated unsuccessfully
    [oracle@oracle /oracle04]$

    [Edited by mendozo2 on 10-31-2001 at 11:53 AM]

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    It looks like the problem is in your roll back segment 3.

    Run the following querry and confirm it
    Code:
            select * from dba_rollback_segs;
    Check all the storage definitions.

    Code:
            Select * from dba_rollback_segs 
                  where segment_id = 3;
    Does it look like the rest?

    If different look at the segment_name. Try taking that segment off-line.
    Code:
             alter rollback segment  segment_name offline; 
    
                select status from dba_rollback_segs 
                     where segment_name = 'SEGMENT_NAME';
    It must be OFFLINE.
    Then drop that rollback segment. Re-try your FULL export. If the ROLLBACK segment will not go offline, then export using USERS.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    May 2001
    Posts
    39

    Cool EXP FAILING EXP-00003: no storage definition found for segment(3, 2)

    The same situation is being reproduced in 8.1.7 on NT platform.
    since i had converted the extent management to llocal the error was caused.
    The error was rectified by converting the extent management of the tablespace (RBS) from locally to Dictionary management and then taking a export .

    sanctus
    sanctus

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