Oracle failure-statement failure

A statement failure occurs when the SQL statement is processed. When a statement fails, Oracle server automatically rolls back the failed SQL statement and returns the error code and message to the user.

1. application logic error

Application logic errors mainly refer to SQL statement compiling errors. For example, if the database is not connected, execute an SQL statement. Developers should resolve such errors, rather than DBA's responsibilities.

Example:

SQL * Plus: Release 10.2.0.1.0-production on Thursday August 21 11:51:02 2008

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> select * from EMP where rownum <2;
SP2-0640: not connected
SQL>

Solution: connect to the Oracle database with the correct user and then execute the corresponding SQL statement.

SQL> connect Scott/tiger @ orcl
Connected.
SQL> set pagesize 50
SQL> set linesize 600
SQL> select * from EMP;

Empno ename job Mgr hiredate Sal comm deptno
-----------------------------------------------------------------------------------
7369 Smith clerk 7902-12-80 800 20

SQL>

 

2. Enter the error data.

Input error data refers to an error in which the user attempts to enter a violation rule.

Example:

SQL> ALTER TABLE EMP add constraint chk_sal check (SAL> = 800 and Sal <= 5000 );

The table has been changed.

SQL> Update EMP set sal = 100 Where empno = 7788;
Update EMP set sal = 100 Where empno = 7788
*
Row 3 has an error:
ORA-02290: violation of check constraints (Scott. chk_sal)

Solution: carefully review the data to be entered, and then enter the correct data to re-execute the SQL statement.

SQL> Update EMP set sal = 1000 where empno = 7788;

1 row updated.

SQL>

3To perform unauthorized operations.

An unauthorized operation is an attempt by the DB user to perform an unauthorized db operation.

Example:

SQL * Plus: Release 10.2.0.1.0-production on Thursday August 21 13:05:46 2008

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> connect/As sysdba
Connected.
SQL> create user test identified by test;

The user has been created.

SQL> grant connect to test;

Authorization successful.

SQL> connect test/test @ orcl
Connected.
SQL> select * from Scott. EMP where rownum <2;
Select * from Scott. EMP where rownum <2
*
Row 3 has an error:
ORA-00942: Table or view does not exist

Solution: grant the required permissions to the user.

SQL> connect Scott/tiger @ orcl
Connected.
SQL> grant select on EMP to test;

Authorization successful.

SQL> conn test/test @ orcl
Connected.
SQL> select empno, ename from Scott. EMP where rownum <2;

Empno ename
--------------------
7369 Smith

 

4, Exceeds the space quota

The excess space quota indicates that the actual space required by the user in a specific tablespace exceeds the maximum space occupied by the user in the tablespace.

Example:

SQL> Conn/As sysdba
Connected.
SQL> alter user test quota 3 K on users;

The user has changed.

SQL> grant create any table to test;

Authorization successful.

SQL> conn test/test @ orcl;
Connected.
SQL> Create Table tablea
2 (
3 cola varchar2 (50 ),
4 COLB varchar2 (100)
5)
6 tablespace users;
Create Table tablea
*
Row 3 has an error:
ORA-01536: exceeds the space limit for tablespace 'users'

Solution: log on to the database as a DBA, assign more space quota to the user using the alter user language name, and re-execute.

SQL> Conn/As sysdba
Connected.
SQL> alter user test quota 50 m on users;

The user has changed.

SQL> conn test/test @ orcl
Connected.
SQL> Create Table tablea
2 (
3 cola varchar2 (50 ),
4 COLB varchar2 (100)
5)
6 tablespace users;

The table has been created.

SQL>

5The tablespace does not have enough space.

Insufficient space in the tablespace is an error caused by insufficient space in the tablespace when the SQL operation requires space allocation from the tablespace.

Example:

SQL> connectNew/New @ orcl
Connected.
SQL> grant select on stock to test;

Authorization successful.

SQL> connect test/test @ orcl
Connected.

SQL> insert/* + append */into tablea value (select cola, COLB from new. Temp );
Insert/* + append */into tablea value (select cola, COLB from new. Temp)
*
Row 3 has an error:
ORA-01653: Table Test. tablea cannot be extended through 128 (in tablespace Users)

Solution: Expand the tablespace.

SQL> Conn/As sysdba
Connected.
SQL> alter tablespace "users" add datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ users02.dbf' size
500 m;

The tablespace has been changed.

SQL> insert/* + append */into tablea value (select cola, COLB from new. Temp );

You have created 8045614 rows.

SQL>