Database Objects

You can create, edit, delete (drop), and add to the Cart most types of objects in an Oracle database by using the context menu (right-click, or Shift+F10) in the Connections navigator or by clicking the Actions button in the detail pane display. For some objects, you can do other operations, as appropriate for the object type.

For some object types the context menu includes Open, which generates a tabular overview display of information about objects of that type. For example, selecting Open for the Tables node in the Connections navigator displays a list of all tables for the connection, and for each table the table name, the number of rows, the table status, the number of columns, the comment (if any), and other information.


Note:

The actions available from context menus and Actions buttons depend on the Oracle Database release number for the specified database connection. If an action mentioned in the text is not available with a connection, it may be that the feature was not available in that release of Oracle Database.

The dialog boxes for creating and editing many objects contain a tab or pane named DDL or SQL, where you can see the statement that SQL Developer will use to perform the actions you specify (CREATE to create an object, ALTER to apply changes to an existing object).

You can search for specific objects associated with an Oracle database connection by clicking the Search icon, as explained in Finding Database Objects.

If you have connected to any third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2, you can view their objects using the Connections navigator. (For information about connecting to third-party databases, see the SQL Developer user preferences for Database: Third Party JDBC Drivers.)

Related Topics

SQL Developer Concepts and Usage

SQL Developer Tutorial: Creating Objects for a Small Database

Database: Third Party JDBC Drivers

Supplementary Oracle Database Information

Applications (Application Express 3.0.1 and Later)

Effective with Oracle Application Express 3.0.1, if you use SQL Developer to connect to a schema that owns any Application Express applications, the Connections navigator has an Application Express node. You can click an application name to display tabs (Application, Pages, LOVs, Lists, Templates, Breadcrumbs, and so on) with information about the application.

You can perform the following operations on an Application Express application by right-clicking the application name in the Connections navigator and selecting an item from the menu:

The following operations are available only by right-clicking the Application Express node in the Connections navigator and selecting an item from the menu:

Cache Groups (Oracle TimesTen In-Memory Database)

A cache group describes a collection of in-memory database tables that map to all or a subset of the tables in an Oracle database. A cache group can consist of all or a subset of the rows and columns in these tables. Multiple cache groups can be used to cache different sets of related tables in the Oracle database.

Chains

A chain is an Oracle Scheduler object that enables you to implement dependency scheduling, in which jobs are started depending on the outcomes of one or more previous jobs. A chain consists of multiple steps that are combined using dependency rules. The dependency rules define the conditions that can be used to start or stop a step or the chain itself. Conditions can include the success, failure, or completion-codes or exit-codes of previous steps. Logical expressions, such as AND/OR, can be used in the conditions. In a sense, a chain resembles a decision tree, with many possible paths for selecting which tasks run and when.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Credentials

A credential is an Oracle Scheduler object that is a user name and password pair stored in a dedicated database object. A job uses a credential to authenticate itself with a database instance or the operating system so that it can run.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Database Destinations

A database destination is an Oracle Scheduler object that defines a location for running a job. There are two types of destinations: an external destination specifies a remote host name and IP address for running a remote external job; a database destination specifies a remote database instance for running a remote database job.

If you specify a destination when you create a job, the job runs on that destination. If you do not specify a destination, the job runs locally, on the system on which it is created.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Database Links (Public and Private)

A database link is a database object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system; however, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views in the other database. The Connections navigator has a Database Links node for all database links (public and private) owned by the user associated with the specified connection, and a Public Database Links node for all public database links on the database associated with the connection. For help with specific options in creating a database link, see Create/Edit Database Link.

You can perform the following operations on a database link by right-clicking the database link name in the Connections navigator and selecting an item from the menu:

Destination Groups

A destination group is an Oracle Scheduler object whose members are a list of Scheduler destination objects. In a database destination group, the members are database destinations, for running remote database jobs. In an external destination group, members are external destinations, for running remote external jobs.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Directories

A directory object specifies an alias for a directory (called a folder on Windows systems) on the server file system where external binary file LOBs (BFILEs) and external table data are located. To create a directory (that is, a directory object), you can use SQL Developer or the SQL statement CREATE DIRECTORY.

You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility. All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users.

Editions

Edition-based redefinition, introduced in Oracle Database 11g Release 2 (11.2), enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time. To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users. For more information, see the chapter about edition-based redefinition in Oracle Database Development Guide.

To specify the current edition, right-click the edition name and select Set Current Edition. To create an edition under an existing edition, right-click the edition name and select Create Edition. To delete an edition (and optionally all editions under it), right-click the edition name and select Drop Edition.

File Watchers

A file watcher is an Oracle Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Functions

A function is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Functions return a value; procedures do not return a value.) For help with specific options in creating a PL/SQL subprogram, see Create PL/SQL Subprogram (Function or Procedure).

You can perform the following operations on a function by right-clicking the function name in the Connections navigator and selecting an item from the menu:

Indexes

An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Indexes are automatically created on primary key columns; however, you must create indexes on other columns to gain the benefits of indexing. For help with specific options in creating an index, see Create/Edit Index.

You can perform the following operations on an index by right-clicking the index name in the Connections navigator and selecting an item from the menu:

