Forum Stats

  • 3,733,966 Users
  • 2,246,849 Discussions
  • 7,856,954 Comments

Discussions

Tablespace Query

Prateek Agarwal-Oracle
Prateek Agarwal-Oracle Member Posts: 142 Employee

I recently created a DB with ABC_TBS with 2GB of SIZE and import data into few table that consumed 10MB of storage in other tablespace USER

Now I was trying to migrate the data to a new table X (ABC_TBS tablespace) with OLD table A (ABC_TBS tablespace) + B (USER tablespace) with outer join. The create statement went fine.

On INSERT the Tablespace was used completely like 2GB, and gave a error saying

Error report:
SQL Error: ORA-01658: unable to create INITIAL extent for segment in tablespace CTB_TBS
01658. 00000 -  "unable to create INITIAL extent for segment in tablespace %s"
*Cause:    Failed to find sufficient contiguous space to allocate INITIAL
           extent for segment being created.
*Action:   Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
           tablespace or retry with a smaller value for INITIAL
Rollback

When I check the tablespace size it says, almost used, but before migrating it had like 2000 MB free

TablespaceUSED (MB)FREE (MB)TOTAL(MB)Pct.Free
ABC_TBS2044420480

Now when I see the USAGE of INDEX and TABLE in ABC_TBS its hardly 7 MB

OWNERTABLE_NAMEUSED(MB)TABLESPACE
ABCTABLE12ABC_TBS
ABCTABLE1_N1_IND2ABC_TBS
ABCTABLE1_N2_IND2ABC_TBS
ABCTABLE2_U1_IND1ABC_TBS
ABCTABLE20ABC_TBS
ABCTABLE1_U1_IND0ABC_TBS
ABCTABLE1_N3_IND0ABC_TBS
ABCTABLE30ABC_TBS

Now my doubt is, if the data never got inserted to NEW table, how did 2000 MB of space get consumed ? How can I reclaim it ? I have no problem adding new DATAFILE, but my mystery is where did the 2GB go ???

Tagged:

Best Answer

Answers

  • Sadly you failed to share the create table statement and the insert table, so it will never be clear why Oracle wanted to create a *contiguous* initial extent of 2000 Mb.

    Nor do we know how the tablespace was configured (autoallocate, uniform).

    I'm going to search for my crystal ball.

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • Prateek Agarwal-Oracle
    Prateek Agarwal-Oracle Member Posts: 142 Employee
    edited November 2013

    Will the below script help ?

    CREATE TABLE "TABLE3"
      (
        "COL1"  NUMBER NOT NULL ENABLE,
        "COL2"  NUMBER NOT NULL ENABLE,
        "COL3"  NUMBER NOT NULL ENABLE,
        "COL4"  VARCHAR2(80 CHAR) NOT NULL ENABLE,
        "COL5"  NUMBER NOT NULL ENABLE,
        "COL6"  VARCHAR2(30 CHAR) NOT NULL ENABLE,
        "COL7"  VARCHAR2(30 CHAR),
        "COL8"  VARCHAR2(50 CHAR),
        "COL9"  VARCHAR2(240 CHAR),
        "COL10" VARCHAR2(240 CHAR),
        "COL11" VARCHAR2(240 CHAR),
        "COL12" VARCHAR2(30 CHAR),
        "COL13" VARCHAR2(4000 CHAR),
        "COL14" NUMBER,
        "COL15" NUMBER,
        "COL16" VARCHAR2(1 CHAR),
        "COL17" VARCHAR2(30 CHAR),
        "COL18" VARCHAR2(50 CHAR),
        "COL19" VARCHAR2(240 CHAR),
        "COL20" VARCHAR2(240 CHAR),
        "COL21" VARCHAR2(240 CHAR)
      )
      PARALLEL 32 NOLOGGING COMPRESS FOR OLTP PARTITION BY HASH
      (
        COL4
      )
      PARTITIONS 1024 ;
    ALTER TABLE TABLE3 MODIFY COL4 DEFAULT 'EMPTY';
    ALTER TABLE TABLE3 ENABLE ROW MOVEMENT;
    INSERT
      /*+ APPEND PARALLEL(32) */
    INTO TABLE3
      (SELECT TAB.*
        FROM
          (SELECT A.COL1,
            A.COL2,
            A.COL3,
            X.COL4,
            A.COL5,
            A.COL6,
            A.COL7,
            A.COL8,
            A.COL9,
            A.COL10,
            A.COL11,
            A.COL12,
            A.COL13,
            A.COL14,
            A.COL15,
            A.COL16,
            A.COL17,
            A.COL18,
            A.COL19,
            A.COL20,
            A.COL21
          FROM TABLEA A,
           TABLEB X
          WHERE A.COL1 = X.COL1
          ) TAB
      );
    COMMIT;
    
    
  • Prateek Agarwal-Oracle
    Prateek Agarwal-Oracle Member Posts: 142 Employee
    Accepted Answer

    I think I got my answer, It was the table_partition consuming the space.

    Thanks All.

This discussion has been closed.