3.3 Managing Tables

You can create, browse, edit, or drop tables as well as view different table reports.

A table is a unit of data storage in an Oracle database, containing rows and columns. When you view a table in Object Browser, a table description appears that describes each column in the table.

• Creating a Table
  Create a table using Object Browser.
• Browsing a Table
  Learn how to view different reports about a table.
• Reports Available for Tables
  Learn about alternative views available when viewing a table in Object Browser.
• Editing a Table
  Click the appropriate buttons on a table in Object Browser to edit a table.
• Dropping a Table
  Drop a table using Object Browser.
• Creating an Application from a Table
  Use the Create App button to quickly enter the Create Application Wizard based on the current table.
• Analyzing Statistics of a Table
  Use the Statistics tab to examine various metrics of a table.
• Viewing Sample Queries for a Table
  View the Sample Queries tab to review a set of example queries (such as sample table queries and sample column queries for tables with foreign keys) derived from the table contents.

See Also:

• Oracle Database Administrator’s Guide for information on managing tables

• Oracle Database Concepts for conceptual information on tables types

• Oracle Database SQL Language Reference for the syntax required to create and alter tables

3.3.1 Creating a Table

Create a table using Object Browser.

To create a table:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. Click the Create icon.
3. From the list of object types, select Table.
4. Enter a table name.

   Table names must conform to Oracle naming conventions.

5. To have the final table name match the case entered in the Table Name field, click Preserve Case.
6. Enter column details:

   1. Column Name - Enter the column name.

   2. Type - Select the column type.

   3. Precision - The precision is the number of digits before the decimal point. Not all column types have this setting. Precision must be a positive integer. Only NUMBER, FLOAT, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND have this setting. For example, if a column of type NUMBER has precision of 4, the column value can be 0 through 9999.

   4. Scale - The function of the scale setting depends on the column type. For NUMBER types, scale is the number of digits after the decimal point. For VARCHAR2 and CHAR types, scale is the number of stored characters. For TIMESTAMP types, scale is the fractional seconds precision and must be a number between 0 and 9. Scale must be a positive number. For example, if a column type TIMESTAMP has a scale of 3, seconds are between .000 seconds and .999 seconds.

   5. Not Null - To specify a column must always have a value, select the Not Null check box.

   6. If running Oracle Database 12c - Select an identity option for NUMBER and FLOAT columns. Options include:

      • Always - The Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error is returned.

      • Default - The Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column.

      • Default on Null - The Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

   7. Move - Click the Up and Down arrows in the Move column to change the order of previously entered columns.

   8. Add Column - Click Add Column to add additional columns.

   9. Click Next.

   Next, define the primary key for this table (optional). A primary key is a single field or combination of fields that uniquely identifies a record.

7. For Primary Key, select the appropriate option and click Next:

   1. Select an option:

      • No Primary Key - No primary key is created.

      • Populate from a new sequence - Creates a primary key and creates a trigger and a sequence. The sequence is used in the trigger to populate the selected primary key column. The primary key can only be a single column.

      • Populated from an existing sequence - Creates a primary key and creates a trigger. The selected sequence is used in the trigger to populate the selected primary key column. The primary key can only be a single column.

      • Not populated - Defines a primary key but does not have the value automatically populated with a sequence within a trigger. You can also select this option to define a composite primary key (that is, a primary key made up of multiple columns).

      • If running Oracle Database 12c - Populate By Identity Column - Creates a primary key and a sequence. The sequence is used to populate the selected primary key column.

   2. Click Next.

   Next, add foreign keys (optional). A foreign key establishes a relationship between a column (or columns) in one table and a primary or unique key in another table.

8. Add Foreign Key:

   1. Name - Enter a name of the foreign key constraint that you are defining.

   2. Select the appropriate option:

      • Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in this table.

      • Cascade Delete - Deletes the dependent rows from this table when the corresponding parent table row is deleted.

      • Set to Null on Delete - Sets the foreign key column values in this table to null when the corresponding parent table row is deleted.

   3. Select Key Column(s) - Select the columns that are part of the foreign key, then click the Add icon to move them to Key Column(s).

   4. References Table - Select the table which is referenced by this foreign key. Then, select the columns referenced by this foreign key. Once selected, click the Add icon to move the selected columns to Referenced Column(s).

   5. Click Add.

   6. Click Next.

   Next, add a constraint (optional). You can create multiple constraints, but you must add each constraint separately.