Java Sources

Java sources can be created and managed in the database. You can create a Java source object by right-clicking the Java node in the Connections navigator, selecting Load Java, and specifying the Java source name and either entering the source code or loading a Java source, class, or resource from a file (BFILE). (A CREATE OR REPLACE AND RESOLVE JAVA SOURCE statement is executed using the information you specify.) For information about Java concepts and stored procedures, see Oracle Database Java Developer's Guide.

Jobs

A job is an Oracle Scheduler object that is a collection of metadata that describes a user-defined task. It defines what needs to be executed (the action), when (the one-time or recurring schedule or a triggering event), where (the destinations), and with what credentials. A job has an owner, which is the schema in which it is created.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Job Classes

A job class is an Oracle Scheduler object that enables the Scheduler administrator to group jobs for logical purposes, such as to assign the same set of attribute values to member jobs, to set service affinity for member jobs, to set resource allocation for member jobs, or to group jobs for prioritization.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. For help with specific options in creating a materialized view, see Create/Edit View, especially the View Information or Materialized View Properties pane.

Materialized View Logs

A materialized view log is a table associated with the master table of a materialized view. When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

Multitenant Container Database (CDB)

A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

For more information, see the "Overview of Pluggable Databases" and "Architecture of Pluggable Databases" chapters in Oracle Database Concepts.

Packages

A package is an object that contains subprograms, which are programming objects that can be stored and executed in the database server, and called from other programming objects or applications. A package can contain functions or procedures, or both. For help with specific options in creating a package, see Create PL/SQL Package.

You can perform the following operations on a package by right-clicking the package name in the Connections navigator and selecting an item from the menu:

Procedures

A procedure is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Procedures do not return a value; functions return a value.) For help with specific options in creating a PL/SQL subprogram, see Create PL/SQL Subprogram (Function or Procedure).

You can perform the following operations on a procedure by right-clicking the procedure name in the Connections navigator and selecting an item from the menu:

Programs

A program is an Oracle Scheduler object that describes what is to be run by the Scheduler. A program includes an action, a type, and the number of arguments that the stored procedure or external executable accepts. (A program is a separate entity from a job. A job runs at a certain time or because a certain event occurred, and invokes a certain program.)

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Queues

A queue is an object in which messages are enqueued and dequeued. Queues are managed by Oracle Streams Advanced Queueing (AQ). For information about using queues, see Oracle Database Advanced Queuing User's Guide.

Queue Tables

A queue table is a table that holds messages to be used with Oracle Streams Advanced Queueing (AQ). For information about using queue tables, see Oracle Database Advanced Queuing User's Guide, especially the information about managing queue tables in the chapter describing the Oracle Streams AQ administrative interface.

Recycle Bin

The Recycle bin (applicable only to Oracle Database Release 10g and later) holds objects that have been dropped (deleted). The objects are not actually deleted until a commit operation is performed. Before the objects are actually deleted, you can "undelete" them by selecting them in the Recycle bin and selecting Flashback to Before Drop from the context menu.

The Recycle bin is available only for non-system, locally managed tablespaces. Thus, to take advantage of the Recycle bin to be able to recover dropped tables for a database user, create or edit the user to have a non-system default tablespace (for example, USERS and not SYSTEM).

You can perform the following operations on an object in the Recycle bin by right-clicking the object name in the Recycle bin in the Connections navigator and selecting an item from the menu:

Replication Schemes (Oracle TimesTen In-Memory Database)

A replication scheme is a configuration, using SQL statements and a transaction-based log, whereby committed changes are copied from their source to one or more subscriber databases. The goal is to enable high efficiency and low overhead during the replication.

Schedules

A schedule is an Oracle Scheduler object that specifies when and how many times a job is run. Schedules can be shared by multiple jobs. For example, the end of a business quarter may be a common time frame for many jobs; instead of having to define an end-of-quarter schedule each time a new job is defined, job creators can point to a named schedule.

For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

Sequences

Sequences are used to generate unique integers. You can use sequences to automatically generate primary key values. For help with specific options in creating and editing a sequence, see Create/Edit Sequence.

For conceptual and usage information about sequences, see Managing Sequences.

Synonyms (Public and Private)

Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms. The Connections navigator has a Synonyms node for all synonyms (public and private) owned by the user associated with the specified connection, and a Public Synonyms node for all public synonyms on the database associated with the connection. For help with specific options in creating and editing a synonym, see Create/Edit Synonym.

For conceptual and usage information about synonyms, see Managing Synonyms.

Tables

Tables are used to hold data. Each table typically has multiple columns that describe attributes of the database entity associated with the table, and each column has an associated data type. You can choose from many table creation options and table organizations (such as partitioned tables, index-organized tables, and external tables), to meet a variety of enterprise needs. To create a table, you can do one of the following:

You can perform the following operations on a table by right-clicking the table name in the Connections navigator and selecting an item from the menu:

You can perform the following operations on a column in a table by right-clicking the column name in the Connections navigator and selecting an item from the menu:

For conceptual and usage information about tables, see Managing Tables.

Flashback Table Support

