This discussion is archived
5 Replies Latest reply: Jun 30, 2011 2:46 PM by Udo RSS

11g space limit happens when 5g are present?

Alain Newbie
Currently Being Moderated
Hi,

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 11.2.0.2.0 - 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:
"CREATE INDEX "IDX_BATCHGROUPS_FULL" ON "BATCHGROUPS" ("PELIID" , "GROUPNAME"
"" , "STAGEID" , "SUBPRODUCT" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE("
"INITIAL 25296896 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESP"
"ACE "USR" LOGGING"
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?

Regards.
  • 1. Re: 11g space limit happens when 5g are present?
    Lakmal Rajapakse Expert
    Currently Being Moderated
    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.

    thanks
    Lakmal...
  • 2. Re: 11g space limit happens when 5g are present?
    Udo Guru
    Currently Being Moderated
    ...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;
    -Udo
  • 3. Re: 11g space limit happens when 5g are present?
    Alain Newbie
    Currently Being Moderated
    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 TABLESPACE abiec BLOCKSIZE 8192 datafile SIZE 3500M autoextend on next 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO;
    ->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;

    sql:
    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?
    Alain Newbie
    Currently Being Moderated
    Confirmation:
    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?
    Udo Guru
    Currently Being Moderated
    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.

    -Udo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points