List of index in a oracle table

SQL> select index_name, substr(column_name,1,20) column_name,
2 column_position
3 from dba_ind_columns
4 where upper(table_name) like upper('%&T%')
5 order by 1,3
6 ;

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_ANREQ_DTL_N1 REQ_LINE_ID 1
KNM_ANREQ_DTL_N1 REQ_NO 2
KNM_ANREQ_DTL_U1 REQ_LINE_ID 1
KNM_ANREQ_HDR_N1 DEPT_VALUE 1
KNM_ANREQ_HDR_N1 REQ_NO 2
KNM_ANREQ_HDR_U1 REQ_NO 1
KNM_CHQ_DTL_N1 HDR_ID 1
KNM_CHQ_DTL_N1 CHK_HANDED_OVER_DATE 2
KNM_CHQ_DTL_N1 PAYMENT_ID 3
KNM_EMD_INFO_N1 EMD_ID 1
KNM_EMD_INFO_N1 PO_HEADER_ID 2
KNM_EMD_INFO_N1 ORGANIZATION_ID 3
KNM_EMD_INFO_N1 RECEIPT_NUM 4
KNM_EMD_INFO_U1 EMD_ID 1
KNM_INSP_N1 HDR_ID 1
KNM_INSP_N1 IRR_NO 2
KNM_INSP_N1 INVENTORY_ITEM_ID 3
KNM_INSP_N1 ORGANIZATION_ID 4
KNM_INSP_N2 ORGANIZATION_ID 1
KNM_INSP_N2 INVENTORY_ITEM_ID 2

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_INSP_U1 HDR_ID 1
KNM_INV_REF_DTL_N1 INVREF_ID 1
KNM_INV_REF_DTL_N1 INVREF_LINE_ID 2
KNM_INV_REF_DTL_N1 DED_NAME 3
KNM_INV_REF_DTL_N1 SUBMIT_FLAG 4
KNM_INV_REF_DTL_U1 INVREF_ID 1
KNM_INV_REF_DTL_U1 INVREF_LINE_ID 2
KNM_INV_REF_HDR_N1 INVREF_ID 1
KNM_INV_REF_HDR_N1 PO_HEADER_ID 2
KNM_INV_REF_HDR_N1 VENDOR_ID 3
KNM_INV_REF_HDR_N1 SUBMIT_FLAG 4
KNM_INV_REF_HDR_U1 INVREF_ID 1
KNM_ISSUE_U1 TRANSACTION_NO 1
KNM_ISSUE_U1 ISSUE_ID 2
KNM_ITEMWISE_ONHAND_THR_ID_PK THR_ID 1
KNM_MAT_ASSG_LINE_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_LINE_N1 SL_NO 2
KNM_MAT_ASSG_N1 TRANSACTION_NO 1
KNM_MAT_ASSG_N1 REQUEST_NUM 2
KNM_MAT_CONS_U1 LINE_ID 1
KNM_MAT_ISSUE_DTL_BATCH_N1 MAT_REQUEST_NUM 1

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_MAT_ISSUE_DTL_BATCH_N1 ISSUE_ID 2
KNM_MAT_ISSUE_DTL_BATCH_N1 INVENTORY_ITEM_ID 3
KNM_MAT_ISSUE_DTL_BATCH_N1 ORGANIZATION_ID 4
KNM_MAT_ISSUE_DTL_BATCH_N1 LOT_NO 5
KNM_MAT_REQ_LINE_N1 HEADER_ID 1
KNM_MAT_REQ_LINE_N1 ITEM_ID 2
KNM_MAT_REQ_LINE_N1 LINE_ID 3
KNM_MAT_REQ_LINE_U1 LINE_ID 1
KNM_MAT_REQ_N1 REQUEST_NUM 1
KNM_MAT_REQ_N1 INDENTING_DEPT 2
KNM_MAT_REQ_N1 TRANSACTION_TYPE 3
KNM_MAT_REQ_N1 ORGANIZATION_ID 4
KNM_MAT_REQ_U1 HEADER_ID 1
KNM_MAT_REQ_U1 REQUEST_NUM 2
KNM_MAT_REQ_U1 ORGANIZATION_ID 3
KNM_OLDADV_DTL_N1 BILL_NO 1
KNM_OLDADV_DTL_N1 HDR_ID 2
KNM_OLDADV_DTL_N1 LINE_ID 3
KNM_OLD_ADV_N1 HDR_ID 1
KNM_OLD_ADV_N1 VENDOR_ID 2
KNM_OLD_ADV_STG_N1 SL_NO 1

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_OLD_ADV_STG_N1 LC 2
KNM_OLD_ADV_STG_N1 CHQNO 3
KNM_OLD_ADV_STG_N1 JDATE 4
KNM_OLD_ADV_STG_N1 VCHNO 5
KNM_OLD_ADV_STG_N1 DED_NAME 6
KNM_OLD_ADV_STG_N2 LC 1
KNM_OLD_ADV_STG_N2 SL_NO 2
KNM_OLD_ADV_STG_N2 DED_NAME 3
KNM_OLD_ADV_STG_N2 NAME 4
KNM_OLD_ADV_STG_N2 BILL_NO 5
KNM_PREPAY_DTL_N1 INVOICE_ID 1
KNM_PREPAY_DTL_N1 PREPAY_INVOICE_ID 2
KNM_PREPAY_DTL_N1 ORG_ID 3
KNM_SUPP_OLD_STG_N1 SUPP_BILL_NO 1
KNM_SUPP_OLD_STG_N1 BILL_NO 2
KNM_SUPP_OLD_STG_N1 CHQNO 3
KNM_SUPP_OLD_STG_N1 CHQ_DATE 4
KNM_SUPP_OLD_STG_N1 VOUCHER_NO 5
KNM_TAX_DED_DTL_N1 LINE_ID 1
KNM_TAX_DED_DTL_N1 PO_HEADER_ID 2
KNM_TAX_DED_DTL_N1 INVOICE_ID 3

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_TAX_DED_DTL_N1 DEDUCTION_NAME 4
KNM_TAX_DED_N1 PO_HEADER_ID 1
KNM_TAX_DED_N1 INVOICE_ID 2
KNM_TAX_DED_N1 SUBMITTED_FLAG 3
KNM_TP_SALE_DTL_N1 PO_HEADER_ID 1
KNM_TP_SALE_DTL_N1 LINE_ID 2
KNM_TP_SALE_DTL_N1 HDR_ID 3
KNM_TP_SALE_DTL_N1 VENDOR_ID 4
KNM_TP_SALE_DTL_N1 VENDOR_SITE_ID 5
KNM_TP_SALE_HDR_N1 HDR_ID 1
KNM_TP_SALE_HDR_N1 PO_HEADER_ID 2
KNM_TP_SUP_N1 PO_HEADER_ID 1
KNM_TP_SUP_N1 VENDOR_ID 2
KNM_TP_SUP_N1 VENDOR_SITE_ID 3
KNM_VCH_UQ VOUCHER_NO 1
KNM_VCH_UQ FIN_YEAR 2
KNM_VCH_UQ VENDOR_ID 3
KNM_VOUCHER_UQ VOUCHER_NO 1
KNM_VOUCHER_UQ INVOICE_NUM 2
KNM_VOUCHER_UQ CHQ_NO 3
KNM_WORKS_HDR_N1 WORK_NO 1

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_HDR_N1 EST_HEADER_ID 2
KNM_WORKS_HDR_N1 ORG_ID 3
KNM_WORKS_HDR_N1 SUBMIT_FLAG 4
KNM_WORKS_HDR_N1 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N1 DEPARTMENT 6
KNM_WORKS_HDR_N2 WORK_NO 1
KNM_WORKS_HDR_N2 EST_HEADER_ID 2
KNM_WORKS_HDR_N3 SUBMIT_FLAG 1
KNM_WORKS_HDR_N3 WORK_NO 2
KNM_WORKS_HDR_N3 EST_HEADER_ID 3
KNM_WORKS_HDR_N4 EST_APPROVAL_STATUS 1
KNM_WORKS_HDR_N4 WORK_NAME 2
KNM_WORKS_HDR_N4 TYPE 3
KNM_WORKS_HDR_N5 WORK_NO 1
KNM_WORKS_HDR_N5 DEPARTMENT 2
KNM_WORKS_HDR_N5 BOROUGH 3
KNM_WORKS_HDR_N5 WARD 4
KNM_WORKS_HDR_N5 EST_APPROVAL_STATUS 5
KNM_WORKS_HDR_N6 DEPARTMENT 1
KNM_WORKS_HDR_N6 SUBMIT_FLAG 2
KNM_WORKS_HDR_U1 EST_HEADER_ID 1

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
KNM_WORKS_LINES_N1 EST_HEADER_ID 1
KNM_WORKS_LINES_N1 EST_LINE_ID 2
KNM_WORKS_LINES_N1 LINE_NUM 3
KNM_WORKS_LINES_N1 SOURCE_TYPE 4
KNM_WORKS_LINES_N1 EST_ITEM_ID 5
KNM_WORKS_LINES_N1 ORGANIZATION_ID 6
KNM_WORKS_LINES_U1 EST_LINE_ID 1
KNM_WORKS_LINES_U1 SOURCE_TYPE 2

