4 Replies Latest reply on Jun 12, 2012 1:46 PM by CKPT

    ora -03249 error on creation of tablespace

    user11221081
      dear gurus

      i have an export dump from linux...i want to import it in windows...

      as export taken by full=y so i am reading the .dmp and creating tablespaces and users from it and then will import schema wise

      now i have a tablespace creation command in dump file as

      CREATE TABLESPACE "TOOLS" DATAFILE '/oradata/irshipdb/data/tools01.dbf' SIZE 10485760 REUSE DEFAULT STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERMANENT

      when i am executing it on windows machine its giving error

      ORA-03249 – Uniform size for auto segment space managed tablespace should have atleast 5 blocks

      please suggest how to i create the same tablespace and if i create it without storage clause,would the import will run fine.

      Rgds
        • 1. Re: ora -03249 error on creation of tablespace
          CKPT
          i have an export dump from linux...i want to import it in windows...
          as export taken by full=y so i am reading the .dmp and creating tablespaces and users from it and then will import schema wise
          now i have a tablespace creation command in dump file as
          CREATE TABLESPACE "TOOLS" DATAFILE '/oradata/irshipdb/data/tools01.dbf' SIZE 10485760 REUSE DEFAULT STORAGE(INITIAL 32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERMANENT
          when i am executing it on windows machine its giving error
          ORA-03249 – Uniform size for auto segment space managed tablespace should have atleast 5 blocks
          please suggest how to i create the same tablespace and if i create it without storage clause,would the import will run fine.
          Error:       ORA-03249  (ORA-3249)
          Text:       Uniform size for auto segment space managed tablespace should 
                 have atleast %s blocks 
          ---------------------------------------------------------------------------
          Cause:     For the given blocksize, uniform size specified is insufficient 
          Action:     Specify larger extent size and retry
          • 2. Re: ora -03249 error on creation of tablespace
            Helios-GunesEROL
            Hi;

            Please see:
            OERR: ORA-3249 Uniform size for auto segment space managed tablespace should h [ID 172561.1]

            Regard
            Helios
            • 3. Re: ora -03249 error on creation of tablespace
              user11221081
              hello

              thanx for reply

              but i am not getting what exactly do i need to do...tell me if i create tabespace just with autoextend and not give storage clause...would my import run fine.
              • 4. Re: ora -03249 error on creation of tablespace
                CKPT
                but i am not getting what exactly do i need to do...tell me if i create tabespace just with autoextend and not give storage clause...would my import run fine.
                It will work.
                (or)

                from source take DDL of that tablespace & you can run that DDL according to the locations. better to create same size of datafiles while importing
                SQL> set long 90000
                SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
                
                DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
                --------------------------------------------------------------------------------
                
                  CREATE TABLESPACE "USERS" DATAFILE
                  'C:\ORACLE\APP\ORADATA\CKPT\USERS01.DBF' SIZE 5242880
                  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
                  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEM
                ENT AUTO
                   ALTER DATABASE DATAFILE
                  'C:\ORACLE\APP\ORADATA\CKPT\USERS01.DBF' RESIZE 28835840
                Or your same example
                SQL> CREATE TABLESPACE "TOOLS" DATAFILE 'C:\ORACLE\APP\ORADATA\CKPT\tools01.dbf' SIZE 10m REUSE DEFAULT STORAGE(INITIAL
                32768 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERMANENT ;
                CREATE TABLESPACE "TOOLS" DATAFILE 'C:\ORACLE\APP\ORADATA\CKPT\tools01.dbf' SIZE 10m REUSE DEFAULT STORAGE(INITIAL 32768
                 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERMANENT
                *
                ERROR at line 1:
                ORA-03249: Uniform size for auto segment space managed tablespace should have atleast 5 blocks
                
                "NOW" 
                
                SQL> CREATE TABLESPACE "TOOLS" DATAFILE 'C:\ORACLE\APP\ORADATA\CKPT\tools01.dbf' SIZE 10m REUSE DEFAULT STORAGE(INITIAL
                32768 NEXT 5 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) ONLINE PERMANENT ;
                
                Tablespace created.
                
                SQL>
                Edited by: CKPT on Jun 12, 2012 7:16 PM