Database A opens the audit, but database B does not open the audit when it is imported to database B, and database A does not have the SYSTEM. PROC_AUDIT object. This article describes how to solve this problem. For more information, see
Database A opens the audit, but database B does not open the audit when it is imported to database B, and database A does not have the SYSTEM. PROC_AUDIT object. This article describes how to solve this problem. For more information, see
When you export the data of USERNAME user of data A and then import it to USERNAME in database B, the following problems occur during table data query by USERNAME User:
ORA-06550: 1st rows, 7th columns:
PLS-00201: the identifier 'system. PROC_AUDIT 'must be declared'
ORA-06550: 1st rows, 7th columns:
PL/SQL: Statement ignored
This problem occurs because Database A has opened the audit, but database B has not opened the audit when it is imported to database B, and the database does not have the SYSTEM. PROC_AUDIT object.
The problem solving process is as follows::
1) log on to the database as sysdba:
D: \ Users \ Administrator> sqlplus/as sysdba
2) display the 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 audit.
SQL> alter system set audit_sys_operations = TRUE scope = spfile;
The system has been changed.
4) check the new status and check whether the audit operation is not enabled. you need to 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 has been closed.
The database has been detached.
The ORACLE routine has been disabled.
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 has been loaded.
The database has been opened.
6) check the audit status again. The Audit is enabled..
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;
Connected.
7) query table data. the error persists, but the error is changed to SYSTEM. PROC_AUDIT.:
SQL> select * from configurationhot;
Select * from configurationhot
*
Row 3 has an error:
ORA-06550: 1st rows, 14th columns:
PLS-00905: the object SYSTEM. PROC_AUDIT is invalid
ORA-06550: 1st rows, 7th columns:
PL/SQL: Statement ignored
8) authorize the object SYSTEM. PROC_AUDIT to USERNAME.
SQL> grant execute on SYSTEM. PROC_AUDIT TO USERNAME;
Authorization successful.
9)Log on again with USERNAME and execute the data table query. the SYSTEM. PROC_AUDIT object is prompted to be invalid. because the object has been authorized, this situation may be caused by an object error.
SQL> conn USERNAME/PWDXXXXXX;
Connected.
SQL> select * from TABLENAME;
Select * from TABLENAME *
Row 3 has an error:
ORA-06550: 1st rows, 14th columns:
PLS-00905: the object SYSTEM. PROC_AUDIT is invalid
ORA-06550: 1st rows, 7th columns:
PL/SQL: Statement ignored
10)Find the SYSTEM. PROC_AUDIT stored procedure and find a compilation error. the SYSTEM. v _ $ SQL, v $ SQL _bind_capture, and sys. v _ $ session is none.
Valid identifier, because the above object exists and can be queried, it is suspected that it is a permission setting problem, do the following authorization:
SQL> conn/as sysdba;
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 SYSTEM. PROC_AUDIT is compiled, log on to the database with USERNAME and check that the table data recovery is normal. The problem is solved.
In order to verify whether the audit is enabled when an error is exported, but the audit is not enabled during import, close the audit again:
SQL> alter system set audit_sys_operations = FALSE scope = spfile;
The system has been changed.
SQL> restart;
SP2-0042: Unknown command "restart"-ignore for other lines.
SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
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 has been loaded.
The database has been opened.
12) after the database is restarted, it is normal to query data with USERNAME user login.