133 rows selected

SQL>

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 6




Sol : set serveroutput on size 1000000 ( give some extra size)

ORA-01476: divisor is equal to zero

ORA-01476: divisor is equal to zero
Error: function FA_GAINLOSS_UPD_PKG.farboe returned failure
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure
User-Defined Exception
Error: function FA_GAINLOSS_UPD_PKG.fagpdp returned failure


SOLUTION:
Check the setup and also the code.

IO error writing block to file %s (block # %s)

"IO error writing block to file %s (block # %s)"
// *Cause: Device on which the file resides is probably offline
// *Action: Restore access to the device



Solution : -


Disk is full

so remove log and out files from $APPLCSF/

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> !
$ oerr ora 265
00265, 00000, "instance recovery required, cannot set ARCHIVELOG mode"
// *Cause: The database either crashed or was shutdown with the ABORT
// option. Media recovery cannot be enabled because the online
// logs may not be sufficient to recover the current datafiles.
// *Action: Open the database and then enter the SHUTDOWN command with the
// NORMAL or IMMEDIATE option.

ora 00031 session marked for kill

oracle error ora 00031
00031, 00000, "session marked for kill"
// *Cause: The session specified in an ALTER SYSTEM KILL SESSION command
// cannot be killed immediately (because it is rolling back or blocked
// on a network operation), but it has been marked for kill. This
// means it will be killed as soon as possible after its current
// uninterruptable operation is done.
// *Action: No action is required for the session to be killed, but further
// executions of the ALTER SYSTEM KILL SESSION command on this session
// may cause the session to be killed sooner.

ORA-19504: failed to create file

ARC2: Beginning to archive log 2 thread 1 sequence 68308
Creating archive destination LOG_ARCHIVE_DEST_2: '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
Mon Apr 30 14:00:27 2007
Errors in file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc:
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file.






Dump file /oradb/oracleerp/oracleerpdb/9.2.0/admin/KOUSHIKERP_krishna/bdump/oracleerp_arc2_7695.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oradb/oracleerp/oracleerpdb/9.2.0
System name: SunOS
Node name: n1
Release: 5.9
Version: Generic_118558-09
Machine: sun4u
Instance name: KOUSHIKERP
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7695, image: oracle@n1 (ARC2)

*** SESSION ID:(20.1) 2007-04-30 14:00:27.897
Failed to create file '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc' (file not accessible?)
*** 2007-04-30 14:00:27.898
ARC2: Error 19504 Creating archive log file to '/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc'
*** 2007-04-30 14:00:27.898
kcrrfail: dest:2 err:19504 force:0
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-19504: failed to create file "/oracleerpdb/archive2/KOUSHIKERP_1_68308.arc"
ORA-27044: unable to write the header block of file
SVR4 Error: 5: I/O error
Additional information: 1.