This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 23, 2013 2:19 AM by 899401 RSS

unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER

899401 Newbie
Currently Being Moderated
Hi,

unable to extend tablespace error gettion
ORA-01653: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 1024 in tablespace USERS

attempt:
ALTER DATABASE DATAFILE 'C:\app\vshadmin\oradata\orcl01\users01.dbf' AUTOEXTEND ON NEXT 1050m MAXSIZE UNLIMITED;

still getting same error

thanks
  • 1. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    duplicated:
    Re: ORA-1652: unable to extend temp segment by 128 in tablespace

    again we can only give you the same answer:

    - not enough space at your system --> add more disk
    - not enough space in the tablespace --> add new datafile or resize
    - not quota on the tablespace for this user --> give quota to user

    Edited by: Fran on 22-ene-2013 23:27
  • 2. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    633234 Newbie
    Currently Being Moderated
    Hi,

    Can you please provide output for below query for your datafile?

    select file_name, bytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files;

    Regards,
    Amit
  • 3. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    Hi,

    output is

    C:\APP\VSHADMIN\ORADATA\ORCL01\USERS01.DBF     32767.984375     32767.875     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\UNDOTBS01.DBF     2670     2669.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\SYSAUX01.DBF     946.8125     946.75     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\SYSTEM01.DBF     2440     2439.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\EXAMPLE01.DBF     115.1875     115.125     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_MDS.DBF     100     99.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\DEV_BIPLATFORM.DBF     64     63.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\DEV1_BIPLATFORM.DBF     64     63.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\DEV1_MDS.DBF     100     99.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\MES_DW.DBF     200     199.9375     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\MES_DW2.DBF     200     199.9375     NO
    C:\APP\VSHADMIN\ORADATA\ORCL01\MES_DW3.DBF     1891.1875     1891.125     YES
    C:\APP\VSHADMIN\ORADATA\ORCL01\FLA_TBS01.DBF     10     9.9375     YES

    yesterday's error resolved and it was in temp and this is in USERS
    thanks
  • 4. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    yesterday's error resolved and it was in temp and this is in USERS
    same error different tablespace, did you try to do the same but changing the tablespace_name?

    Please post step by step what did you tried to resolve this issue.
  • 5. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    I did the following

    ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\USERS02.DBF' SIZE 1050m autoextend on;
    alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\USERS02.DBF' autoextend on;

    thanks
  • 6. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    ALTER TABLESPACE temp ADD TEMPFILE 'C:\APP\VSHADMIN\ORADATA\ORCL01\USERS02.DBF' SIZE 1050m autoextend on;
    alter database tempfile 'C:\APP\VSHADMIN\ORADATA\ORCL01\USERS02.DBF' autoextend on;
    You must think what are you doing before try to do it. I mean:

    tablespace--> TEMP
    datafile --> USER02.DBF

    should not be on the user tablespace? User tablespace it isn't a temporary tablespace
  • 7. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    633234 Newbie
    Currently Being Moderated
    why dont you add a new datafile under same tablespace instead of keeping it on autoextend?

    As its already 32GB around, you can add new datafile and keep maxsize defined for it.
  • 8. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    can u help me with the statement
    i used

    ALTER DATABASE DATAFILE 'C:\app\vshadmin\oradata\orcl01\users02.dbf' AUTOEXTEND ON NEXT 1050m MAXSIZE UNLIMITED;

    tablespace is users
    thanks

    Edited by: 896398 on Jan 22, 2013 11:59 PM
  • 9. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    incorrect format, please check:

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles002.htm
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm
  • 10. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    i am confused can u help me with the statement

    thanks
  • 11. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    i am confused can u help me with the statement
    confused with what?
    did you read the documents posted? it comes with "how to add datafiles" and "examples"
  • 12. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    I fired below mentioned statement and gets executed successfuly

    ALTER TABLESPACE
    users
    ADD DATAFILE
    'D:\app\USERS06.DBF'
    size 1050m AUTOEXTEND ON

    and assigne to user

    alter user DATAWAREHOUSE quota unlimited on users

    but still same error

    thanks
  • 13. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    Fran Guru
    Currently Being Moderated
    so you add a new datafile on USERS tablespace, give quota to the user on USERS tablespace correctly and get the same error.

    show result of :
    SET SERVEROUTPUT ON
    SET PAGESIZE 1000
    SET LINESIZE 255
    SET FEEDBACK OFF
    
    SELECT     d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Ext Man",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
    FROM       sys.dba_tablespaces d,
               (select
                  tablespace_name,
                  sum(bytes) bytes
                from dba_data_files
                group by tablespace_name) a,
               (select
                  tablespace_name,
                  sum(bytes) bytes
                from dba_free_space
                group by tablespace_name) f
    WHERE      d.tablespace_name = a.tablespace_name(+)
    AND        d.tablespace_name = f.tablespace_name(+)
       AND NOT
       (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
    UNION ALL
    SELECT     d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Ext Man",
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
       TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99,999,999.999') "Used (M)",
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
    FROM       sys.dba_tablespaces d,
               (select
                  tablespace_name,
                  sum(bytes) bytes
                from dba_temp_files
                group by tablespace_name) a,
               (select
                  tablespace_name,
                  sum(bytes_cached) bytes
                from v$temp_extent_pool
                group by tablespace_name) t
    WHERE      d.tablespace_name = a.tablespace_name(+)
    AND        d.tablespace_name = t.tablespace_name(+)
    AND        d.extent_management like 'LOCAL'
    AND        d.contents like 'TEMPORARY'
    ORDER BY "Name";
    /
    
    SET FEEDBACK ON
    and
    select * from dba_role_privs where grantee='DATAWAREHOUSE';
    and
    select * from dba_sys_privs where grantee='DATAWAREHOUSE';
  • 14. Re: unable to extend table INFA_USER2.OPB_DTL_SWIDG_LOG by 128 in tblespce USER
    899401 Newbie
    Currently Being Moderated
    first output,

    ONLINE     DATA     PERMANENT     LOCAL     200.000     .063     0.03
    ONLINE     DEV1_BIPLATFORM     PERMANENT     LOCAL     64.000     44.313     69.24
    ONLINE     DEV1_IAS_TEMP     TEMPORARY     LOCAL     100.000     .000     0.00
    ONLINE     DEV1_MDS     PERMANENT     LOCAL     100.000     5.438     5.44
    ONLINE     DEV_BIPLATFORM     PERMANENT     LOCAL     64.000     44.313     69.24
    ONLINE     DEV_IAS_TEMP     TEMPORARY     LOCAL     100.000     .000     0.00
    ONLINE     DEV_MDS     PERMANENT     LOCAL     100.000     5.438     5.44
    ONLINE     EXAMPLE     PERMANENT     LOCAL     115.188     109.000     94.63
    ONLINE     FLA_TBS1     PERMANENT     LOCAL     10.000     .063     0.63
    ONLINE     MES_DW2     PERMANENT     LOCAL     2,091.188     1,794.188     85.80
    ONLINE     SYSAUX     PERMANENT     LOCAL     947.813     901.000     95.06
    ONLINE     SYSTEM     PERMANENT     LOCAL     2,440.000     2,429.313     99.56
    ONLINE     TEMP     TEMPORARY     LOCAL     3,380.000     862.000     25.50
    ONLINE     TOOLS     PERMANENT     LOCAL     1,050.000     .063     0.01
    ONLINE     UNDOTBS1     UNDO     LOCAL     2,670.000     189.125     7.08
    ONLINE     USERS     PERMANENT     LOCAL     37,556.984     35,822.672     95.38

    2nd output,

    DATAWAREHOUSE     RESOURCE     NO     YES
    DATAWAREHOUSE     SSE_ROLE     NO     YES
    DATAWAREHOUSE     CONNECT     NO     YES
    DATAWAREHOUSE     DBA     NO     YES
1 2 Previous Next

Legend

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