9. To add a constraint:

   1. Specify the type of constraint (Check or Unique).

      A check constraint is a validation check on one or more columns within the table. No records can be inserted or updated in a table which violates an enabled check constraint. A unique constraint designates a column or a combination of columns as a unique key. To satisfy a unique constraint, no two rows in the table can have the same values for the specified columns.A check constraint is a validation check on one or more columns within the table. No records can be inserted or updated in a table which violates an enabled check constraint. A unique constraint designates a column or a combination of columns as a unique key. To satisfy a unique constraint, no two rows in the table can have the same values for the specified columns.

   2. Enter the constraint in the field provided. For unique constraints, select the column(s) that are to be unique. For check constraints, enter the expression that should be checked such as flag in ('Y','N').

   3. Click Add.

10. Click Next.

    A confirmation page appears.

11. Click Create.

See Also:

• Using the Table Finder in Oracle APEX App Builder User’s Guide

• Overview of Tables in Oracle Database Concepts for information about tables

3.3.2 Browsing a Table

Learn how to view different reports about a table.

When you view a table in Object Browser, the table description appears. While viewing this description, you can add a column, modify a column, rename a column, drop a column, rename the table, copy the table, drop the table, truncate the table, or create a lookup table based upon a column in the current table. Additionally, you have access to other reports that offer related information including the table data, indexes, data model, constraints, grants, statistics, user interface defaults, triggers, dependencies, and SQL to create the selected table.

To view a table description:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, ensure Tables is selected.
3. From the Object Selection pane, select a table.

   The table description appears.

4. Click the tabs at the top of the page to view different reports about the table.

See Also:

Reports Available for Tables

3.3.3 Reports Available for Tables

Learn about alternative views available when viewing a table in Object Browser.

Table 3-1 describes all available reports for tables.

Table 3-1 Available Reports for Tables

View	Description
Table	Displays details of the first 57 columns including the column name, data type, nullable status, default value, and primary key. While viewing table details you can add, modify, delete, or rename a column. Additionally, you can drop, rename, copy, or truncate the table only if the referencing table has no records and create a lookup table. To export the data as a comma-delimited file (.csv) file, click the Download link. To print the data in a portable document format (.pdf) file, click the Print link. Please note, this option must have a Report Server configured at the instance level. See Configuring Report Printing for an Instance in Oracle APEX Administration Guide.
Data	Displays a report of the data in the current table. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Displays a report of the number of rows in the current table. Insert Row - Enables you to insert a row into the table. Download - Exports all data in the table to a spreadsheet. Click the Download link at the bottom of the page to export all data in the selected table. Edit - Click the Edit icon to edit a row.
Indexes	Displays indexes associated with this table. Actions you can perform include Create and Drop.
Model	Displays a graphical representation of the selected table along with all related tables. Related tables are those that reference the current table in a foreign key and those tables referenced by foreign keys within the current table. You can position the cursor over an underlined table name to view the relationship between that table and the current table. Click an underlined table name to view the model of the related table.
Constraints	Displays a list of constraints for the current table. Actions you can perform include Create, Drop, Enable, and Disable.
Grants	Displays a list of grants on the current table, including the grantee, the privilege, and grant options. Actions you can perform in this view include Grant and Revoke.
Statistics	Displays collected statistics about the current table, including the number of rows and blocks, the average row length, sample size, when the data was last analyzed, and the compression status (enabled or disabled). Click Analyze to access the Analyze Table Wizard.
UI Defaults	Displays user interface defaults for forms and reports. User interface defaults enable developers to assign default user interface properties to a table, column, or view within a specified schema. Click Edit to edit defined user interface defaults. Click Create Defaults to initialize user interface defaults for tables that do not currently have user interface defaults defined.
Triggers	Displays a list of triggers associated with the current table. Actions you can perform include Create, Drop, Enable, and Disable. To view trigger details, click the trigger name.
Dependencies	Displays report showing objects referenced by this table, objects this table references, synonyms for this table, and synchronization information for this table.
SQL	Displays the SQL necessary to re-create this table, including keys, indexes, triggers and table definition.
REST	If the table is REST-enabled, displays REST settings. Toggle the REST Enable Object switch to either enable or disable the table for REST.

