Administering the DDL Log Files in 12c
The DDL log is created only if the ENABLE_DDL_LOGGING initialization parameter is set to TRUE. When this parameter is set to FALSE, DDL statements are not included in any log. A subset of executed DDL statements is written to the DDL log.
How to administer the DDL Log?
--> Enable the capture of certain DDL statements to a DDL log file by setting ENABLE_DDL_LOGGING to TRUE.
--> DDL log contains one log record for each DDL statement.
--> Two DDL logs containing the same information:
--> XML DDL log: named log.xml
--> Text DDL: named ddl_<sid>.log
When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the log:
ALTER/CREATE/DROP/TRUNCATE CLUSTER
ALTER/CREATE/DROP FUNCTION
ALTER/CREATE/DROP INDEX
ALTER/CREATE/DROP OUTLINE
ALTER/CREATE/DROP PACKAGE
ALTER/CREATE/DROP PACKAGE BODY
ALTER/CREATE/DROP PROCEDURE
ALTER/CREATE/DROP PROFILE
ALTER/CREATE/DROP SEQUENCE
CREATE/DROP SYNONYM
ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
ALTER/CREATE/DROP TRIGGER
ALTER/CREATE/DROP TYPE
ALTER/CREATE/DROP TYPE BODY
DROP USER
ALTER/CREATE/DROP VIEW
Example
$ more ddl_orcl.log
Thu Nov 15 08:35:47 2012
diag_adl:drop user app_user
Locate the DDL Log File
$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/log
$ ls
ddl ddl_orcl.log debug test
$ cd ddl
$ ls
log.xml
Notes:
- Setting the ENABLE_DDL_LOGGING parameter to TRUE requires licensing the Database Lifecycle Management Pack.
- This parameter is dynamic and you can turn it on/off on the go.
- alter system set ENABLE_DDL_LOGGING=true/false;
No comments:
Post a Comment