Enable DDL Logging in Oracle

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.

Enable DDL Logging in Oracle with Examples

In this location, there is a folder called log. Go to the log folder.

Enable DDL Logging in Oracle with Examples

Go to the log folder.

Enable DDL Logging in Oracle with Examples

We can see there are many folders. Open the DDL folder.

Enable DDL Logging in Oracle with Examples

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”.

Enable DDL Logging in Oracle with Examples

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.

Enable DDL Logging in Oracle with Examples

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

Enable DDL Logging in Oracle with Examples

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;

Enable DDL Logging in Oracle with Examples

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.

Enable DDL Logging in Oracle with Examples

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);

Enable DDL Logging in Oracle with Examples

Let’s go ahead and create another table using the command “create table tutorial (new number);

Enable DDL Logging in Oracle with Examples

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;

Enable DDL Logging in Oracle with Examples

Let’s open the below path where the log of DDL is present.

Enable DDL Logging in Oracle with Examples

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.

Enable DDL Logging in Oracle with Examples

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.

Enable DDL Logging in Oracle with Examples

You will find a file name log.xml which contains the logging of DDL operations done on the pluggable database.

Enable DDL Logging in Oracle with Examples

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.

Leave a Reply

Your email address will not be published. Required fields are marked *