Re: ORA-1652: unable to extend temp segment by 512 in tablespace

From: <fitzjarrell_at_cox.net>
Date: 7 Oct 2005 09:56:19 -0700
Message-ID: <1128704179.401671.274170@g43g2000cwa.googlegroups.com>

Comments embedded.
suresh wrote:
> I created a database called desksite and a user as
>
> create user insite_adm identified by testing

You are missing much in this create user statement, such as default tablespace, temp tablespace and quota.

> grant CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM to
> insite_adm;
>

Nothing wrong here.

>
> create tablespace insite
> datafile 'E:\oracle\oradata\insite\insite_data.dbf'
> size 100M
> extent management local uniform size 4M
>

Nothing wrong here.

>
> CREATE TABLE insite_adm.account (
> account_id varchar2 (24) NOT NULL,
> supplier_code varchar2 (4) NOT NULL,
> name varchar2 (100) NOT NULL,
> account_code varchar2 (100) NOT NULL,
> description varchar2 (1000) NOT NULL,
> active_flag number(1) NOT NULL
> )
> ;
>
> CREATE TABLE insite_adm.account_site (
> account_id varchar2 (24) NOT NULL,
> site_id varchar2 (24) NOT NULL
> )
> ;
>
> First table gets created and looks like it takes all the space,
> because when I look at user_segments; I get
>
> segment_name|tablespace_name|bytes|blocks|extents|initial_extent|pct_increase|freelists
> ACCOUNT|INSITE|4194304|512|1|4194304|0|1
>

How can you state that 'it takes all the space'??? I see one extent, at 4M, in a 100M tablespace. Subtract 64K for the extent bitmap and you still have over 95M left to create tables.

>
> I am getting this error when I try to create tables from insite_adm,
>
> ORA-01658: unable to create INITIAL extent for segment in tablespace
> INSITE
>

I don't:

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Oct 7 16:30:59 2005

SQL> connect / as sysdba
Connected.
SQL> create tablespace insite
2 datafile '/oraunix/oradata/dev9/insite_data.dbf' 3 size 100M
4 extent management local uniform size 4M 5 /

Tablespace created.

SQL>
SQL> create user insite_adm identified by testing default tablespace insite temporary tablespace temp quota unlimited on insite;

User created.

SQL>
SQL> grant CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM to 2 insite_adm;

Grant succeeded.

SQL>
SQL> connect insite_adm/testing
Connected.
SQL>
SQL> CREATE TABLE insite_adm.account (

  2      account_id varchar2 (24) NOT NULL,
  3      supplier_code varchar2 (4) NOT NULL,
  4      name varchar2 (100) NOT NULL,
  5      account_code varchar2 (100) NOT NULL,
  6      description varchar2 (1000) NOT NULL,
  7      active_flag number(1) NOT NULL

8 )
9 ;

Table created.

SQL>
SQL> CREATE TABLE insite_adm.account_site (

  2      account_id varchar2 (24) NOT NULL,
  3      site_id varchar2 (24) NOT NULL

4 )
5 ;

Table created.

SQL>
>
> Please anyone can help me?

It may be your release of Oracle causing your problems. You really need to verify the size of your tablespace in DBA_DATA_FILES:

SQL> select file_name, bytes
2 from dba_data_files
3 where tablespace_name = 'INSITE';

FILE_NAME

BYTES

/oraunix/oradata/dev9/insite_data.dbf
104857600

SQL> Also start looking into the DBA_FREE_SPACE view:

SQL> select tablespace_name, file_id, bytes, blocks 2 from dba_Free_Space
3 where tablespace_name = 'INSITE';

TABLESPACE_NAME                   FILE_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ----------
INSITE                                 12   92274688      22528

SQL> As you can see there is roughly 9iM free in the INSITE tablespace after the two tables are created. Querying DBA_SEGMENTS:

SQL> select owner, segment_name, segment_type, bytes, blocks, extents 2 from dba_Segments
3 where tablespace_name = 'INSITE';

OWNER

SEGMENT_NAME

SEGMENT_TYPE BYTES BLOCKS EXTENTS

------------------ ---------- ---------- ----------

INSITE_ADM
ACCOUNT

TABLE                 4194304       1024          1

INSITE_ADM
ACCOUNT_SITE

TABLE                 4194304       1024          1

SQL> proves each table has but one extent, at 4M.

Do the research and post your findings. Until you do any response is simply speculation.

David Fitzjarrell Received on Fri Oct 07 2005 - 11:56:19 CDT