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
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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