See Also:

• Editing a Table
• Managing Indexes
• Managing User Interface Defaults
• Managing Triggers
• Automatic Enabling of Schema Objects for REST Access (AutoREST) in Oracle REST Data Services Developer's Guide

3.3.4 Editing a Table

Click the appropriate buttons on a table in Object Browser to edit a table.

While viewing a table description, you can edit it by clicking the buttons above the table description.

To edit a table:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, ensure Tables is selected.
3. From the Object Selection pane, select a table.

   The table description appears.

4. Click the appropriate button described in Table 3-2.

   Table 3-2 Edit Table Buttons

   Button	Description
   Add Column	Adds a column to the table. Enter a column name and select a type. Depending upon the column type, specify whether the column requires a value, the column length, precision, and scale.
   Modify Column	Modifies the selected column. For Oracle Database 11g Release 2 (11.2.0.4) and later, a Data Redaction region may be shown if the current schema selected has been granted execute on DBMS_REDACT.
   Rename Column	Renames the selected column.
   Drop Column	Drops the selected column.
   Rename	Renames the selected table.
   Copy	Copies the selected table.
   Drop	Drops the selected table.
   Truncate	Removes all rows from the selected table. Truncating a table can be more efficient than dropping and re-creating a table. Dropping and re-creating a table may invalidate dependent objects, requiring you to regrant object privileges or re-create indexes, integrity constraints, and triggers.
   Create Lookup Table	Creates a lookup table based on the column you select. That column becomes a foreign key to the lookup table.

See Also:

Using the Recycle Bin

3.3.5 Dropping a Table

Drop a table using Object Browser.

To drop a table:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, ensure Tables is selected.
3. From the Object Selection pane, select a table.

   The table description appears.

4. Click Drop.
5. To confirm, click Finish.

See Also:

Using the Recycle Bin

3.3.6 Creating an Application from a Table

Use the Create App button to quickly enter the Create Application Wizard based on the current table.

APEX Data Dictionary analyzes the table data with DBMS_STATS and adds the following types pages:

• chart page (if chart recommendation was computed)
• calendar page (if any date or timestamp column type exists)
• interactive report and form page
• dashboard with charts with columns for charting (these dashboard charts automatically include links to the interactive report page)
• faceted search page (if valid facets found)

To create an application from a table in Object Browser:

1. Select a table in Object Browser.
2. Click Create App.
3. On the confirmation page, click Create App.

The Create Application Wizard appears loaded with the data from the selected table.

Note:

If an error displays after you make DDL or DML changes (such as modifications to the table structure or column names), refresh the Data Dictionary Cache (see Refreshing the Data Dictionary Cache in Oracle APEX Administration Guide).

See Also:

Using the Create Application Wizard in Oracle APEX App Builder User’s Guide

3.3.7 Analyzing Statistics of a Table

Use the Statistics tab to examine various metrics of a table.

To view the statistics of a selected table:

1. Select a table in Object Browser.
2. Click the Statistics tab.
   The Statistics tab displays a number of metrics separated into Table Statistics for row analysis and Column Statistics for column analysis. The information presented depends on the table.

To refresh the displayed statistics:

3. Click one of the following buttons:
   • Click Analyze to use the traditional analysis algorithm.
   • Click Gather Table Statistics to use a modern method of analysis.
4. Proceed through the dialog screens as prompted.

The Statistics tab redisplays in Object Browser.

3.3.8 Viewing Sample Queries for a Table

View the Sample Queries tab to review a set of example queries (such as sample table queries and sample column queries for tables with foreign keys) derived from the table contents.

To view the table query examples:

1. Select a table in Object Browser.
2. Click the Sample Queries tab.
   The Sample Queries page loads with a Table Query and Column Queries.

Each query has a Run button that you can click to run.