Object Creation Methods in Oracle 12c
As a database administrator (DBA), you’re expected to create objects, but you seldom create them from scratch, especially when you use Oracle 12c. Typically, the application developer or software vendor provides SQL scripts with the DDL and DML for the objects to be created. You simply log in via SQL*Plus and run the scripts provided.
SQL scripts are the recommended method for these reasons:
A script isn’t subject to typos.
A script can be versioned, controlled, and re-executed as necessary.
Odds are that if you’re creating multiple objects by hand, typing directly into SQL*Plus, something is wrong with your overall development process. Rarely is it okay to create ad hoc objects.
The easiest way to create objects is with a tool, such as SQL*Developer. SQL*Developer enables you to hand-code the SQL to create objects or use various wizards if you’re not yet comfortable with the SQL language. The nice thing is that if you use a wizard, you can always see the SQL it created to help you learn.
Now let’s run through some examples using SQL*Developer on Linux. First, launch SQL*Developer and get connected to your database with the HR demo schema. This example uses the Oracle software installed on our database server, and connects to the local database, dev12c.
Open a terminal window and navigate to sqldeveloper under your ORACLE_HOME directory.
Log in as your Oracle software owner, oracle.
From this point, type
Launch the SQL*Developer tool by typing the following.
Connect to your database by clicking the (+) sign under the Connections tab on the right side of the screen.
In the wizard that appears, give your connection a name and then fill in the username and password.
Select the Save Password check box.
Change the SID to dev12c.
Click the Test button. If everything works, click Save.
Click the Connect button to open a connection to the database.
To create a database table in a schema, follow these steps:
Open the drop-down menu from the plus sign (+) next to your connected username.
Right-click Tables and choose New Table from the context menu.
The Create Table screen appears.
Fill in your column information.
This a DEPT table with two columns.
(Optional) Click the DDL tab to see the code generated in the background.
Click OK to create the table.
From the Connections panel, right-click Indexes and choose New Index from the context menu.
The Create Index screen appears.
Fill in the appropriate values:
Name: Enter <DEPT_NAME_IDX>.
Table: Choose DEPT from the drop-down list.
Type: Select the Normal and the Unique options.
The DEPT_NAME column is automatically selected because it’s the only un-indexed column in the table. If that’s not what you want, you can select DEPT_NAME and then click the red X button to remove it from the column list. You can then add the appropriate columns.
(Optional) Click the DDL tab to see the code being generated.
Click OK to create the new index.