Session 2: Object and Schema Management

From DBArtisan
Jump to: navigation, search

Go Up to DBArtisan Tutorial exercises

DBArtisan provides unparalleled database object management capabilities. Its database platform-specific and version-specific graphical object editors and wizards enable you to easily create, drop or alter any of your accessible database objects.

Creating a Table Object

The following example walks you through creating a standard Oracle table object. This concept carries across all of the supported object types, across all of the supported platforms.

  1. On the Datasource Explorer, expand the Schema node of an Oracle datasource.
  2. Right-click the Tables node, and then select Create.
  3. DBArtisan opens the Table wizard and leads you through the process of creating a table object.
  4. Complete the wizard panels, and ensure that you create two or more columns in the table.
  5. Click Finish.

DBArtisan lets you preview any and all generated scripts before you submit them to the database. This is standard for all object-related scripts.

Making changes to an existing table object

Changes to database tables, such as modifying column lengths, inserting new columns, or deleting unneeded ones, can require dropping off a table. This requires knowledge of the underlying object dependencies so that these dependent objects are rebuilt after the table has been recreated. DBArtisan provides the ability to perform “extended” table alterations by constructing an SQL script with the steps necessary to save off the original data, create the new table, and populate it with the original data. Once these steps are complete, all dependent objects are then rebuilt and permissions re-applied.

  1. From the Explorer, Tables node, select the table you created in the previous example.
  2. Double-click the table or from the Command menu, click Open.
    DBArtisan opens the Table Editor. The Table Editor provides access to basic table properties, the list of table columns as well as any constraints, storage parameters, space allocation, partitioning, table dependencies, object privileges, table DDL and other attributes of the table.
  3. Ensure that the Columns tab is selected and select one of the columns you created in this table you want to modify.
    Details for the column are shown in the Column Attributes area on the right side of the pane.
  4. In the Width or Scale text box, type a new value.
  5. On the Table Editor toolbar, select the Alter button.
    DBArtisan lets you preview the SQL script before you submit it to the database.
  6. Close the Table Editor pane.

Working with Object DDL

DBArtisan allows you to easily extract DDL for single or multiple objects using several methods. The most straight-forward is described here:

  1. On the Explorer, right-click the table you created earlier, and then select Extract.
    The DDL for all highlighted objects is extracted directly in a DDL Editor where it can be altered, executed and saved to the database, with no intermediary steps required. For example:
  2. Close the PL/SQL Editor pane.

Proceed to Session 3: Database Security Management.