|
Code
|
Message
|
|
ORA-00001
|
ORA-00001 unique constraint violated
|
|
ORA-00609
|
ORA-00609:
could not attach to incoming connection
|
|
ORA-00904
|
ORA-00904 invalid identifier
|
|
ORA-00907
|
ORA-00907:
missing right parenthesis
|
|
ORA-00911
|
ORA-00911 invalid character
|
|
ORA-00933
|
ORA-00933
SQL command not properly ended
|
|
ORA-00936
|
ORA-00936: missing expression
|
|
ORA-00937
|
ORA-00937:
not a single-group group function
|
|
ORA-00942
|
ORA-00942 table or view does not exist
|
|
ORA-00972
|
ORA-00972
identifier is too long
|
|
ORA-00979
|
ORA-00979 not a group by expression
|
|
ORA-01017
|
ORA-01017
invalid username/password; logon denied
|
|
ORA-01031
|
ORA-01031 insufficient privileges
|
|
ORA-01400
|
ORA-01400:
cannot insert null into (string)
|
|
ORA-01722
|
ORA-01722 invalid number
|
|
ORA-06502
|
ORA-06502
PLSQL numeric or value error
|
|
ORA-06512
|
ORA-06512 at line
|
|
ORA-06550
|
ORA-06550
line N column N
|
|
ORA-12154
|
ORA-12154 TNS Connection Identifier
|
|
ORA-12514
|
ORA-12514:
TNS:listener does not currently know or service requested in connect
descriptor
|
|
ORA-20000
|
none
|
|
ORA-609
|
ORA-609:
opiodr aborting process unknown ospid
|
Oracle has many features that makes it popular and thereby makes it as the world's largest enterprise Solution Provider. Oracle comes with new versions with new features implemented in new version while the features of earlier versions still being maintained. One important aspect is Oracle databases tend to be backwards compatible.
Saturday, November 12, 2016
ORACLE ERROR CODES
Wednesday, October 12, 2016
FRM-30187: Size of CHAR column in record group must be between 1 and 2000.
FRM-30187: Size of CHAR column in record group must be between 1 and 2000. Record Group EMPLOYEE
FRM-30187: Size of CHAR column in record group must be between 1 and 2000.
Record Group EMPLOYEESolution:
Go to the property fo RECORDGROUP named EMPLOYEE and there is one property called Column Specification there is one columns will be having length greater then 2000 there set it to 2000 which is maximum value. Then it will work.
Sunday, October 9, 2016
Count Number of rows of each table with table name
SELECT table_name,
TO_NUMBER
(EXTRACTVALUE
(XMLTYPE (DBMS_XMLGEN.getxml ( 'select count(*) c from '
|| table_name
)
),
'/ROWSET/ROW/C'
)
) COUNT
FROM user_tables
ORDER BY table_name;
TO_NUMBER
(EXTRACTVALUE
(XMLTYPE (DBMS_XMLGEN.getxml ( 'select count(*) c from '
|| table_name
)
),
'/ROWSET/ROW/C'
)
) COUNT
FROM user_tables
ORDER BY table_name;
Find Missing Indexes of Schema
SELECT 'CREATE INDEX '
|| owner
|| '.'
|| REPLACE (constraint_name, 'FK_', 'IX_')
|| ' ON '
|| owner
|| '.'
|| table_name
|| ' ('
|| col_list
|| ') TABLESPACE ' || :table_space ||';' indx
FROM (SELECT cc.owner, cc.table_name, cc.constraint_name,
MAX (DECODE (POSITION,
1, '"' || SUBSTR (column_name, 1, 30) || '"',
NULL
)
)
|| MAX (DECODE (POSITION,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_constraints dc, dba_cons_columns cc
WHERE dc.owner = cc.owner
AND dc.constraint_name = cc.constraint_name
AND dc.constraint_type = 'R'
AND UPPER (dc.owner) = UPPER (:owner)
GROUP BY cc.owner, cc.table_name, cc.CONSTRAINT_NAME
) con
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT table_owner, table_name,
MAX (DECODE (column_position,
1, '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_ind_columns
WHERE UPPER (table_owner) = UPPER (:owner)
GROUP BY table_owner, table_name, index_name) col
WHERE con.owner = col.table_owner
AND con.table_name = col.table_name
AND con.col_list =
SUBSTR (col.col_list, 1, LENGTH (con.col_list)));
|| owner
|| '.'
|| REPLACE (constraint_name, 'FK_', 'IX_')
|| ' ON '
|| owner
|| '.'
|| table_name
|| ' ('
|| col_list
|| ') TABLESPACE ' || :table_space ||';' indx
FROM (SELECT cc.owner, cc.table_name, cc.constraint_name,
MAX (DECODE (POSITION,
1, '"' || SUBSTR (column_name, 1, 30) || '"',
NULL
)
)
|| MAX (DECODE (POSITION,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_constraints dc, dba_cons_columns cc
WHERE dc.owner = cc.owner
AND dc.constraint_name = cc.constraint_name
AND dc.constraint_type = 'R'
AND UPPER (dc.owner) = UPPER (:owner)
GROUP BY cc.owner, cc.table_name, cc.CONSTRAINT_NAME
) con
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT table_owner, table_name,
MAX (DECODE (column_position,
1, '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_ind_columns
WHERE UPPER (table_owner) = UPPER (:owner)
GROUP BY table_owner, table_name, index_name) col
WHERE con.owner = col.table_owner
AND con.table_name = col.table_name
AND con.col_list =
SUBSTR (col.col_list, 1, LENGTH (con.col_list)));
Find Missing Indexes of Schema
SELECT 'CREATE INDEX '
|| owner
|| '.'
|| REPLACE (constraint_name, 'FK_', 'IX_')
|| ' ON '
|| owner
|| '.'
|| table_name
|| ' ('
|| col_list
|| ') TABLESPACE ' || :table_space ||';' indx
FROM (SELECT cc.owner, cc.table_name, cc.constraint_name,
MAX (DECODE (POSITION,
1, '"' || SUBSTR (column_name, 1, 30) || '"',
NULL
)
)
|| MAX (DECODE (POSITION,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_constraints dc, dba_cons_columns cc
WHERE dc.owner = cc.owner
AND dc.constraint_name = cc.constraint_name
AND dc.constraint_type = 'R'
AND UPPER (dc.owner) = UPPER (:owner)
GROUP BY cc.owner, cc.table_name, cc.CONSTRAINT_NAME
) con
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT table_owner, table_name,
MAX (DECODE (column_position,
1, '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_ind_columns
WHERE UPPER (table_owner) = UPPER (:owner)
GROUP BY table_owner, table_name, index_name) col
WHERE con.owner = col.table_owner
AND con.table_name = col.table_name
AND con.col_list =
SUBSTR (col.col_list, 1, LENGTH (con.col_list)));
|| owner
|| '.'
|| REPLACE (constraint_name, 'FK_', 'IX_')
|| ' ON '
|| owner
|| '.'
|| table_name
|| ' ('
|| col_list
|| ') TABLESPACE ' || :table_space ||';' indx
FROM (SELECT cc.owner, cc.table_name, cc.constraint_name,
MAX (DECODE (POSITION,
1, '"' || SUBSTR (column_name, 1, 30) || '"',
NULL
)
)
|| MAX (DECODE (POSITION,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (POSITION,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_constraints dc, dba_cons_columns cc
WHERE dc.owner = cc.owner
AND dc.constraint_name = cc.constraint_name
AND dc.constraint_type = 'R'
AND UPPER (dc.owner) = UPPER (:owner)
GROUP BY cc.owner, cc.table_name, cc.CONSTRAINT_NAME
) con
WHERE NOT EXISTS (
SELECT 1
FROM (SELECT table_owner, table_name,
MAX (DECODE (column_position,
1, '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
2, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
3, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
4, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
5, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
6, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
7, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
8, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
9, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
)
|| MAX (DECODE (column_position,
10, ', '
|| '"'
|| SUBSTR (column_name, 1, 30)
|| '"',
NULL
)
) col_list
FROM dba_ind_columns
WHERE UPPER (table_owner) = UPPER (:owner)
GROUP BY table_owner, table_name, index_name) col
WHERE con.owner = col.table_owner
AND con.table_name = col.table_name
AND con.col_list =
SUBSTR (col.col_list, 1, LENGTH (con.col_list)));
Subscribe to:
Posts (Atom)