5 Replies Latest reply on Jun 30, 2011 9:46 PM by Udo

    11g space limit happens when 5g are present?


      I have a 11.2 database (linux) with several data imported (no blobs till now).
      From 10.2 console enterprise manager, I can see tablespaces name, type, size and use:
      sysaux, pemanent, 530Mb (492 used)
      undotbs1, undo, 384 Mb (246 used)
      t_jobins, pemanent, 2Mb (1 used)
      abiec, pemanent, 8777Mb (3001 used)
      system, pemanent, 360Mb (358 used)
      indx, pemanent, 1900Mb (1646 used)
      usr, pemanent, 100Mb (100 Mb used)
      temp, temporary, 740 Mb (738 Mb used)
      Temporary, temporary, 2Mb (0 used).

      => total used for data=4748 Mb (1 + 3001 + 1646 + 100).

      Tablespaces are locally managed, ASSM, autoextend on.
      I also have 6Gb free on the disk

      I want to import the some structures (no rows) from a 10gr2 xe db (so max 4Gb even with unicode data). DMP file is 670 Kb (so sure, no rows are in it).
      export NLS_LANG=...
      imp userid=superuser/abyyal file=EXPDAT.DMP statistics=none fromuser=cec,abiec,carddb,jbossmq,production,quartz,guard,eiddb,rnfiles touser=cec,abiec,carddb,jbossmq,production,quartz,guard,eiddb,rnfiles rows=n ignore=y log=empoldtest.log

      Connected to: Oracle Database 11g Express Edition Release - 64bit Beta

      Export file created by EXPORT:V10.02.01 via conventional path
      import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
      . importing CEC's objects into CEC
      . importing ABIEC's objects into ABIEC
      IMP-00017: following statement failed with ORACLE error 12953:
      IMP-00003: ORACLE error 12953 encountered
      ORA-12953: The request exceeds the maximum allowed database size of 11 GB

      1. I don't use 11Gb data. Does this mean that oracle counts space that HAS been used in the past? Or do I have to shrink data tablespace where only 50% is used (and how????)
      2. the tablespace usr is full and autoextend on, should I make it bigger before import?
      3. How do I monitor what oracle "thinks" to be used?

        • 1. Re: 11g space limit happens when 5g are present?
          Lakmal Rajapakse
          I think oracle counts the space allocated to the datafile rather than the space used by objects. Why don't you create the objects in the abiec tablespace as it has enough free space. Or you could try to resize the datafiles in the abiec tablespace by issuing the following command:

          alter database datafile '< data file name> ' resize <new size>;

          Of course you can only resize the files upto the HWM.

          • 2. Re: 11g space limit happens when 5g are present?
            ...and if the HWM is too high for you, and you can't lower the HWM enough easily by shrinking, you could try to "defragment" the existing tablespace by moving objects near the HWM into a different tablesapce so you could lower it. Obviously, you have to gain a bit of free space somewhere. My suggestion would be the indx tablespace, assuming you have only index data in there. Indexes can be recreated, so you could drop them and drop that tablespace to gain these 1900m to do some "defragmentation" to lower the HWM of your abiec tablespace - which has really grown quite large for a XE tablespace...
            To "defragment", you can use statements like
            ALTER TABLE <tablename> TABLESPACE <new_tablespace>;
            ALTER INDEX <indexname> REBUILD <new_tablespace>;
            And after resizing it, do the same backwards from <new_tablespace> to /abiec/ again, if you want to keep things together.
            To find out best candidates, use a statement like
            SELECT owner, segment_type, segment_name
              FROM dba_extents
             WHERE tablespace_name ='ABIEC'
             ORDER BY block_id DESC;
            • 3. Re: 11g space limit happens when 5g are present?
              oracle 11g xe is completely stupid as it claims unused space.
              Here is what I did:
              ->drop tablespace abiec including contents and datafiles;
              ->shutdown and restart db.
              ->create user borabiec identified by ...;
              ->alter user borabiec default tablespace abiec;
              ->grant connect, resource to borabiec;
              ->alter user borabiec quota unlimited on abiec;
              ->alter user borabiec quota unlimited on indx;

              export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
              imp file=/disk2/ablast.dmp userid=superuser/... fromuser=borabiec touser=borabiec ignore=y indexes=n statistics=none
              => import ok no warning
              => If I look at abiec tablespace: 8830 Mb, 8343 used.

              then for all table in tablespace abiec : enable row movement, shrink space compact, shrink space cascade (there are indexes for primary keys, foreigh keys, constraints), shrink space;

              set pages 200
              set head off
              select 'alter table '||owner||'.'||table_name||' enable row movement;' from dba_tables where owner='BORABIEC';
              select 'alter table '||owner||'.'||table_name||' shrink space compact;' from dba_tables where owner='BORABIEC';
              select 'alter table '||owner||'.'||table_name||' shrink space cascade;' from dba_tables where owner='BORABIEC';
              select 'alter table '||owner||'.'||table_name||' shrink space;' from dba_tables where owner='BORABIEC';
              and execute results.

              At this point, if I look at abiec tablespace: 8830 Mb, 3001 used. Oracle used more than 2 times the space needed to store data (tablespace didn't even need to be extended).

              At oracle training, I was told: when importing into an assm tablespace, storage clause of objects inside dmp file is ignored. It seems that either it is not true (and I will be able to see if this causes the problem by re-exporting then re-importing), either that oracle uses twice the space needed, and you can't claim it afterwards: file size cannot be reduced, and you cannot move all tables to other tablespace as you don't have 3Gb available. Real bullshit. I will be able to do it table by table, hopefully I have less than 50.
              • 4. Re: 11g space limit happens when 5g are present?
                if you re-export the tables after a shrink space compact/shrink space cascade/shrink space, drop the tablespace, re-create it, and re-import, the ts size is 3.5Gb and not 9Gb.

                So import into an assm tablespace takes into account the storage clause of the .dmp file, you have to shrink, export, drop, create, import to shrink the tablespace.
                Furthermore, even if space is NOT used in XE (5Gb used after shrinks), XE tells you exceed the 11gb quota.
                • 5. Re: 11g space limit happens when 5g are present?
                  Well, that's not very surprising for me. Either there was a misunderstanding in your training or somebody really made a mistake there.

                  But you could use another approach that you outlined: shrink before creating the initial dump. I'd recommend this anyway, at least if the dump is not just for regular backup purposes.
                  I understand your anger about that way to enforce 11 GB limit, and that you'll have to take some efforts to regain assignable tablespace capacities.
                  But after all, I personally wouldn't like the database to do a limit check based on every row. Imagine how this would slow down the performance! A file size limit on the other hand only needs to be checked when you change it - which happens comparatively seldom. Therefore, I think this quota approach is very reasonable. And I think you can come quite close to actually have 11 GB when you keep some restrictions in mind.