How all of you are doing well & safe.
Today I was reading some documents referring new DB enhancements in Oracle 18C & found we can do Oracle’s AUDIT table exports using datapump.
I remember some of links in Oracle MOS/Docs mentioning, EXPDP can’t be used to export any of the objects under SYS. You need to use traditional exp commands for the same.
As EXPDP operation for SYS objects fails with ORA-39165: Schema SYS Was Not Found. See below document for more details:
DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Doc ID 553402.1)
This was always painful as traditional exp commands lacks parallelism options, making this operation very slow.
But this is no more the case, just you need to know the right syntax 🙂
expdp directory=<directory_name> dumpfile=<dumpfile_name> logfile=<logfile_name> full=y include=audit_trails
Above syntax will export all tables related to audit in SYS schema. Best part that you can include parallel clause to make this operation faster .
Example is given below:
Best part is that this syntax even works on Oracle 12.1 & 12.2.
Hope u will find this post very useful. 🙂
Stay tuned for more articles on different topics.
Email ID: firstname.lastname@example.org