For tables in Oracle Database Release 11.1 and later, the table display includes the Flashback tab, which provides a view of the modified and original data in the table. If you have appropriate privileges, you can click the Undo SQL subtab to select and review the syntax required to undo changes. For information about using the Flashback Table feature, see Oracle Database Backup and Recovery User's Guide.

Triggers

Triggers are stored PL/SQL blocks associated with a table, a schema, or the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java. Oracle Database automatically executes a trigger when specified conditions occur. For help with specific options in creating a trigger, see Create Trigger.

A crossedition trigger is intended to fire when DML changes are made in a database while an online application that uses the database is being patched or upgraded with edition-based redefinition. The body of a crossedition trigger is designed to handle these DML changes so that they can be appropriately applied after the changes to the application code are completed. To create a crossedition trigger, you must be enabled for Editions.

For conceptual and usage information about triggers, see Triggers: Usage Information.

Types

A data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a function or procedure. These properties cause Oracle Database to treat values of one data type differently from values of another data type. Most data types are supplied by Oracle, although users can create data types.

For conceptual and usage information about data types, see Data Types: Usage Information. For help with specific options in creating a user-defined type, see Create Type (User-Defined).

Users (Other Users)

Database users are accounts through which you can log in to the database. In the Connections navigator, you can see the Other Users in the database associated with a connection, but the database objects that you are allowed to see for each user are determined by the privileges of the database user associated with the current database connection.

If you are connected as a user with the DBA role, you can create a database user by right-clicking Other Users and selecting Create User, and you can edit an existing database user by right-clicking the user under Other Users and selecting Edit User. For help on options in creating and editing users, see Create/Edit User.

Views

Views are virtual tables (analogous to queries in some database products) that select data from one or more underlying tables. Oracle Database provides many view creation options and specialized types of views (such as materialized views, described in Materialized Views), to meet a variety of enterprise needs. For help with specific options in creating and editing a view, see Create/Edit View.

For conceptual and usage information about views, see Managing Views.

Editioning views are shown in a separate navigator node if the connection is to an Oracle Database release that supports Editions. An editioning view selects a subset of the columns from a single base table and, optionally, provides aliases for them. In providing aliases, the editioning view maps physical column names (used by the base table) to logical column names (used by the application). For more information about editioning views, see the chapter about edition-based redefinition in Oracle Database Development Guide.

You can perform the following operations on a view by right-clicking the view name in the Connections navigator and selecting an item from the menu:

Window Groups

A window group is an Oracle Scheduler object that is a list of Oracle Scheduler Windows. Scheduler jobs that are scheduled to be run in a window group will be activated in that time span and using that resource plan for all windows in the group.

You can group windows for ease of use in scheduling jobs. If a job must run during multiple time periods throughout the day, week, and so on, you can create a window for each time period, and then add the windows to a window group. You can then set the schedule_name attribute of the job to the name of this window group, and the job executes during all the time periods specified by the windows in the window group.

For example, if you had a window called "Weekends" and a window called "Weeknights," you could add these two windows to a window group called "Downtime." The data warehousing staff could then create a job to run queries according to this Downtime window group—on weeknights and weekends—when the queries could be assigned a high percentage of available resources.

For more information, see Scheduling Jobs Using SQL Developer.

Windows

A window is an Oracle Scheduler object that can be used to automatically start jobs or to change resource allocation among jobs during various time periods of the day, week, and so on. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am".

Windows work with job classes to control resource allocation. Each window specifies the resource plan to activate when the window opens (becomes active), and each job class specifies a resource consumer group or specifies a database service, which can map to a consumer group. A job that runs within a window, therefore, has resources allocated to it according to the consumer group of its job class and the resource plan of the window.

For more information, see Scheduling Jobs Using SQL Developer.

XML DB Repository

Oracle XML DB Repository is a component of Oracle Database that is optimized for handling XML data. The Oracle XML DB repository contains resources, which can be either folders (directories, containers) or files. For more information about Oracle XML DB Repository, see Oracle XML DB Developer's Guide in the Oracle Database documentation library.

To create a subfolder of an existing folder, right-click the folder name and select Create Subfolder. To delete a folder (and optionally all subfolders under it), right-click the folder name and select Drop Folder.

XML Schemas

XML schemas are schema definitions, written in XML, that describe the structure and various other semantics of conforming instance XML documents. For conceptual and usage information about XML schemas, see Oracle XML DB Developer's Guide in the Oracle Database documentation library.

You can edit an XML schema by right-clicking the XML schema name in the Connections navigator and selecting Open from the menu; you can delete a selected schema by selecting Drop Schema from the menu.

Captured and Converted Database Objects (for Migration)

If you are migrating a third-party database to Oracle, the Captured Models and Converted Models navigators can display models that include database objects, such as tables and procedures. A captured object represents an object in the captured third-party database, and a converted object represents an Oracle model of that object as it will be created in the Oracle database.

The context menu for each captured object includes Convert to Oracle, which creates a corresponding converted object. The context menu for each converted object includes Generate, which creates the corresponding Oracle Database object. (The context menus will contain other items as appropriate for the object.)

For information about the related Oracle Database objects, see the following: