7 Replies Latest reply: Mar 2, 2012 2:32 AM by Lubiez Jean-Valentin RSS

    import error

    846231
      Hi All,

      OraDb 10.2.0.3

      Today I have imported a dump of 10G size, into user schema "ABCADMIN" with default tablespace pointed to "USER_01" and temp at "TEMP".
      Note that "USER_01" is autoextend on.

      But I can not understand why it got error on latter tables saying:
      IMP-00017: following statement failed with ORACLE error 1658:
       "CREATE TABLE "SC_USER_GROUP" ("USR_GRP_ID" CHAR(20) NOT NULL ENABLE, "VERSI"
       "ON" NUMBER(5, 0) NOT NULL ENABLE, "OWNER_FLG" CHAR(4) NOT NULL ENABLE)  PCT"
       "FREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 262144 NEXT 2621"
       "44 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
       "CE "CISTS_01" LOGGING NOCOMPRESS"
      IMP-00003: ORACLE error 1658 encountered
      ORA-01658: unable to create INITIAL extent for segment in tablespace USER_01
      But in my alert log it has different message, lots of:
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      ORA-1652: unable to extend temp segment by 32 in tablespace                 USER_01 
      Why does temp points to USER_01 when the schema user itself has been set to TEMP?

      weird....

      Any ideas pleasee.....

      Thanks a lot,
        • 1. Re: import error
          CKPT
          ORA-1652: unable to extend temp segment by 32 in tablespace USER_01
          post

          SQL> select file_name,user_bytes/1024/1024,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name='USER_01';

          & check where that files reside and that have enough free space on mount point to extend..
          Thanks.

          Edited by: CKPT on Mar 1, 2012 7:33 PM
          • 2. Re: import error
            Dean Gagne
            If the user on the source database used tableapce user_01, then that will be the tablespace where the objects will be created when you do the import. This is a limitation of exp/imp. It doesn't matter what the default tablespace is for the target schema, what determines where the objects are created is the source database.

            If you were to use expdp/impdp then you could use remap_tablespace=old_tbs:new_tbs.

            Dean
            • 3. Re: import error
              846231
              Thanks all,

              I'm sure USER_01 is autoextensible since it grows to 35Gb now it single datafile from original 2G.
              My question is, how can I avoid this error? I thought autoextend handles new space allocation from physical disk
              where a table is needing additional space.
              • 4. Re: import error
                909975
                check the quota of "ABCADMIN" on "USER_01"
                • 5. Re: import error
                  Lubiez Jean-Valentin
                  Hello,

                  I'm sure USER_01 is autoextensible since it grows to 35Gb now it single datafile from original 2G.
                  My question is, how can I avoid this error? I thought autoextend handles new space allocation from physical disk
                  where a table is needing additional space.
                  Assuming that you are not using a Bigfile Tablespace, you may check for the db_block_size as follow:
                  show parameter db_block_size
                  If you find *8192* (8KB) which is the usual Block Size, then the Datafile is physically limited to 4MBlock, so about *32 GB*.

                  In this case you have to add a second Datafile and, your Tablespace could grow up to 64 GB.


                  Hope this help.
                  Best regards,
                  Jean-Valentin
                  • 6. Re: import error
                    846231
                    Thanks Jean :) ....most logical answer so far ;) ....so I will watch 32G+ limit for single tablespace then.....Is there a way to avoid this? Ah so i have to use Bigfile tablespace.....Is it available in 10g? Thanks.
                    • 7. Re: import error
                      Lubiez Jean-Valentin
                      Hello,

                      so I will watch 32G+ limit for single tablespace
                      In fact this limit (i.e. *32 GB* ) is for a Datafile in a Smallfile Tablespace. But a Smallfile Tablespace can have up to *1022* Datafiles, so a Smallfile Tablespace (with a Block Size of *8 KB* ) can reach almost *32 TB* which is enough in most of the case.
                      Is there a way to avoid this?
                      Yes, if you know the Size of the Source Database from which the Dump come from, you may assess the space you need in the Tablespace, and add all the necessary Datafiles. For instance, if you need 100 GB then you should have 4 Datafiles in your Tablespace ( 4 x 32 GB > 100 GB).
                      Ah so i have to use Bigfile tablespace.....Is it available in 10g?
                      Bigfile Tablespaces are available in 10g but you should be carreful with them. In fact a Bigfile Tablespace have only *1 Datafile*, but it can grow up to *4 GB Blocks*, so *32 TB* (with Block Size of 8 KB). Then, as you can see a Bigfile Tablespace is not bigger than a Smallfile Tablespace. However, the Bigfile Tablespace can have a very large Datafile and, you should be sure that the OS and the Hardware can support a so large file:
                      Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
                      Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm#i1010733



                      Hope this help.
                      Best regards,
                      Jean-Valentin