ddl script

This article explains what DDL scripts are and how to use them. The script can contain various PL/SQL DDL commands used to create database objects and establish the relations between them. The abbreviation of the DDL statement is Data Definition Language. We can perform the following tasks using DDL commands.

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-Jan-18-2022-05-54-52-11-PM

  1. Create, alter and drop the schema or database objects.
  2. Grant and revoke the access of the user on all or specific database objects and schema.
  3. Configure the database auditing.
  4. Analyze the information of the index, table, and cluster.

Now, let’s go over the list of DDL commands.

Purpose

DDL Command

Manage database objects

·       CREATE: It is used to create database objects, i.e., Tables, views, procedures, indexes, functions, and constraints.

·       ALTER: It is used to manage the database objects in oracle. For example, you can add, modify and drop the columns from the table or change the definition of the stored procedure, functions, or view.

·       DROP: It is used to drop the database objects.

The above commands require exclusive permissions on the database objects that you want to manage.

Manage user access

·       GRANT: It is used to GRANT the privileges to a specific user or all users to perform one or more actions on all or specific database objects.

·       REVOKE: It is used to REVOKE the privileges from a specific user or all users to perform one or more actions on all or specific database objects.

·       DENY: It is used to restrict the privileges of a specific user or all users to perform one or more actions on all or specific database objects.

You can read Managing User Privileges to learn how to manage user access in oracle.

Manage database Audit

·       AUDIT: The AUDIT command is used to record and monitor the action performed by the user on a database. For example, if you want to audit all CREATE TABLE and DROP TABLE statements. You can configure the DDL statement audit using the AUDIT command. You can read Auditing Database Activity to learn more about database auditing.

Analyze database objects

·       ANALYZE: The ANALYZE command is used to perform the following tasks

·       Delete or collect the statistics of the table, table partitions, index, index partition, IOT (Index organized tables), or clusters.

·       Validate the structure of the table, index, IOT, or cluster.

·       Identify the migrated rows of the table.

You can read ANALYZE to learn more about ANALYZE commands.

 

Benefits of using DDL Scripts

The DDL scripts have the following benefits.

  1. The execution of the script can be automated.
  2. The scripts can be useful when you want to create an empty database whose schema structure is identical to the existing database. You can generate the script of the objects, rename the objects and execute the script.
  3. The scripts are handy if you drop an object containing no data. For example, if you accidentally drop any index, view, stored procedure, or function. If the script of the dropped object is available, you can easily recreate the dropped object.
  4. The scripts of the database objects can be used in database migration. For example, if you migrate the database objects from Oracle to SQL Server, you can generate the DDL script of the objects, make the required changes and execute it on the SQL Server database.
  5. DDL Scripts are reusable. The script is used to alter a specific object, and the same script can be used to alter a different object. All you have to do is change the object names in the script. It is much more convenient than using GUI tools.

Things to be considered while creating a DDL script.

The database management and development tools always help us create a script of any existing object. For example, Toad for oracle helps export the script for all databases. Those scripts can be used to create an empty or blank database. Not every company uses sophisticated database development and management tools. The DBAs and Developers must write a script to create DDL objects, so if you are writing a script to re-create any specific object or all objects of the database, you should consider the following items.

  1. If you have automated the process to run the script, verify the access of the user who is running the script. The script execution will fail if the user who is executing the script does not have appropriate permission on the table/schema/database.
  2. If you create the table using DDL script, make sure that tables are created in the proper sequence. For example, if you add a table with foreign keys, make sure you add a parent table to avoid errors.
  3. When you are adding any constraint in a column, always check the column that exists on the table on which you are creating the constraint.
  4. If you create a stored procedure/triggers/functions using DDL Script, make sure that the table and views exist on the schema or the database. Similarly, if you move the table to a different tablespace, ensure it exists in the database.
  5. If you change a data type of the large table using the DDL script, you can use export-import methods. First, you should create a separate table with a new datatype, import the data from the old table to the new table and then rename the old table with dummy name and change the name of the new table to the original name. For example, if you are changing the datatype of tblPatient, then you can use the following sequence.
    1. Create tblPatient_New table with the new datatype.
    2. Import data to the tblPatient_New(New Table) table from the tblPatient (Old table) table.
    3. Rename tblPatient to tblPatient_Old.
    4. Rename tblPatient_New to tblPatient.

Now, let’s understand how to generate a DDL script using the Toad for Oracle. To demonstrate, I have created a sample pluggable database named eltechDB. I have created a user named eltechapp that contains the required permission to export the DDL Script of any table.

The database contains the following objects.

Query

select object_type, count(object_type)"Count of object" from all_objects where owner='ELTECHAPP' group by object_type order by object_type desc;

Output

ddl script 1

Let us see how to generate the DDL scripts using Toad for Oracle.

Generate DDL Scripts using Toad for Oracle.

Open the Toad for Oracle and configure the connection parameters as shown.

ddl script 2

Once connected, Click on Database>Click on Export>Select Export DDL.

ddl script 3

An export DDL tab opens. Here, we can choose to export the script of all database objects or specific database objects. To select the objects, click on Add (Image 1). A dialog box (Image 2) opens. We are exporting the script of all user tables of the ELTECHAPP schema, therefore, select Tables from the Load object type drop-down box and select ELTECHAPP from the schema drop-down box. Click on Load Rows.

ddl script 4

All tables created in the ELTECHAPP schema will be loaded. We want to create one script that contains the DDL of all tables. Also, we want to open it in a SQL editor of Toad, so select Editor -as script option and click on execute button.

ddl script 5

Once the process is executed successfully, you can view the DDL script of all tables in a query editor.

ddl script 6

As you can see, the DDL script creates all tables created by the eltechapp user.

Try Toad free for 30 days. 

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

Summary

In this article, we learned about DDL statements and commands. We learned the following topics.

  1. What the DDL commands are and their usage.
  2. The benefits of using DDL script and best practices and points that should be taken care of while executing DDL script.
  3. How to generate the DDL scripts of the database objects using the Toad for Oracle.

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.

About the Author

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 11 years of experience with SQL Server administration and 2 years in managing oracle database. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com.

Notable Replies

  1. says:
    litithompson1

    Hello, please how can i create a DDL file?
    Because i don't really know how to undergo with this protocol anyway
    Please can someone out there help/guide me on how to go with it please?