Back to: Oracle DBA Tutorials
Enable DDL Logging in Oracle with Examples
In this article, I am going to discuss Enable DDL Logging in Oracle with Examples. Please read our previous article where we discussed Trace Files Purging in Oracle with Examples.
Enable DDL Logging in Oracle
This is an interesting topic. In this topic, we will try to enable DDL logging in the database. We already have the alert log file which stores all the logs happening in the database along with some DDLs like creating a database, and altering the database but does not contain DDLs like creating a table, creating columns and dropping tables, and so on.
We can enable it using database triggers but we have another option to enable it using the parameters. Open the file manager and go to the below location.
In this location, there is a folder called log. Go to the log folder.
Go to the log folder.
We can see there are many folders. Open the DDL folder.
We can see there are no files present inside the DDL folder because we haven’t enabled any DDL. Now let’s open the terminal and connect to the database as “Sqlplus / as sysdba”.
We are going to create some tables in the pluggable databases and log them in a log file. So, let’s move to the pluggable database.
We are in the pluggable database JAYAPDB now. There is a parameter that can enable_ddl_logging. Let us check whether the parameter is present or not. Command: Show parameter ddl
We used the command “show parameter ddl” and we got the results containing parameters with ddl in it. The parameter we use here enable_ddl_logging. The value of enable_ddl_logging is false. Let’s turn the value of enable_ddl_logging from FALSE to TRUE.
Command: alter session set enable_ddl_logging=True;
So, we have changed the value of enable_ddl_logging to True. Now, we can have logs for the DDL operations happening in the pluggable database. Let’s check whether the parameter is True or not.
The value of enable_ddl_logging is True only for the current session. Now, that we have enabled DDL logging let’s try to create some tables and try to drop the tables and see if these things are logged into the log file.
Command: create table dotnet (n number);
Let’s go ahead and create another table using the command “create table tutorial (new number);
We have enough tables created in the pluggable database. Now, let’s go ahead and drop the tables in the pluggable database.
Command: drop table table_name;
Let’s open the below path where the log of DDL is present.
We can see a new file named ddl_jayadb.log had been created. Let’s open the file and check whether the logs were generated or not.
We can see there are two table creations and two table drops as well. There are time stamps on the table creations and drop tables as well. We have another version of the DDL log file that is present in another folder. This is the XML version. Go to the below path where we can find the XML version of the DDL log file.
You will find a file name log.xml which contains the logging of DDL operations done on the pluggable database.
We can see we have created a table called tutorial and we can find the con_name as JAYAPDB and the hostname and time stamp for this operation.
Note: We have enabled DDL logging only for the session and only for the pluggable database we can enable it for the container database as well.
In the next article, I am going to discuss V$SPPARAMETER in Oracle with Examples. Here, in this article, I try to explain Enable DDL Logging in Oracle with Examples and I hope you enjoy this Enable DDL Logging in Oracle with Examples article.