Oralce data import presents the of ES2en.PROC_ES4en problem handling method
- 2020-12-20 03:50:26
- OfStack
When the USERNAME user of A data is exported and imported into USERNAME database, the following problems occur when performing table data query under USERNAME user:
ORA-06550: Row 1, column 7:
PLS-00201: Identifier 'SYSTEM. PROC_AUDIT' must be declared
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
This problem occurs because the A database has an audit open and when imported into the B database, the B database audit is not open and there are no SYSTEM.es21EN_ES22en objects in the database.
The problem solving process is as follows:
1) Login the database with sysdba:
D:\Users\Administrator > sqlplus / as sysdba
2) Display current audit parameters:
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN\ORC
L\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
3) Enable auditing.
SQL > alter system set audit_sys_operations=TRUE scope=spfile;
The system has changed.
4) Check the new status, or show that the audit operation is not open, and restart the database:
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN
L\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
5) Restart the database
SQL > shutdown immediate
The database is closed.
The database has been unloaded.
The ORACLE routine has been closed.
SQL > startup
The ORACLE routine has been started.
Total System Global Area 3056513024 bytes
Fixed Size 2179656 bytes
Variable Size 1644170680 bytes
Database Buffers 1392508928 bytes
Redo Buffers 17653760 bytes
The database is loaded.
The database is open.
6) Re-check the audit status. The audit has been opened.
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN
L\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB
SQL > conn USERNAME/PWDXXXXXX;
The connected.
7) Query table data, error still, but the error changed to ES127en. PROC_AUDIT invalid:
SQL > select * from corporationhot;
select * from corporationhot
*
Error in line 1:
ORA-06550: Row 1, column 14:
PLS-00905: Object ES143en. PROC_AUDIT is not valid
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
8) Authorize the object SYSTEM.PROC_ES154en to USERNAME.
SQL > grant execute on SYSTEM.PROC_AUDIT TO USERNAME;
Authorization successful.
9) Log in with USERNAME again, perform data table query, prompt object ES164en.es165EN_ES166en invalid; Because the object is already authorized, this situation may be due to an error in the object.
SQL > conn USERNAME/PWDXXXXXX;
The connected.
SQL > select * from TABLENAME;
select * from TABLENAME *
Error in line 1:
ORA-06550: Row 1, column 14:
PLS-00905: Object ES183en. PROC_AUDIT is invalid
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
10) Find SYSTEM. PROC_AUDIT stored procedure, find compilation error, prompt ES195en. v_$sql,v$sql_bind_capture, ES202en. v_$session is none
Valid identifier. Since the above objects exist and can be queried, it is suspected that it is a problem of permission setting. Authorization is made as follows:
SQL > conn / as sysdba;
The connected.
SQL > grant all on sys.v_$sql to system
2 ;
Authorization successful.
SQL > grant all on v$sql_bind_capture to system;
Authorization successful.
SQL > grant all on sys.v_$session to system;
Authorization successful.
11) After the compilation of ES230en.es231EN_ES232en is passed, log in the database with USERNAME, check the table data to restore normal, and solve the problem.
In order to verify whether the error is caused by the audit being opened when exporting and not opened when importing, the audit is closed again:
SQL > alter system set audit_sys_operations=FALSE scope=spfile;
The system has changed.
SQL > restart;
SP2-0042: unknown command "restart" - the remaining lines are ignored.
SQL > shutdown immediate;
The database is closed.
The database has been unloaded.
The ORACLE routine has been closed.
SQL > startup;
The ORACLE routine has been started.
Total System Global Area 3056513024 bytes
Fixed Size 2179656 bytes
Variable Size 1644170680 bytes
Database Buffers 1392508928 bytes
Redo Buffers 17653760 bytes
The database is loaded.
The database is open.
12) After the database is restarted, the query data logged in as USERNAME user is normal.
ORA-06550: Row 1, column 7:
PLS-00201: Identifier 'SYSTEM. PROC_AUDIT' must be declared
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
This problem occurs because the A database has an audit open and when imported into the B database, the B database audit is not open and there are no SYSTEM.es21EN_ES22en objects in the database.
The problem solving process is as follows:
1) Login the database with sysdba:
D:\Users\Administrator > sqlplus / as sysdba
2) Display current audit parameters:
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN\ORC
L\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
3) Enable auditing.
SQL > alter system set audit_sys_operations=TRUE scope=spfile;
The system has changed.
4) Check the new status, or show that the audit operation is not open, and restart the database:
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN
L\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
5) Restart the database
SQL > shutdown immediate
The database is closed.
The database has been unloaded.
The ORACLE routine has been closed.
SQL > startup
The ORACLE routine has been started.
Total System Global Area 3056513024 bytes
Fixed Size 2179656 bytes
Variable Size 1644170680 bytes
Database Buffers 1392508928 bytes
Redo Buffers 17653760 bytes
The database is loaded.
The database is open.
6) Re-check the audit status. The audit has been opened.
SQL > show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
audit_file_dest string J:\APP\ADMINISTRATOR\ADMIN
L\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB
SQL > conn USERNAME/PWDXXXXXX;
The connected.
7) Query table data, error still, but the error changed to ES127en. PROC_AUDIT invalid:
SQL > select * from corporationhot;
select * from corporationhot
*
Error in line 1:
ORA-06550: Row 1, column 14:
PLS-00905: Object ES143en. PROC_AUDIT is not valid
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
8) Authorize the object SYSTEM.PROC_ES154en to USERNAME.
SQL > grant execute on SYSTEM.PROC_AUDIT TO USERNAME;
Authorization successful.
9) Log in with USERNAME again, perform data table query, prompt object ES164en.es165EN_ES166en invalid; Because the object is already authorized, this situation may be due to an error in the object.
SQL > conn USERNAME/PWDXXXXXX;
The connected.
SQL > select * from TABLENAME;
select * from TABLENAME *
Error in line 1:
ORA-06550: Row 1, column 14:
PLS-00905: Object ES183en. PROC_AUDIT is invalid
ORA-06550: Row 1, column 7:
PL/SQL: Statement ignored
10) Find SYSTEM. PROC_AUDIT stored procedure, find compilation error, prompt ES195en. v_$sql,v$sql_bind_capture, ES202en. v_$session is none
Valid identifier. Since the above objects exist and can be queried, it is suspected that it is a problem of permission setting. Authorization is made as follows:
SQL > conn / as sysdba;
The connected.
SQL > grant all on sys.v_$sql to system
2 ;
Authorization successful.
SQL > grant all on v$sql_bind_capture to system;
Authorization successful.
SQL > grant all on sys.v_$session to system;
Authorization successful.
11) After the compilation of ES230en.es231EN_ES232en is passed, log in the database with USERNAME, check the table data to restore normal, and solve the problem.
In order to verify whether the error is caused by the audit being opened when exporting and not opened when importing, the audit is closed again:
SQL > alter system set audit_sys_operations=FALSE scope=spfile;
The system has changed.
SQL > restart;
SP2-0042: unknown command "restart" - the remaining lines are ignored.
SQL > shutdown immediate;
The database is closed.
The database has been unloaded.
The ORACLE routine has been closed.
SQL > startup;
The ORACLE routine has been started.
Total System Global Area 3056513024 bytes
Fixed Size 2179656 bytes
Variable Size 1644170680 bytes
Database Buffers 1392508928 bytes
Redo Buffers 17653760 bytes
The database is loaded.
The database is open.
12) After the database is restarted, the query data logged in as USERNAME user is normal.