TriniDBA

This documentation shows the SQL*Plus output for resolving the ORA-01654 error.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:Documents and Settingsoracle>f:
F:>cd oraclePRODdbtech_st10.2.0
F:oraclePRODdbtech_st10.2.0>PROD_mainfinsrv1.cmd
ECHO is off.
ECHO is off.
Thu 11/12/2009 08:04 PM
PROD_mainfinsrv1.cmd exiting with status 0
F:oraclePRODdbtech_st10.2.0>cd adminPROD_mainfinsrv1bdump
F:oraclePRODdbtech_st10.2.0adminPROD_mainfinsrv1bdump>tail -40 alert_prod.log
Thu Nov 12 19:54:51 2009
ORA-1654: unable to extend index APPLSYS.FND_CRM_HISTORY_U2 by 16 in tablespace
APPS_TS_TX_IDX
Thu Nov 12 19:55:21 2009
ORA-1654: unable to extend index APPLSYS.FND_CRM_HISTORY_U2 by 16 in tablespace
APPS_TS_TX_IDX
F:oraclePRODdbtech_st10.2.0adminPROD_mainfinsrv1bdump>sqlplus apps/apps
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Nov 12 20:05:07 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> desc dba_indexes
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
SQL> select owner, index_name, index_type, tablespace_name, initial_extent, next_extent,
min_Extents, max_extents from dba_indexes where index_name = 'FND_CRM_HISTORY_U2';
OWNER INDEX_NAME
------------------------------ ------------------------------
INDEX_TYPE TABLESPACE_NAME INITIAL_EXTENT
--------------------------- ------------------------------ --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
----------- ----------- -----------
APPLSYS FND_CRM_HISTORY_U2
NORMAL APPS_TS_TX_IDX 131072
131072 1 2147483645
SQL> DESC DBA_DATA_FILES
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'APPS_TS_TX_IDX';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
F:ORACLEPRODDBAPPS_STDATAA_TXN_IND05.DBF
APPS_TS_TX_IDX 1273
F:ORACLEPRODDBAPPS_STDATAA_TXN_IND04.DBF
APPS_TS_TX_IDX 1287
F:ORACLEPRODDBAPPS_STDATAA_TXN_IND03.DBF
APPS_TS_TX_IDX 1577
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
F:ORACLEPRODDBAPPS_STDATAA_TXN_IND02.DBF
APPS_TS_TX_IDX 1200
F:ORACLEPRODDBAPPS_STDATAA_TXN_IND01.DBF
APPS_TS_TX_IDX 1200
SQL> ALTER TABLESPACE APPS_TS_TX_IDX
2 ADD DATAFILE 'F:ORACLEPRODDBAPPS_STDATAA_TXN_IND06.DBF' SIZE 1200M;
ALTER TABLESPACE APPS_TS_TX_IDX
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> CONNECT SYSTEM/MANAGER
Connected.
SQL> L
1 ALTER TABLESPACE APPS_TS_TX_IDX
2* ADD DATAFILE 'F:ORACLEPRODDBAPPS_STDATAA_TXN_IND06.DBF' SIZE 1200M
SQL> R
1 ALTER TABLESPACE APPS_TS_TX_IDX
2* ADD DATAFILE 'F:ORACLEPRODDBAPPS_STDATAA_TXN_IND06.DBF' SIZE 1200M
Tablespace altered.
SQL> DISC
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> EXIT
F:oraclePRODdbtech_st10.2.0adminPROD_mainfinsrv1bdump>