Oralce data import troubleshooting (system. proc_audit and Audit)

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 solution 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 whether the new status shows that 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. The error is invalid:
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 run the Data Table query. The system. proc_audit object is invalid.
This may be because the object has an 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 indicates 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 log on with username to query data.

 

 

PS: if you do not need audit, a simpler method is to use the System user to create a proc_audit stored procedure without doing anything, and grant the permission to the user.