Aug 30, 2022 3:00:00 AM by Nisarg Upadhyay
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.
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. |
The DDL scripts have the following benefits.
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.
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
Let us see how to generate the DDL scripts using Toad for Oracle.
Open the Toad for Oracle and configure the connection parameters as shown.
Once connected, Click on Database>Click on Export>Select Export DDL.
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.
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.
Once the process is executed successfully, you can view the DDL script of all tables in a query editor.
As you can see, the DDL script creates all tables created by the eltechapp user.
Try Toad free for 30 days.Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now. |
In this article, we learned about DDL statements and commands. We learned the following topics.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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.
Tags: Toad for Oracle
Written by 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.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.