SQL Developer Dialog Boxes and Wizards

6.1 Add Extension

This dialog box is displayed when you click Add in the File Types pane of SQL Developer preferences.

Extension: Specify the file extension, including the period (for example, .xyz).

After you click OK, you can select that extension and modify its details, including the file type, content type, and whether to have files with the extension automatically opened by SQL Developer.

Related Topics

6.2 Add Schema Error

This dialog box is displayed when you specify an invalid file after clicking Add in the XML Schemas pane of SQL Developer preferences. A list of the errors is displayed.

Ignore errors and continue registration: Ignores the errors and allows the process of registering the specified XML schema or schemas after you click OK. Choose this option only if you are sure that the apparent errors can be ignored.

Skip registration and open using SQL Developer: Opens an editing window in which you can correct any errors.

Related Topics

6.3 Advanced Properties (Connections)

This dialog box is displayed if you click Advanced in the Create/Edit/Select Database Connection dialog box.

6.3.1 Proxy tab

This information applies to proxy connections.

Proxy Type: User Name for authentication by proxy user name and password, or Distinguished Name for authentication by proxy user name and distinguished name.

Proxy User: Name of the user to be used for authentication for this connection.

Proxy Password (if Proxy Type is User Name): Password for the specified proxy user.

Distinguished Name (if Proxy Type is Distinguished Name): Distinguished name for the specified proxy user.

Related Topics

Connections with Proxy Authentication

6.4 Application Migration

The Application Wizard enables you to migrate a DBLib or CTLib Sybase application to Oracle by specifying a directory with source code from the DBLib or CTLib Sybase application. You will be able to see what changes must be made to migrate the application to Oracle, and to perform the migration.

The Welcome page includes a description of the wizard's operation and options. If you want to continue to see this Welcome page in the future, do not enable (do not check) the Do not show this page again option.

Follow the instructions on each page of the wizard.

On the Overview page, specify the Application Name and optionally a Description of the application.

On the File Selection page, specify the application root directory and other information to determine the files to be added to the project:

Application Directory: Root directory for the application. All files and folders under this directory will be scanned and added to the project.

Database Type: Type of third-party database for the migration (for example, sybase).

Output Actions: What the wizard should do with the output (for example, Output to directory, and then specify the Output Directory).

Use Custom Rules: Whether to use custom rules for the output; and if so, the Rules Directory for the custom rules.

Related Topics

SQL Developer: Migrating Third-Party Databases

6.5 Associate Repository

This dialog box is displayed if you click Tools, then Migration, then Repository Management, then Associate Repository.

Associate Repository: Name of the database connection to use to create a migration repository. The objects associated with the migration repository are created in the schema of the user associated with the selected connection.

6.6 Cart Error (Objects Not Available)

This box, with the text Some objects included in the cart are not available. Review cart contents., is displayed when you attempt to perform a Cart operation when one or more of the database objects in the selected cart are no longer available. For example, if you attempt to export some tables that include TABLE_1, but TABLE_1 was dropped or the database connection was deleted after that table was added to the cart, then this error box is displayed.

To fix the problem, ensure that you are attempting to perform the Cart operation only on database objects that exist and are available.

Related Topics

Using the Cart

6.7 Change Type

Use this dialog box to change the data type of a column in a captured model before you perform the migration.

Source Data Type: Specify the new data type for the column.

Any remaining fields in the dialog box depend on the Source Data Type that is selected.

6.8 Check for Updates

When you click Help and then Check for Updates, you can check for and download available SQL Developer updates. The following pages may be displayed. (If you have enabled the SQL Developer preference to check for updates automatically at startup, and if you click to see available updates at startup, the Updates page is displayed.)

If you are unable to check for updates because your system is behind a firewall, you may need to set the SQL Developer user preferences for Web Browser and Proxy.

Source: Select the source or sources to be checked for available updates: any or all of some specified online update centers, or a local ZIP file containing an update bundle. You can also click Add to add a user-defined update center.
Updates: If any updates are available from the selected source or sources, select those that you want to download.The available updates include certain third-party JDBC drivers, which require that you agree to the terms of their licenses.

The Show Upgrades Only option restricts the display to upgrades of currently installed SQL Developer components. To enable the display of all new and updated components, whether currently installed or not, uncheck this option.

After you click Next, you may be prompted to enter your Oracle Web Account user name and password. If you do not have an account, you can click the Sign Up link.
License Agreements (displayed only if you selected any updates that require a license agreement): For each update that requires you to agree to the terms of a license, review the license text and click I Agree. You must do this for each applicable license.
Download: If you selected any updates to download, this page displays the progress of the download operation.
Summary: Displays information about the updates that were downloaded. After you click Finish, you will be asked if you want to install the updates now and restart SQL Developer.

6.10 Choose Directory

This is a standard box for choosing a directory in which to place files: use Location to navigate to (double-clicking) the folder in which to save the files, or enter a directory name. If the directory does not already exist, it is created.

6.11 Clone PDB to Oracle Cloud

Use this dialog box to clone a PDB to the Oracle Cloud. It unplugs the source PDB, plugs it into the Cloud CDB destination, then either plugs the source PDB back into its CDB or deletes it from its CDB.

Source PDB: Name of the PDB to be cloned to the Oracle Cloud.

Destination Connection: Name of the multitenant container database (CDB) connection in which to plug in the cloned PDB.

Action after clone: Determines what happens to the original (source) PDB after its clone is unplugged from it current CDB and plugged into the CDB at the destination connection.

RePlug: The source PDB is plugged back into its CDB. (Note that after the source PDB is replugged, it and the clone made at the destination connection are separate and independent; you can modify metadata and data in one without affecting the other, if you wish.)
Delete: The source PDB is deleted from its CDB.

Related Topics

Multitenant Container Database (CDB)

6.12 Clone Pluggable Database

Use this dialog box to clone a PDB. It creates a new PDB from (using basic specifications of) a specified PDB.

Database Name: Name of the new PDB.

Source PDB: Source PDB for the cloning operation.

Database Link: If the source PDB is in a remote CDB, specify the name of the database link to use for connecting to the remote CDB.

Storage: You can specify storage limits for the PDB total size or temporary tablespace usage, or both; or unlimited storage for either or both.

File Name Conversions: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

None: The database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names.
Custom Names: Select a Source Files/Target Files pair.
Custom Expressions: Specify one or more Source File Expression/Target File Expression pairs. Each pair item is a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

Related Topics

Multitenant Container Database (CDB)

6.14 Color Palette and Custom Colors

You can use the color palette editor to select a color from the supplied Available Colors or saved Custom Colors.

You can also create a color by using a gradient box or by specifying the RGB (Red, Green, Blue) values for the color.

6.15 Configure Extension

This dialog box, which is displayed if you click Configure for Versioning Support in the Extensions preferences pane, enables you to select from among available versioning support extensions for SQL Developer.

If you change any existing settings, you will need to restart SQL Developer.

Related Topics

Using Versioning

6.16 Configure File Type Associations

This dialog box, which is displayed the first time you start SQL Developer, enables you to associate certain file types with SQL Developer. If a file type is associated with SQL Developer, files with that type's extension will automatically be opened by SQL Developer when you double-click the file name. Any previous association for that file type is replaced.

If you do not associate a file type with SQL Developer, any existing association for that file is unchanged.

After you close this box, you can change the associations for these file types and many others by clicking Tools and then Preferences, and selecting File Types .

Related Topics

File Types

6.18 Copy Columns

This dialog box is displayed if you click the Copy Columns icon when specifying column definitions for a table.

Schema: The schema that owns the table from which to copy column definitions.

Table: The table within the selected schema.

Columns: A list of the columns in the table. Select one or more columns to be copied into the table that you are creating or editing, and click OK.

6.19 Copy Objects

This dialog box is displayed if you click the Copy Objects icon in the Cart window.

Open Configuration icon: Opens a previously saved XML configuration file, to use its settings as defaults for this use of the dialog box.

Save Configuration icon: Saves the current settings in the dialog box to an XML file, which you can later open to use for Cart operations of this type.

Destination Connection: Database connection into which to copy the objects.

Copy DDL: Copies the object definitions. For destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify whether to not perform the copy (that is, do not replace the destination objects) or to perform the copy (that is, replace the existing destination objects with the source objects).

Copy Data: Copies the data for any tables and views that are copied. If you do not select this option, any copied tables or views are empty in the destination connection.

Truncate Destination Data Before Copying: If a table or view of the same name already exists in the destination connection (for example, if an EMPLOYEES table exists in the source and destination), this option deletes any existing data in the destination object before copying the data from the source connection. If this option is not selected, the copied data is appended to the existing data in the destination object.

Related Topics

6.20 Copy to Hadoop / Append to Hadoop Table

Use this wizard to copy a table from Oracle Database to HDFS and create a Hive table, or to append data to an existing Hive table that was created using Copy to Hadoop. To use the wizard, you must understand the information in Apache Hadoop Connectors Support in SQL Developer.

The copy operation creates Oracle Data Pump files from an Oracle Database table, and copies them to HDFS. It also creates a Hive external table over the Data Pump files. It can optionally convert the Oracle Data Pump files to Parquet or ORC format.

You can initiate Copy by right-clicking the Tables icon in a Hive database schema.

You can initiate Append to an Existing Hive External Table by right-clicking the icon for that Hive table.

The wizard steps specify details for the source and destination objects, and selection criteria for the source data content to be copied.

Copy Method

Select Direct Copy to Staging External Table method.

The Direct Copy method copies data from an Oracle Database table directly to HDFS. It copies data from one Oracle table, rows can be selected by a WHERE clause filter. Direct Copy requires an SSH connection to a Hadoop client, a Hadoop edge node, or a node in the Hadoop cluster. The Direct Copy method is recommended.

The Staging External Table method exports data to a file system location on the database system, and then copies them to HDFS. The Staging External Table method requires an SSH connection to the database system, connecting as the user running the database process (typically “oracle”).

SSH Connection: You must select an SSH connection.

For the Direct Copy method, this is an SSH connection to a Hadoop client, a Hadoop edge node, or a node in the Hadoop cluster. As discussed in the Apache Hadoop Connectors Support in SQL Developer section, the .sqldev_cp2hadoop_env file must have been created in the home directory of this SSH connection.

For the Staging External Table method, this is an SSH connection to the database system.

The Copy to Hadoop job will run from the node of the SSH connection.

Source and Destination

Oracle Database Source: Specify the connection, database object, Oracle directory, Hadoop cluster (if Oracle Big Data SQL is configured to access multiple clusters), and degree of parallelism.

Hive Destination: Specify the Hive external table name and HDFS root directory for the data files.

Source Selection Criteria

Enter the selection criteria for the source table. The selection criteria is used to specify table rows to copy in the copy operation.

Optionally, select the columns to include in the copy operation.

Optionally, select parameters and specify a WHERE clause.

Refresh: Click to view sample results.

Source Connection

Summary

Specify Wallet credentials if using Oracle Wallet. These are credentials to access the table you are copying from.

Select the Delete Deployment Files checkbox on the SSH host to clean up the files for this job. For debugging, deselect the checkbox.

You can review the options that you specified before clicking Finish to submit the job.

Click the Process tab to view the steps that will be executed as a part of the job.

6.21 Copy to Oracle

This dialog box is displayed if you try to drag and drop an object from one connection to another connection. The options shown depend on the type of object. For copying a table, the options include the following.

Copy DDL: Copies the object definitions. For existing destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify either Do Not Replace (that is, do not perform the copy and replace the destination objects) or Replace (that is, replace the existing destination objects with the source objects).

Copy Data: Copies the data for any tables that are copied. If you do not select this option, any copied tables are empty in the destination connection. For existing destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify one of the following:

Do Not Copy Data: Do not copy data from the source object.
Append: Append the copied data in the destination object.
Truncate Destination Data Before Copying: Delete any existing data in the destination object before copying the data from the source connection.

Related Topics

6.22 Component Palette

The Component Palette displays the elements that you can drag and drop. The components available for selection in the palette vary depending on the content of the active editor window.

To insert a component into a file open in the active editor, drag the component from the palette to an insertion point in the editor. In some file types you can click a component in the palette and then click in the editor to insert the component.

Enter the appropriate information in the dialog box that is displayed (for example, Insert CDATA or Insert Processing Instruction in an XML file).

6.23 Component Palette: Configure Component Palette

Lets you configure the component palette. Note that some page types cannot be edited or removed, and most existing component types cannot be added to, edited, or removed.

Add: Displays the Component Palette: Create Palette Page dialog box.

Remove: Deletes the selected page from the palette.

Rename: Renames a specified page.

6.24 Component Palette: Create Palette Page

Lets you create a new page for the component palette. Specify a name of the page, and select the type of page from a list.

Page Name: Name of the page. Suggestion: Include the type of page in the name, perhaps naming pages in the form name_type_page.

Page Type: Page type, selected from the list.

6.25 Component Palette: New/Edit Code Snippet

Lets you create or edit a code snippet, which you will be able to drag into files that you edit.

Name: Name for the code snippet.

Image: Icon image to be associated with the snippet.

Code: Code for the snippet.

6.26 Component Palette: New Section/Rename Section

Lets you create or rename a section in the Code Snippets panel of the Component Palette.

You can create sections in the Code Snippets panel to organize your snippets better. For example, if you have a group of code snippets that pertain to mathematical functions, you can create a new section called Math and group the related snippets under it.

Name: Name of the section.

6.27 Confirm Drop Application

This dialog box is displayed when you right-click an Application Express application and select Drop. To drop the application, click Yes; to keep (not drop) the application, click No.

If the application contains an uninstall script, that script is run before the application is dropped.

Related Topics

Applications (Application Express 3.0.1 and Later)

6.28 Confirm Running SQL

This dialog box is displayed in certain situations when SQL Developer needs to run a setup script on the server. The script is displayed in a text box, where you can view or edit the contents. To allow the script to run, click Yes; to prevent.the script from running, click No.

6.29 Connection Has Uncommitted Changes

This dialog box is displayed if you try to end the active database session while there are transactions to be committed. Select the appropriate option and click OK.

To commit the changes and end the session, select Commit Changes. To roll back the changes and end the session, select Rollback Changes. To cancel the attempt to end the session, select Abort Connection Disconnect. (Selecting Abort Connection Disconnect and clicking OK has the same effect as clicking Cancel.)

6.30 Create New Object

This dialog box is displayed if you click File, then New. Specify the type of object to create. After you click OK, the dialog box for creating that type of object is displayed.

Related Topics

Database Objects

6.31 Create/Edit Chain

This dialog box or pane is used for creating or editing an Oracle Scheduler chain.

(To create a chain, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_CHAIN procedure.)

Name: Name of the chain.

Enabled: Enables the chain. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the chain is created.)

Description: Optional text string that can be used to describe the chain.

Related Topics

6.32 Create/Edit Credential

This dialog box or pane is used for creating a new Oracle Scheduler credential or editing an existing credential.

(To create a credential, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_CREDENTIAL procedure.)

Name: Name of the credential. Cannot be set to NULL. It is converted to upper case unless enclosed in double-quotes. For an existing credential, this field is read-only; to change the name, you must drop the credential and create a new credential with the desired name.

Enabled: If this option is selected, the credential is enabled; if this option is not enabled, the credential is not enabled.

Description: Optional text string that can be used to describe the credential.

User Name: User name to use for login to the host operating system or remote Oracle database. This cannot be set to NULL and is case sensitive. It cannot contain double quotes or spaces.

Password: Password for the user name. This cannot be set to NULL and is case sensitive. The password is stored obfuscated and is not displayed in the Scheduler dictionary views.

Database Role: (Reserved for future use.)

Windows Domain: For a Windows remote executable target, this is the domain that the specified user belongs to. The domain is converted to uppercase automatically.

Related Topics

6.33 Create/Edit/Select Database Connection

The database connection dialog box displays any existing connections. Depending on the context, you can select a connection to connect to the database, edit the information about existing connections, or specify information while creating a new connection.

Connection Name: An alias for a connection to the database using the information that you enter. (The connection name is not stored in the database, and the connection is not a database object.) Suggestion: Include the database name (SID) and user name in the connection name. Example: personnel_herman for connecting to the personnel database as user Herman.

Username: Name of the database user for the connection. This user must have sufficient privileges to perform the tasks that you want to perform while connected to the database, such as creating, editing, and deleting tables, views, and other objects.

Password: Password associated with the specified database user.

Save Password: If this option is checked, the password is saved with the connection information, and you will not be prompted for the password on subsequent attempts to connect using this connection.

Connection Color: Lets you specify a standard or custom color for the border of any SQL Worksheet and other windows associated with the connection. The border can be a helpful visual indicator for preventing confusion; for example, if you use red borders for connections to the production database, it might prevent you from mistakenly dropping a table in the production environment when you intended to drop it in the development environment.

Connection Color does not affect the display of the connection name in the Connections navigator.

Information for Database-Specific Tabs:

Related Topics

6.33.1 Oracle tab

The following information applies to a connection to an Oracle Database.

Connection Type: Select Basic, TNS, LDAP (Lightweight Directory Access Protocol), Advanced, Local/Bequeath (using bequeath protocol: if the client and database exist on the same computer, then a client connection can be passed directly to a dedicated server process without going through the listener), SSH, or Cloud Wallet. (The display of fields changes to reflect any change in connection type.)

Role: The set of privileges to be associated with the connection. For a user that has been granted the SYSDBA system privilege, you can specify a connection that includes the privilege.

OS Authentication: If this option is checked, control of user authentication is passed to the operating system (OS). This allows the specified user to connect to the database by authenticating that user's OS username in the database. No password is associated with the connection since it is assumed that OS authentication is sufficient.

Kerberos Authentication: If this option is checked, credentials can be shared across many Kerberos-enabled applications (for example, to have the same username and password for both the operating system and Oracle Database). Thick driver configuration is done through sqlnet.ora (sqlnet.authentication_services=(KERBEROS) and related parameters), so no username and password are needed. Thin driver configuration uses the configuration (.conf) file and the credentials cache, and uses a service principal and password.

Proxy Connection: If this option is checked, proxy authentication will be used. Displays the Advanced Properties (Connections) dialog box.

The different connection types are:

Basic Connection Type

Hostname: Host system for the Oracle database.

Port: Listener port.

SID: Database name.

Service Name: Network service name of the database (for a remote database connection over a secure connection).

TNS Connection Type

Network Alias: Oracle Net alias for the database. (The list for selecting a network alias is initially filled from all tnsnames.* files on your system, unless you have set the Datbase: Advanced preference Tnsnames Directory to identify the location of the tnsnames.ora file to be used.)

Connect Identifier: Oracle Net connect identifier.

LDAP Connection Type

Enterprise users are authenticated with the Lightweight Directory Access Protocol (LDAP) server. The user login information must be configured in the LDAP server and mapped to a schema in the database. Support for LDAP-compliant directory servers provides a centralized vehicle for managing and configuring a distributed Oracle network. The directory server can replace client-side and server-side localized tnsnames.ora files.

LDAP Server: Select from the list (from <DIRECTORY_SERVER> entries in the ldap.ora file); or enter the directory server location and port (either SSL or non-SSL), for example: system123.example.com:389:636 (ldap-system:nonssl-port:ssl-port)

Context: LDAP administrative context. The contexts available in the selected server are listed.

DB Service: Database connection information: click Load to display a list of database services associated with the selected context. (If an error is displayed, no database services are associated with this context.) If a connection uses the OCI/Thick driver (see the Use OCI/Thick preference under Database: Advanced), the system on which SQL Developer is running must have an Oracle Client installation that contains the JDBC and orai18n libraries, these libraries must be present on the path, and the Oracle Client installation must be version 10.2 or later.

Advanced Connection Type

Custom JDBC URL: URL for connecting directly from Java to the database; overrides any other connection type specification. If you are using TNS or a naming service with the OCI driver, you must specify this information: Example:

jdbc:oracle:thin:scott/@localhost:1521:orcl

Note that in this example, the "/" is required, and the user will be prompted to enter the password.

To use a custom JDBC URL, the system on which SQL Developer is running must have an Oracle Client installation that contains the JDBC and orai18n libraries, is present on the path, and is version 10.2 or later.

SSH Connection Type

Port Forward: Name of the port forward for the SSH session.

SID: Database name.

Service Name: Network service name of the database.

Cloud Wallet Connection Type (previously referred as Cloud PDB Connection)

This connection type is relevant for Oracle Cloud connections that use Oracle Wallet.

Configuration File: Client credentials zip file downloaded from the Cloud service console.

Service: Service name in the client credentials file. This field is automatically filled in after the client credential file is selected.

See Also:

For Kerberos authentication options, see the Database: Advanced preferences.
For configuring Kerberos authentication, see Oracle Database Advanced Security Guide
Connections with Proxy Authentication

6.33.2 TimesTen tab

The following information applies to a connection to an Oracle TimesTen In-Memory Database.

For Username and Password, specify the user name and password of the user account in the TimesTen database.

DSN: Data source name. Select an existing DSN (if any are displayed), or User-specified to create a new DSN. A DSN is a character string that identifies a TimesTen database and includes connection attributes to be used when connecting to the database. A DSN has the following characteristics: its maximum length is 32 characters; it cannot contain spaces; and it consists of ASCII characters except for the following: []{},;?*=!@\

Connection Type (if DNS is user-specified): C/S for client/server mode or Direct for direct mode

Connection String: Connection attributes including database attributes, first connection attributes, general connection attributes, NLS attributes, and Cache Connect attributes.

Oracle Password (for Cache): The password for the TimesTen user account on the Oracle Database. (See the TimesTen documentation for more information.)

See Also:

For information about connection attributes, see Oracle TimesTen Application-Tier Database Cache User's Guide.

For more information about SQL Developer support for TimesTen, see Oracle TimesTen In-Memory Database Support.

For detailed usage and reference information about Oracle TimesTen, see the online documentation that is included in the TimesTen installation. For additional information, go to: http://www.oracle.com/technetwork/database/timesten/

6.33.3 Amazon Redshift tab

The following information applies to a connection to an Amazon Redshift database.

Note that the Amazon Redshift tab is not displayed unless you first download the appropriate connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Amazon Redshift JDBC URL: The JDBC URL of the cluster has the following format: jdbc:redshift://endpoint:port/database, where endpoint is the endpoint of the Amazon Redshift cluster, port is the port number that was specified when the cluster was launched, and database is the database created for the cluster.

You can obtain the URL for the JDBC connection to the database from the cluster configuration in the Amazon Web Services console.

See Also:

For more details about obtaining the JDBC URL, see http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#obtain-jdbc-url

6.33.4 DB2 tab

The following information applies to a connection to an IBM DB2 database.

Note that to connect to an IBM DB2 database, you must first download the db2jcc.jar and db2jcc_license_cu.jar files, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add these files.

Platform: UDB.

Host Name: Host system for the IBM DB2 database.

Port: TCP/IP Port on which the IBM DB2 server will listen.

Enter Database: Name of the IBM DB2 database.

6.33.5 Hive tab

Click the Hive tab (next to the Oracle tab) to enter Hive connection details.

The following information applies to a connection to a Hive database.

Note that to connect to a Hive database, you must first download the Cloudera Hive JDBC driver, which contains the following files: hive-metastore.jar, hive-service.jar, HiveJDBC4.jar, libfb303-0.9.0.jar, libthrift-0.9.0.jar, log4j-1.2.14.jar, ql.jar, slf4j-api-1.5.8.jar, slf4j-log4j12-1.5.8.jar, TCLIServiceClient.jar. (For files whose names indicate a version number, a more recent version might be available.). Note that in SQL Developer 4.2, you need to use the files in Cloudera_HiveJDBC4_*.zip (not Cloudera_HiveJDBC41_*.zip or Cloudera_HiveJDBC3_*.zip). Extract the files from the zip file in a temporary directory. Then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add these files.

Username: User name to connect to Hive Server 2. For example, the user name is “oracle” on Oracle Big Data Appliance without Kerberos. Password might not be required, depending on how authentication was set up for the database.

Host Name: Host system running Hive Server 2.

Port: TCP/IP Port on which the Hive server will listen (default is 10000).

Database: Name of the Hive database. (default is the name of the base database on Hive. You can create other databases using the CREATE DATABASE command, described at https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/AlterDatabase.)

To add new parameters for the connection, click Add. For example, when you connect to a Kerberos–enabled cluster, add the following parameters:

KrbServiceName: Hive Server 2 service name.
AuthMech: 1 indicates a Kerberos connection.
KrbHostFQDN: Hive Server 2 host name.
KrbRealm: Kerberos realm.

See Also:

For more information about installing Hive JDBC drivers, see “Using Oracle SQL Developer to Connect to Hive” in Oracle Big Data SQL User's Guide.

For information about the user preference pane for third-party JDBC drivers, see Database.

6.33.6 JDBC tab

The following information applies to a JDBC connection.

JDBC-ODBC Bridge or Other Third Party Driver: Indicates a JDBC to ODBC bridge driver or another third-party driver.

Data Source (JDBC-ODBC Bridge): Name of an existing ODBC data source.

Extra Parameters (JDBC-ODBC Bridge): Additional parameters for the connection.

JDBC URL (Other Third Party Driver): URL for connecting directly from Java to the database; overrides any other connection type specification.

Driver Class (Other Third Party Driver): The name of the driver class that will be used for the connection (for example, com.microsoft.jdbc.sqlserver.SQLServerDriver). This name can be found in the JDBC driver specification (usually shipped with the driver).

6.33.7 MySQL tab

The following information applies to a connection to a MySQL database.

Note that to connect to a MySQL database, you must first download the appropriate MySQL connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Host Name: Host system for the MySQL database.

Port: TCP/IP Port on which the MySQL server will listen.

Choose Database: Name of the MySQL database.

Zero Date Handling: Because the MySQL JDBC driver cannot handle the default 0000-00-00 date, specify one of the following options for handling this date: Set to NULL to set it to a null value, or Round to 0001-01-01 to set it to 0001-01-01.

6.33.8 PostgreSQL tab

The following information applies to a connection to a PostgreSQL database.

For PostgreSQL migrations, only online capture and online data move are supported. (DDL can be generated, but the data must be moved using the online method.)

Note that to connect to a PostgreSQL database, you must first download the appropriate connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Host Name: Host system for the PostgreSQL database.

Port: TCP/IP Port on which PostgreSQL will listen.

Choose Database: Name of the PostgreSQL database.

6.33.9 SQL Server and Sybase tab

The following information applies to a connection to a Microsoft SQL Server or Sybase Adaptive Server database.

Note that to connect to a Microsoft SQL Server or Sybase Adaptive Server database, you must first download the appropriate connection driver, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add the driver.

Use Default Password: If this option is checked, the SQL Server or Sybase default password for new users and for resetting passwords is used.

Use Windows Authentication: If this option is checked, control of user authentication is passed to the Microsoft Windows operating system. This allows the specified user to connect to the database by authenticating that user's Windows username in the database. No password is associated with the connection since it is assumed that Windows authentication is sufficient.

Host Name: Host system for the Microsoft SQL Server or Sybase Adaptive Server database.

Port: TCP/IP Port on which Microsoft SQL Server or Sybase Adaptive Server will listen.

Retrieve Database: Name of the Microsoft SQL Server or Sybase Adaptive Server database.

6.33.10 Teradata tab

The following information applies to a connection to a Teradata database.

Note that to connect to a Teradata database, you must first download the tdgssconfig.jar and a terajdbc4.jar files, and then click Tools, then Preferences, and use the SQL Developer user preference pane for Database: Third Party JDBC Drivers to add these files. (See also the readme.txt file that is included with the tdgssconfig.jar and a terajdbc4.jar files.)

Host Name: Host system for the Teradata database.

DBS Port: TCP/IP Port on which the Teradata server will listen.

Charset: Character set for the data.

TMODE: Transaction mode: ANSI, TERA (Teradata), or DEFAULT.

To add a connection parameter to the list in the box, click Add; to delete a connection parameter from the list, click Delete.

6.33.11 Creating and Editing Connections

To create a new connection when no connections exist, enter the connection information and click Connect. To test the connection before you create it, click Test.

To create a new connection when one or more connections already exist, click to select an existing connection, change the Connection Name to the desired name, edit other connection information as needed, and click Save or Connect to create the new connection. To test the connection before you create it, click Test.

To edit an existing connection, click in its entry in the Connection Name column, change any connection information except the connection name, and click Save or Connect. To test the connection before you save changes to it, click Test.

6.34 New/Edit Cloud Connection

Use this dialog box to create or edit a connection to an Oracle Cloud Database Schema Service instance, which does not offer Oracle Net (SQL*Net) access. You will need the following information from the Welcome to Oracle Cloud email you received when the Cloud service with database schema information was activated:

Service Home: the Cloud service URL
User: the SFTP user for this Cloud service
Secure FTP Site: the SFTP site URL

When you create a Cloud connection, you can use it immediately within the SQL Developer graphical interface; however, to use that connection with the SQL Developer command line interface, you must first exit the SQL Developer session in which you created the connection.

Connection Name: A name for this connection to a specified Cloud Database Service instance. Can be any name that you choose.

Database

Username: Username required during sign in when launching the Cloud service. (You cannot specify the password here. When you attempt to connect to the Cloud connection, an Authentication dialog box is displayed, prompting you for the user name and password.)

URL: Service Home URL from the Welcome to Oracle Cloud email under Service Details. May be in the following form:

https://database-<identity-group>.db.<data-center>.<host-name>/<server-path>/

Advanced: Displays the Advanced Connection Information dialog box.

OSS

Enable OSS: Select to use OSS (Oracle Storage Service) as the staging area for data transfer to cloud services.

Identity Domain, Service Name, User Name, Password: You can find these details in the Welcome to Oracle Cloud email that you received for the OSS service.

Service Base URL: The Service Base URL is automatically generated. You can edit this field if needed.

SFTP

Username: Secure FTP user name that you received in the Welcome to Oracle Cloud email under Service Details. (You cannot specify the password here. You will be prompted for the password when necessary.)

Hostname: Secure FTP Site from the Welcome to Oracle Cloud email under Service Details.

Port: Secure FTP port number (for example, 22).

6.34.1 Advanced Connection Information

This dialog box is displayed if you click Advanced in the New/Edit Cloud Connection dialog box.

HTTP or HTTPS: Select the desired protocol. (HTTPS provides secure communication.)

Hostname: <host-name> part of the URL in the New/Edit Cloud Connection dialog box.

Port: Port number (if any).

Server Path: <server-path> part of the URL in the New/Edit Cloud Connection dialog box. For example: /apex/

Service Name: Service name. For example: _sqldev/

Related Topics

New/Edit Cloud Connection

6.35 New/Edit NoSQL Connection

Use this dialog box to create or edit a connection to an Oracle NoSQL database.

Connection Name: A name for this connection to a Oracle NoSQL database. Can be any name that you choose.

Store

Username: User name on the NoSQL store.

Password: Password for the specified user.

Host: Host name for the NoSQL store.

Port: Port number on the specified host.

Store: Name of the NoSQL store. (kvstore is a typical KVLite store name.)

Security

SSL: Specifies the use of Secure Sockets Layer (SSL) encryption and network port restrictions, which enhance protection from network intrusion.

TrustStore: Location of the Java truststore file that is referenced by the SSL_TRUSTSTORE_FILE_PROPERTY property. You can click Browse to select the TrustStore file.

Oracle Wallet: Specifies Oracle Wallet as the password storage mechanism. This option is only available in the Oracle NoSQL Database EE version. You can click Browse to select the Wallet file.

Properties File: The security file that contains property settings for the login. You can click Browse to select the properties file.

6.36 Rename Model (Migration)

This dialog box is displayed when you right-click a captured or converted model and select Rename Model. To rename the model, change the name and click OK.

6.37 Delete Confirmation

This dialog box is displayed in certain situations to confirm whether you want to delete the selected object or objects. To perform the deletion, click Yes; to cancel the deletion request, click No.

6.38 Delete Confirmation (Migration)

This dialog box is displayed when you right-click a migration repository object under a captured or converted model and select Delete. To delete the object, click Yes.

If you do not want to be asked to confirm deletions of migration repository objects in the future, enable (check) Skip This Message Next Time. This will cause future deletions to occur when you right-click and select Delete.

6.39 Rename Database Item (Migration)

This dialog box is displayed when you right-click a database object under a captured or converted model and select Rename. To rename the object, change the name and click OK.

6.40 Select Connection

Use this dialog box to select a database or other type of connection for use with a specific SQL Developer feature (for example, the SQL worksheet, the Data Miner navigator, or the Reports navigator). After you click OK, the interface for the component is displayed, with the current user the same as the one specified in the connection.

To create a new connection, click the plus (+) icon; to edit the selected connection, click the pencil icon. In both cases, a dialog box for specifying connection information is displayed.

Related Topics

Create/Edit/Select Database Connection

6.41 Connection Information

Use this dialog box to specify the user name and password for the selected database connection.

If the specified user name does not exist in the database associated with the connection, or if the specified password is not the correct one for that user, the connection is refused.

Related Topics

Database Connections

6.42 No Connection Found

This dialog box is displayed when you attempt to perform an operation that requires a database connection, but no connection currently exists for that operation. For example, you might have opened a SQL file but not selected a connection, or the connection might have disconnected; or you might have tried to perform a schema copy operation without specifying both the From Schema and To Schema connections.

To select a connection in the SQL Worksheet, click OK to close this dialog box, then select a connection from the drop-down list in the SQL Worksheet icon bar.

6.43 Connection Rename Error

This dialog box is displayed when you attempt to rename a database connection to a name that is already used for another connection. For example, you might have forgotten to enter a new name for the connection that you want to rename.

To rename the connection, click OK to close this dialog box, then specify a unique connection name.

6.44 New Folder (Connections)

This dialog box enable you to create or rename a folder for organizing database connections. If you are creating a folder, enter the name of the new folder; if you are renaming a folder, replace the existing name with the desired new name. For information about using folders, see Using Folders to Group Connections.

6.45 Continue After Pause

This dialog box is displayed when a PAUSE statement is encountered in a script that you are running in the SQL Worksheet.

To continue execution at the statement after the PAUSE statement, click Yes. To stop execution and not continue with the statement after the PAUSE statement, click No.

6.46 Select Library

This dialog box is displayed when you click Browse in the Database pane when setting SQL Developer preferences. Use this box to select the library for the specified JDBC driver class.

Related Topics

6.47 Create Library

This dialog box is displayed when you click New in the Select Library dialog box, which is displayed when you click Browse in the Database pane when setting SQL Developer preferences. Use this box to create the library for the specified JDBC driver class.

Related Topics

6.48 CVS: Check Out from CVS

(Applies only if you have added support for CVS.)

Use this dialog box to check out modules from a CVS repository.

Connection Name: Name of the connection to the repository

Module Name: Name of the module to be checked out.

Path: Path to the module.

Get/Refresh Module List: Displays the list of modules or updates the current display.

Destination Folder: Folder into which to place the checked out files.

Use Revision or Tag: If this option is checked, the revision or tag that you specify in the text box is used. To see the available tags, click the binoculars icon.

Prune Empty Folders: If this option is checked, empty folders are removed from the working directory.

6.49 CVS: Create/Edit CVS Connection

(Applies only if you have added support for CVS.)

This information applies to creating or editing a CVS (Concurrent Versions System) connection.

Connection

Access Method: The method by which the client will gain access to and authenticate against the server. The methods available depend on which CVS preferences you have set; the available methods might include External, Password Server, Secure Shell via SSH2, and [Other].

Most of the remaining Connection fields apply only to specific Access Method values.

User Name: A CVS user name known to the repository.

Host Name: Qualified host name or IP address of the CVS server system.

Port: TCP/IP port number on which the repository is listening.

Repository Path: The location of the CVS repository software. The seeded / can be overwritten with a path in the format suitable for your operating system, for example c:\cvs. A simple formatting error, such as a forward slash instead of a backslash, will result in a message asking you to enter a valid repository path.

SSH2 Key: Path and file name for the SSH2 private key file for this connection. You can generate a SSH2 private key file using Generate SSH2 Key Pair.

Generate SSH2 Key Pair: Displays a dialog box for generating an SSH2 key pair (that is, a private key file and a public key file). You specify the private key file in the SSH2 Key box. You add the details of the public key file to the list of public keys on the CVS server system

Use HTTP Proxy Settings: Check (enable) this option if you are behind a firewall and need to use HTTP to access the CVS server.

External Locator Configuration: Displays the External Locator Configuration dialog box, in which you can edit the details of the remote shell client and remote server program.

Root

Value of CVSROOT: CVS root variable made up from the information that you have already provided. This variable provides the client with access details when contacting the server. The format of the seeded variable is: :accessmethod:username@serverlocation:repositorypath

You would not normally need to change this value. One instance when you would change this value is when you are attempting to connect to a CVSNT server through a firewall. In this case, you would add proxy information to the beginning of the username portion, so that the CVS root variable would take the following form: :accessmethod:proxy=proxyname;proxyport=portnumber:username@serverlocation:repositorypath

Connection

Test Connection: Attempts to establish a connection to the CVS repository.

Status: Displays the result of the test (success or an error message).

Name

Connection Name: Name to identify the connection to the CVS repository. The default name is the same as the CVSROOT value.

Summary

Displays the connection information that you have specified. To make any corrections, click Back as needed and modify the information. To create the connection, click Finish.

6.50 CVS: Import to CVS

(Applies only if you have added support for CVS.)

This interface is displayed when you click Versioning, then CVS, then Import Module. It enables you to import local files into the repository as a CVS module.

Module

Select the connection name, enter a name for the module, and optionally enter a descriptive comment about the import operation.

6.51 CVS: Log In to CVS

(Applies only if you have added support for CVS.)

Use this dialog box to log in to the specified CVS repository. You must know the password for the specified user.

Connect Automatically on Startup: If this option is checked, a login operation is performed when you start SQL Developer.

6.52 Data Import Wizard

The Data Import Wizard enables you to import data from files into tables. To import data into a new table, right-click the Tables node in the Connectons navigator and select Import Data. To import into an existing table, right-click the table and select Import Data.

For Cloud Wallet connections with the DBMS_CLOUD package installed, you can also load data from files in cloud storage. For more information, see Using Autonomous Data Warehouse Cloud.

The wizard pages that are relevant for data import from cloud storage files are:

Data Preview
Import Metadata Source
Column Definition
Options
Finish

6.52.1 Data Preview

You can specify preferences that affect the preview display of data to be imported. Several default values are determined by the Database: Utilities: Import user preferences.

When using the Import Wizard, you can save and restore state settings in a configuration file, allowing you to reuse the definition you create in the wizard. A saved state can be opened from this page of the wizard by clicking Restore State and selecting the configuration file, which will input the wizard properties from the saved configuration file. You have the option of saving the state in the Summary page.

If you are loading data into an Autonomous Data Warehouse Client, data can be loaded from a local file or from a file saved in Oracle Cloud Storage. Otherwise, data can only be loaded from a local file.

Source: Select Local File if the files are stored on your computer or select Oracle Cloud Storage if the files are stored in a cloud-based object store.

File: Location of the source file. If the source is Oracle Cloud Storage, copy and paste the URL to the file containing the data. If the file is a local file, select the file using the browser or the drop-down list.

Credential: A valid credential name for cloud storage access that was previously created using dbms_cloud.create_credential. After entering the remaining fields, click Preview to sample the contents of the data file.

Header: If this option is enabled, a header row (not data to be imported) is assumed to start before (Before Skip) or after (After Skip) the number of rows for Skip Rows.

Skip Rows: The number of rows at the start to skip (that is, not be considered as data to be imported). Thus, the combination of Header and Skip Rows determines the total number of rows at the start that are not considered data to be imported.

Format: Format of the file containing data to be imported. For example: .xls (Microsoft Excel), .csv (comma separated value), .dsv (delimiter separated value), or .tsv (tab separated value).

Preview Limit: The maximum number of rows of data to be displayed in the preview pane in the lower part of the box. When the wizard creates a new table, the preview data is used to calculate the size of the columns; therefore, ensure that the preview is a good sample of the data. The displayed data is affected by the Preview Limit and by the Database: Utilities: Import user preference for Preview File Read Maximum, which limits the total number of bytes read from the file.

Note:

The Preview Limit is not available when loading data from Oracle Cloud Storage. In such a case, the maximum number of characters that can be displayed in the preview pane is 20,000.

Encoding: Character set used for encoding of the data to be imported.

Delimiter, Line Terminator, Left Enclosure, Right Enclosure: Select or type the character in the input file that is used for each of these.

6.52.2 Import Method

The Import Method page specifies methods for importing data from local files.

Importing Data from a Local File when not Loading into Autonomous Data Warehouse

Import Method: Select one of the following methods for loading the table definitions and data:

Insert: For new tables, the table will be created and data will be inserted.
Insert Script: A script will be generated with DDL statements to create the new tables and INSERT statements to add the data rows.
External Table: External table DDL statements will be generated for accessing the file as a read-only table.
Staging External Table: A script will be generated with DDL statements to create new tables and a staging external table for reading the file, and with INSERT statements for inserting the data into the table from the staging external table.
SQL*Loader: For new tables, files will be created for running SQL*Loader to load the data; each table can be created during the import, or a script can be generated with DDL statements to create the table.

The number of remaining fields on this page and their availability depend on the load method and whether or not you invoked the wizard on a specific table.

Table Name: Target table in which to import the data.

Staging Table Name: External table to be used for loading the target table.

Commit and Drop Staging Table: If this option is enabled, the staging table is committed and dropped after the import operation. If this option is not enabled, the staging table is not committed or dropped.

Send Create Script to Worksheet: If this option is enabled, after you click Finish SQL, Developer does not immediately perform the import operation, but instead opens a SQL Worksheet with statements that will be used if you click the Run Script icon in the worksheet.

Loading Data to Autonomous Data Warehouse

If the source is a local file, you can import using:

Insert: For new tables, the table will be created and data will be inserted.
Insert Script: A script will be generated with DDL statements to create the new tables and INSERT statements to add the data rows.
SQL*Loader: For new tables, files will be created for running SQL*Loader to load the data; each table can be created during the import, or a script can be generated with DDL statements to create the table.

If the source is a cloud storage file, you can import using:

Cloud Load: A temporary external table is used to stage and populate the Oracle table.
External Table: An external table is created that reads the file in cloud storage and provides read-only access to the data.

Load Options: Provides various options to specify when loading data.

Table Name: Name of the target table to import the data.

6.52.3 Import Metadata Source

This step is applicable only when importing data directly from a file into a table using the cart. You can choose the source for defining the destination table and mapping the source to the target columns.

Derive from file: Derives the definition of the destination table from the data file.
Use Oracle table: Captures the definition of the destination table from an existing table. This definition only includes creating the table with the columns, and does not include other attributes, constraints, and so on.

For loading a complete table definition to the cloud, first drop the table to the cart with DDL selected in the cart row, then drop the data file to the cart. You can still use the table to get the metadata for the file, but DDL should not be selected.

Select the connection that the table is associated with, and click Find. Enter the name of the table, and click Lookup. Select the table name from the box, and click Apply.

6.52.4 Choose Columns

This step of the wizard is applicable only when importing data from a local file using the Insert or Insert Script methods.

Available Columns: Lists the columns from which you can select for import into columns in the table. To select one or more columns, use the arrow buttons to move columns from Available to Selected.

Selected Columns: Lists the columns whose data is to be imported into columns in the database table. To change the order of a selected column in the list for the import operation, select it and use the up and down arrow buttons.

6.52.5 Column Definition

Enables you to specify information about the columns in a database table into which to import the data.

If you are deriving the definition from the file, you can modify the details for any of the columns of the destination table that will be created to import the data. If you are using an Oracle table, the table properties for the columns are used and you can only set the date format.

Match By: The kind of automatic matching from source to target columns: by Name, by Position, or None (use the default). The default for existing tables is by name if a header is present in the file, and by position if no header is present.

Source Data Columns and Target Table Columns: When importing from a table, you can select a source file data column to display its target (Oracle) column properties. For Data Type, select one of the supported types for this import operation. For a VARCHAR2 or NUMBER column, you must specify an appropriate Size/Precision value. You can specify whether the column value can be null (Nullable?), and you can specify a default value (Default).

For importing data from a file, these fields identify the mapping between the source column and the appropriate target column. Confirm that the source fields are mapped correctly to the target columns, and that the properties are specified correctly. Use the Status and the Data boxes to validate the preview values. If there are data incompatibilities, you can still proceed to the next page in the wizard but rows may get rejected as a result.

6.52.6 Options

There are a number of options that can be specified when loading data.

If the source is a cloud storage file, you can import using:

Cloud Load: A temporary external table is used to stage and populate the Oracle table.
External Table: An external table is created that reads the file in cloud storage and provides read-only access to the data.

Set Blank Columns Null: Select this option to set columns containing all blanks to Null.

Truncate Columns to Column Size: Select this option to truncate columns longer than the column size.

Trim Spaces: Specify how to handle leading and trailing spaces in columns.

Reject Row Limit: Specifies the number of reject rows that are allowed when loading. If this limit is exceeded, the load is terminated.

Blank Rows: Specifies how to handle blank rows.

Column Conversion Errors: Specifies how to handle rows with columns having conversion errors.

Missing Field Values at End of Row: Specifies how to handle missing fields at the end of the row.

Copy Log File to Cloud Storage: Copies the log file to the same location as the data file in cloud storage. The log file reports results of the validate or copy of the external table. It contains details of the external table and error messages for each rejected record. The contents of this log is included in the SQL Developer log from the import, so you only need to copy it to cloud storage if you want to retain a permanent copy there.

Copy Bad File to Cloud Storage: Copies the bad file to the same location as the data file in cloud storage. The bad file contains the rejected rows from the validate of the external table.

Generate File Character Set Clause: Select this option to specify a character set, other than the default, to use for the load.

6.52.7 Test

This page of the wizard is only applicable for cloud storage files. You can validate the properties for the load, view the results, preview the external table data and definition, and identify and resolve errors.

Test Row Count: Enter the number of rows to load and test.

A temporary external table is created according to the properties identified in the wizard and then a validate is done. The number of rows specified in Test Row Count is used to limit both the number of successfully returned rows and the rejected rows. This is to ensure that the validation will limit the number of tested rows even in the extreme case of not returning a single valid row. In case of rejected rows, the number of tested rows can be higher than the specified test size.

Note:

The reject limit specified in the Load Options dialog is not used in the test and does not apply. The reject limit will become relevant only if the number of bad rows exceeds the number of rows in the test.

Click Test.

If the status is SUCCESS, the data in all the rows of the file is consistent with the definition of the target table.

Description of the illustration test.png

If the status is WARNING Rejected Rows in Bad File, there are some rows that did not meet the table definition. To view the rejected rows, click the Bad File Contents tab, and then click Load Bad Preview. The bad file is copied to the cloud storage in the same location as the data file. Identify the issue, correct the properties if required in the Column Definition page, and click Test again.

Description of the illustration badfile.png

Test Results tab: Displays the results of the test.

External Data tab: Displays the external table data according to the properties that were defined.

External Table DDL tab: Displays the DDL statements for the external table. This is a temporary table that is useful for troubleshooting.

Bad File Contents tab: Displays the rows that were included in the bad file.

6.52.8 Finish

Save State: Save the options specified in the import in a configuration file for later use.

Verify Parameters Before Import: You are encouraged to verify the import parameters. If any test fails, the Information column contains a brief explanation, and you can go back and fix any errors before you can click Finish.

To perform the import operation, click Finish.

Related Topics

6.53 Export/Import Connections

The Export Connections wizard exports information about one or more database connections to a JSON file. The Import Connections wizard imports connections that have been exported. Database connections that you import are added to any connections that already exist.

6.53.1 Export Connections

Select Connections

You can select and deselect all connections or specific connections for the operation.

Destination File

File Name: Name of the JSON file to contain definitions of the connections to be exported. Use the Browse button to specify the location.

Password Handling

Specify how passwords should be handled in the exported file:

Encrypt all passwords with a key: Specify an Encryption Key value and verify that value. (Anyone who attempts to import connections from the exported file will need to know the encryption key in order to be able to use connections with saved passwords without being prompted for the password.)
Remove all passwords from the exported connections: Removes any saved passwords from the exported connections. (If the connections are later imported, users will need to know the passwords for connections that they plan to use.)

Summary

Displays a summary of the options you specified. To make any changes, press Back as needed and change the information. To start the operation, click Finish.

6.53.2 Import Connections

Source File

File Name: Name of the JSON file that contains definitions of the connections to be imported. Use the Browse button to specify the location.

Password Handling

Either specify the key that was used to encrypt the passwords when the connections were exported, or remove all passwords for the imported connections:

Use a key to decrypt all passwords: Specify the Encryption Key value hat was used to encrypt the passwords for the export operation. (If you specify an incorrect encryption key, this option has the same effect as the option to remove all passwords during the import operation.)
Remove all passwords from the exported connections: During the import operation, removes any passwords that are saved in exported connections. (Users of the imported connections will need to know the passwords for connections that they plan to use.)

Select Connections

You can select and deselect all connections or specific connections for the operation.

Duplicate Connections: Determines what happens each existing connection that has the same name as a connection in the source file used for the import operation.

Rename: Gives each duplicate connection a new name similar to the name of the existing connection.
Replace: Replaces the information for the existing connection with the information for that imported connection.

Summary

Displays a summary of the options you specified. To make any changes, press Back as needed and change the information. To start the operation, click Finish.

Related Topics

Database Connections

6.54 Create/Edit Database Destination

This interface is used for creating or editing an Oracle Scheduler database destination.

(To create a database destination, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION procedure.)

Name: Name of the database destination. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the database destination. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the database destination is created.)

Description: Optional text string that can be used to describe the database destination.

Connection Info: An Oracle Net connect identifier that is resolved to the Oracle database instance being connected to.

Agent: The external destination name of the Scheduler agent to connect. Equivalent to an agent name.

Related Topics

6.55 Create/Edit Destination Group (Database or External)

This interface is used for creating a new Oracle Scheduler database or external destination group.

Properties

Name: Name of the destination group. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the destination group. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the destination group is created.)

Description: Optional text string that can be used to describe the destination group.

Members

Select members for the group, and specify whether to use the default credential for all members.

Related Topics

6.56 Create/Edit Database Link

The following information applies to a database link, which is a database object in one database that enables you to access objects on another database, as explained in Database Links (Public and Private).

Public: If this option is checked, the database link is public (available to all users). If this option is not checked, the database link is private and is available only to you.

Schema: Database schema in which to create the database link.

Name: Name of the database link. Must be unique within a schema.

Host Name: The service name of a remote database. If you specify only the database name, Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, you must specify the complete service name.

Current User: Creates a current user database link. The current user must be a global user with a valid account on the remote database. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

Fixed User: Creates a fixed user database link, for which you specify the user name and password used to connect to the remote database.

Shared: If this option is checked, a single network connection is used to create a public database link that can be shared among multiple users. In this case, you must also specify the Authentication information.

Authentication - User Name and Password: The user name and password on the target instance. This information authenticates the user to the remote server and is required for security. The specified user and password must be a valid user and password on the remote instance.

DDL tab

You can review and save the SQL statement that SQL Developer will use to create the database link.

Related Topics

Database Objects

6.57 Create/Edit Index

The following information applies to an index, which is a database object that contains an entry for each value that appears in the indexed column or columns of the table or cluster and provides direct, fast access to rows, as explained in Indexes. For detailed information about all index-related options, see the CREATE INDEX reference section in Oracle Database SQL Language Reference.

Schema: Database in which to create the index (often the same as the user creating the index).

Name: Name of the index. Must be unique within a schema.

6.57.1 Definition

Table Schema: Database schema that owns the table associated with the index.

Table: Name of the table associated with the index.

Index Type: The type of Oracle index. Non-unique means that the index can contain multiple identical values; Unique means that no duplicate values are permitted; Bitmap stores rowids associated with a key value as a bitmap; Domain lets you define an index for a specialized domain (for example, for a spatial index: Indextype Schema = MDSYS, Indextype = SPATIAL_INDEX).

Expressions: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.

For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial, click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.

Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).

Related Topics

Create a Table (BOOKS)

6.57.2 Properties

Enables you to specify index properties

Key Compression: You can enable key compression is enabled, which eliminates repeated occurrence of key column values and may substantially reduce storage. If this option is checked, you can enter an integer to specify the prefix length (number of prefix columns to compress).

Parallel Degree and Degree: If you enable parallel creation of the index, specify Default for the default degree (the optimum degree of parallelism is automatically calculated) or Select to specify an integer value for the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.)

6.57.3 Storage

Storage Options: Lets you specify Storage Options for the index.

6.57.4 Partitions

When applicable, enables you to specify whether the index is not partitioned, locally partitioned, or globally partitioned. If you specify Local or Global, additional fields are displayed relevant to the selected partitioning option.

Local: Specifies that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its associated table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

Global: Specifies that the partitioning of the index is user defined and is not equipartitioned with the underlying table. You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns. The partitioning column list must specify a left prefix of the index column list. If the index is defined on columns a, b, and c, then for the columns you can specify (a, b, c), or (a, b), or (a, c), but you cannot specify (b, c) or (c) or (b, a). If you omit the partition names, then Oracle Database assigns names of the form SYS_Pn.

6.57.5 DDL

Read-only display of the DDL statement or statements to create or edit the index using the current specifications.

Save: Click to save the DDL statement or statements to a ,sql file.

Related Topics

Database Objects

6.58 Create File Watcher

This interface is used for creating a new Oracle Scheduler file watcher.

(To create a file watcher, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_FILE_WATCHER procedure.)

Name: Name of the file watcher. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the file watcher. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the file watcher is created.)

Description: Optional descriptive text.

Destination: Name of an external destination. You create an external destination by registering a remote Scheduler agent with the database.The view ALL_SCHEDULER_EXTERNAL_DESTS lists valid external destination names. If this parameter is null, the file watcher is created on the local host.

Directory Path: Directory in which the file is expected to arrive. The single wildcard '?' at the beginning of the path denotes the Oracle home path. For example, '?/rdbms/log' denotes the rdbms/log subdirectory of the Oracle home directory.

File Name: Name of the file to look for. Two wildcards are permitted anywhere in the file name: '?' denotes any single character, and '*' denotes zero or more characters.

Credential Name: Name of a valid credential object. The file watcher uses the credential to authenticate itself with the host operating system to access the watched-for file. The file watcher owner must have EXECUTE privileges on the credential.

Min File Size: Minimum size in bytes that the file must be before the file watcher considers the file found.

Steady State Duration: Minimum time interval that the file must remain unchanged before the file watcher considers the file found. Cannot exceed one hour. If null, an internal value is used. The minimum value is 10 seconds. Oracle recommends similar steady state duration values for all file watchers for efficient file watcher job operation. Also, the repeat interval of the file watcher schedule must be equal or greater than the steady state duration value.

Related Topics

6.59 Create Filter

This dialog box is displayed when you click New to add a user-defined exclusion filter when importing files into a repository.

Filter: Shell filename pattern, which can contain both normal characters and meta-characters, including wildcards. (See the supplied Selected Filters list for typical patterns.) For example, to exclude files with the extension xyz, enter the following: *.xyz

When you click OK, the specified filter is added to the Selected Filters list.

6.60 Create Function or Procedure

Use this dialog box to create a PL/SQL subprogram (function or procedure).

Schema: Database schema in which to create the subprogram.

Name: Name of the subprogram. Must be unique within a schema.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

Parameters: Specify information about parameters. To add a parameter, click the Add Parameter (+ or sign) icon; to delete a parameter, select it and click the Remove Parameter (X) icon.

Name: Name of the parameter.
Mode: IN for input, OUT for output, or IN OUT for both input and output.
No Copy: Requests that the compiler pass the corresponding actual parameter by reference instead of value. Each time the subprogram is invoked, the optimizer decides, silently, whether to obey or disregard NOCOPY.
Data Type: Data type for the parameter, If the desired value is not included in the drop-down list, you can type it in the box.
Default Value: Value used if no value is specified at run time.

After you click OK, the subprogram is created and is displayed in the Editor window, where you can enter the details.

Related Topics

6.61 Create/Edit Job

This interface is used for creating a new Oracle Scheduler job or editing an existing job. For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

(To create a job, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_JOB procedure, which is documented in Oracle Database PL/SQL Packages and Types Reference.)

6.61.1 Job Details

Job Name: Name of the job.

Enabled: If this option is specified, validity checks will be made and the job will be created enabled if all the checks are successful. If this option is not specified, the job is not created enabled.

Description: Optional text string that can be used to describe the job.

Job Class: Name of the job class to which this job belongs.

Type of Job: Type of object to be executed by the job: PL/SQL Block, Chain, Stored Procedure, Named Program, or Executable.

When to Execute Job: When to execute the job: Immediate (immediately on creation, and once only), Once (once, at a specified time), Repeating, Queue, File Watcher, Schedule (using a named schedule object). If you specify anything other than Immediate, you are prompted for additional information.

For Repeating, you can click the pencil icon to specify a Repeat Interval with details. If you check Advanced, you can use the predefined interval scheme or a user-defined one, or a combination.

6.61.2 Destination

Local (local system), Remote (the database destination for a remote database job, or external destination for a remote external job), or Multiple (the job runs on all destinations).

Depending on what destination you selected for the job, select the local credential, the remote credential and destination, or the multiple group.

6.61.3 Job Arguments

If the job expects arguments to be passed, the Name and Data Type of each argument are listed. For each input or input/output argument, specify the appropriate Value.

6.61.4 Notification

Select Job Events for which to send notification email messages. For each message, you can specify recipient email addresses and the sender (or no sender), and you can modify the body of the message.

6.61.5 Properties

Auto Drop: Determines whether the job is to be automatically dropped after it has completed or has been automatically disabled.

Restartable: Determines whether the job can be restarted in case of failure.

Stop on Window Close: If the schedule of a job is a window or a window group, TRUE causes the job to stop once the associated window is closed, and fALSE causes the job to continue after the window closes. (Note that if the job is allowed to continue, its resource allocation will probably change because closing a window generally also implies a change in resource plans.)

Follow Default Time Zone: Determines whether if the job start date is null, then when the default time zone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

Parallel Instances: For an event-based job, determined what happens if an event is raised and the event-based job that processes that event is already running. FALSE causes the new event to be ignored. TRUE causes an instance of the job to be started for every instance of the event, and each job instance is a lightweight job so multiple instances of the same event-based job can run in parallel.

Job Style: Style of the job being created: REGULAR (regular job) or LIGHTWEIGHT (lightweight job). A lightweight must reference a program object. Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Job Priority: The priority of this job relative to other jobs in the same class as this job. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. It can be a value from 1 through 5, with 1 being the first to be picked up for job execution.

Job Weight: (Do not change the value. Shown only for connections to Oracle Database releases before 11.2.)

Logging Level: Determines how much information is logged: DBMS_SCHEDULER.LOGGING_OFF (no logging), DBMS_SCHEDULER.LOGGING_FAILED_RUNS (only jobs that failed, with the reason for failure), DBMS_SCHEDULER.LOGGING_RUNS (all runs of each job in this class), or DBMS_SCHEDULER.LOGGING_FULL (all operations performed on all jobs).

However, if the job class has a higher (more detailed) logging level than the level specified for the job, the job class logging level is used.

Max Runs: The maximum number of consecutive scheduled runs of the job.

Max Failures: The number of times a job can fail on consecutive scheduled runs before it is automatically disabled.

Instance ID: In an Oracle Real Application Clusters environment., the instance ID of the instance that the job must run on.

Raise Events: Determines at what stages of the job execution to raise events.

Max Run Duration: Maximum amount of time that the job should be allowed to run. Its data type is INTERVAL DAY TO SECOND. If this attribute is set to a nonzero and non-null value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.

Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

Store Output: If set to TRUE, then for job runs that are logged, all job output and error messages are stored in the *_JOB_RUN_DETAILS views. If set to FALSE, then the output and messages are not stored.

Reset to Defaults: Resets all properties to their default values.

6.61.6 Summary/SQL

May include a summary in hierarchical form or the PL/SQL statement that will be used to implement your specifications, or both.

Related Topics

6.62 Create/Edit Job Class

This interface is used for creating a new Oracle Scheduler job class or editing an existing job class.

(To create a job class, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure, which is documented in Oracle Database PL/SQL Packages and Types Reference.)

Name: Name of the job class. If you specify a schema, it must be SYS. For an existing job class, this field is read-only; to change the name, you must drop the job class and create a new job class with the desired name.

Description: Optional text string that can be used to describe the job class.

Logging Level: Specifies how much information is written to the job log: RUNS (detailed information for all runs of each job in this class), FULL (RUNS plus all operations performed on all jobs in this class), or OFF (no logging).

Log Retention Period (days): Number of days that job log entries for jobs in this class are retained. The range of valid values is 0 through 999. If set to 0, no history is kept. If NULL (the default), retention days are set by the log_history Scheduler attribute (set with SET_SCHEDULER_ATTRIBUTE).

Service Name: The database service that the jobs in this class will have affinity to. If no service is specified, the job class will belong to the default service, which means it will have no service affinity and any one of the database instances within the cluster might run the job.

Resource Consumer Group: Resource consumer group this class is associated with. If no resource consumer group is specified, the job class is associated with the default resource consumer group.

Related Topics

6.63 Create/Edit Materialized View Log

User this dialog box to create of edit a materialized view log, which is a table associated with the master table of a materialized view. For more information, see Materialized View Logs.

Schema: Database schema in which to create the materialized view log.

Name: Name of the master table of the materialized view to be associated with this materialized view log.

Properties tab

Tablespace: Tablespace in which the materialized view log is to be created.

Logging: LOGGING or NOLOGGING, to establish the logging characteristics for the materialized view log.

Row ID: Yes indicates that the rowid of all rows changed should be recorded in the materialized view log; No indicates that the rowid of all rows changed should not be recorded in the materialized view log.

Primary Key: Yes indicates that the primary key of all rows changed should be recorded in the materialized view log; No indicates that the primary key of all rows changed should not be recorded in the materialized view log.

New Values: INCLUDING saves both old and new values for update DML operations in the materialized view log; EXCLUDING disables the recording of new values in the materialized view log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify INCLUDING.

Cache: For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this log are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

Parallel: If this option is checked, parallel operations will be supported for the materialized view log.

Object ID: For a log on an object table only: Yes indicates that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log; No indicates that the system-generated or user-defined object identifier of every modified row should not be recorded in the materialized view log.

Sequence: Yes indicates that a sequence value providing additional ordering information should be recorded in the materialized view log; No indicates that a sequence value providing additional ordering information should not be recorded in the materialized view log. Sequence numbers (that is, Yes for this option) are necessary to support fast refresh after some update scenarios.

Available Filter Columns: Additional columns, which are non-primary-key columns referenced by subquery materialized views, to be recorded in the materialized view log. To select one or more filter columns, use the arrow buttons to move columns from Available to Selected.

DDL tab

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

To save the SQL statement to a script file, click Save and specify the location and file name.

Related Topics

Materialized View Logs
Create/Edit View (information related to materialized views)

6.64 Create PL/SQL Package

Use this dialog box to create a package to contain PL/SQL subprograms (functions or procedures, or a combination).

Schema: Database schema in which to create the PL/SQL package.

Name: Name of the package. Must be unique within a schema.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

The package is created and is displayed in the Editor window, where you can enter the details.

Related Topics

Packages

6.65 Create Pluggable Database

Use this dialog box to create a PDB.

Database Name: Name of the PDB.

Admin Name: Administrative user who has or can be granted the privileges required to perform administrative tasks on the PDB.

Admin Password: Password for the Admin Name database user.

File Name Conversions: Determines how the database generates the names of files (such as data files and wallet files) for the PDB.

None: The database first attempts to use Oracle Managed Files to generate file names. If you are not using Oracle Managed Files, then the database uses the PDB_FILE_NAME_CONVERT initialization parameter to generate file names.
Custom Names: Select a Source Files/Target Files pair.
Custom Expressions: Specify one or more Source File Expression/Target File Expression pairs. Each pair item is a string found in names of files associated with the seed (when creating a PDB by using the seed), associated with the source PDB (when cloning a PDB), or listed in the XML file (when plugging a PDB into a CDB).

Storage: You can specify storage limits for the PDB total size or temporary tablespace usage, or both; or unlimited storage for either or both.

Related Topics

Multitenant Container Database (CDB)

6.66 Create/Edit Program

This dialog box is used for creating or editing an Oracle Scheduler program, which can include creating a new program using details from an existing program (Create Like). For more information about job scheduling, see Scheduling Jobs Using SQL Developer.

(To create a program, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_PROGRAM procedure.)

Name: Name of the program. The name has to be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema.

Enabled: If this option is specified, validity checks will be made and the program will be created enabled if all the checks are successful. If this option is not specified, the program is not created enabled.

Description: Optional text string that can be used to describe the program.

Type of program:

PL/SQL Block: The program is a PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0. Enter or paste in the complete PL/SQL code, or edit the existing code.
Stored Procedure: The program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with INOUT or OUT arguments are not supported.

Schema: Schema of the stored procedure. I not specified, the schema of the job is assumed.

Procedure: Name of the stored procedure.

Arguments: For each argument: name, data type, default value, and whether it is an input, output, or input/output argument.
Remote Stored Procedure: The program is a remote stored procedure. You specify the procedure name and any arguments.

Procedure: Name of the stored procedure.

Arguments: For each argument: name, data type, default value, and whether it is an input, output, or input/output argument.
Executable: The program is external to the database. External programs implies anything that can be executed from the operating system's command line. AnyData arguments are not supported.

Executable: Name of the external executable, including the full path name, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at-sign ('@') and the job is local, the at-sign is replaced with the SID of the current Oracle instance.

Arguments: For each argument: name, data type, default value, and whether it is an input, output, or input/output argument
Script: The program is a SQL Script (SQL*Plus statements), Backup Script (RMAN commands), or External Script (operating system commands). Enter or paste the script text in the box.

Properties tab

Enables you to set program properties. For most properties the default is null, but you can check the box to specify a value.

Detached: TRUE if the program is a detached job; otherwise, FALSE. Use a detached job to start a script or application that runs in a separate process, independently and asynchronously to the Scheduler. A detached job typically starts another process and then exits. Upon exit (when the job action is completed) a detached job remains in the running state. The running state indicates that the asynchronous process that the job started is still active. When the asynchronous process finishes its work, it must connect to the database and call DBMS_SCHEDULER.END_DETACHED_JOB_RUN, which ends the job.

Max Runs: Maximum number of runs before the program is marked as completed.

Max Failures: Maximum number of failures tolerated before the program is marked as broken.

Max Run Duration: Maximum run duration of the program.

Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

Related Topics

6.67 Create/Edit Role

This dialog box is used for creating a new database role or editing an existing database role. The role properties are grouped under several tabs.

To create or edit a role, the user associated with your database connection must have the DBA role. You should also be familiar with the main concepts and techniques documented in Oracle Database Administrator's Guide and in the chapter about configuring privilege and role authorization in Oracle Database Security Guide.

Role tab

Specifies general properties for the role.

Role Name: The role name string. For an existing role, this field is read-only; to change the name, you must drop the user and create a new user with the desired name.

New Password: If you specify a password, the role is a local role and a user must specify the password to the database when enabling the role. If you do not specify a password, the role is authorized by the database and no password is required to enable the role.

If you specify a password, you must type the same password string for Confirm Password.

Granted Roles tab

Specifies roles to be granted to the role being created or modified. For each listed role, you can check Granted to grant the specified role to this role, Admin to permit the role to grant this role to other users or roles, and Default to use the default settings for Granted and Admin.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None, Default All, Default None); then, you can specify other settings for individual roles.

System Privileges tab

Specifies privileges to be granted to the user. For each privilege, you can check Granted to grant the privilege, and Admin Option to permit the user to grant the privilege to other users.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None); then, you can specify other settings for individual privileges.

SQL tab

Displays the SQL statements that SQL Developer will use to create (after executing a CREATE ROLE statement) a new user or to edit an existing role. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

Related Topics

Using DBA Features in SQL Developer

6.68 Create/Edit Schedule

This dialog box is used for creating or editing an Oracle Scheduler schedule.

(To create a schedule, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_SCHEDULE procedure.)

Properties tab

Name: Name of the schedule. The name has to be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema.

Description: Optional text string that can be used to describe the schedule.

When: Specify when jobs that use this schedule are to run: Repeating (specify the repeat interval, start date, and end date), Queue (specify the queue name, agent, condition, start date, and end date), or File Watcher (specify the file watcher object name, condition, start date, and end date).

SQL tab

Displays the DBMS_SCHEDULER.CREATE_SCHEDULE procedure that will be used to create the schedule according to your specifications.

Related Topics

6.69 Create/Edit Sequence

The following information applies to a sequence, which is an object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Schema: Database schema in which to create the sequence.

Name: Name of the sequence. Must be unique within a schema.

Increment: Interval between successive numbers in a sequence.

Start with: Starting value of the sequence.

Min value: Lowest possible value for the sequence. The default is 1 for an ascending sequence and -(10^26) for a descending sequence.

Max value: Highest possible value for the sequence. The default is 10^27 for an ascending sequence and -1 for a descending sequence.

Cache and Cache size: Cache causes sequence values are preallocated in cache, which can improve application performance; Cache size indicates the number of sequence values preallocated in cache. No Cache causes sequence values not to be preallocated in cache.

Cycle: Indicates whether the sequence "wraps around" to reuse numbers after reaching its maximum value (for an ascending sequence) or its minimum value (for a descending sequence). If cycling of values is not enabled, the sequence cannot generate more values after reaching its maximum or minimum value.

Order: Indicates whether sequence numbers are generated in the order in which they are requested. If No Order is specified, sequence numbers are not guaranteed to be in the order in which they were requested.

DDL tab

You can review the SQL statement that SQL Developer will use to create a new sequence or that reflects any changes you have made to the sequence properties.

Related Topics

Sequences

6.70 Create SQL File

Use this dialog box to create a SQL script file and to open the file in a SQL Worksheet for editing.

File Name: Name and extension of the file to be created. The default and recommended extension is .sql.

Directory Name: Directory path for the file. To specify a directory, you can click Browse. The default directory is the Location of User-Related Information.

6.71 Create/Edit Synonym

The following information applies to a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class database object, user-defined object type, or another synonym.

Public: If this option is checked, the synonym is accessible to all users. (However each user must have appropriate privileges on the underlying object in order to use the synonym.) If this option is not checked, the synonym is a private synonym, and is accessible only within its schema.

Schema: Database schema in which to create the synonym.

Name: Name of the synonym. A private synonym must be unique within its schema; a public synonym must be unique within the database.

For - Referenced Schema: Schema containing the object or name to which this synonym refers.

Object Based: Specify the object to which this synonym refers.

Name Based: Enter the name of the object to which this synonym refers.

DDL tab

You can review the SQL statement that SQL Developer will use to create a new synonym or that reflects any changes you have made to the synonym properties.

Related Topics

Synonyms (Public and Private)

6.72 Create Table (quick creation)

This dialog box (if you do not check the Advanced box) creates a new table quickly by specifying columns and some frequently used features. (If you need to add or change features after you create the table, you can edit the table by clicking the Modify icon while viewing the table or by right-clicking its name in the Connections navigator and selecting Properties, which displays the Create/Edit Table (with advanced options) dialog box.)

To create a new table, the only things you must do are specify the schema and the table name, add the necessary columns, and click OK. Although it is not required, you should also specify a primary key.

Advanced: If this option is checked, the dialog box changes to include an extended set of features for creating the table. For example, you must check this option if you want to create a partitioned table, an index-organized table, or an external table.

Schema: Database schema in which to create the table.

Name: Name of the table. Must be unique within a schema.

Table tab (quick creation)

Specifies properties for each column in the table.

Columns: Lists the columns currently in the table.

Note:

To add a column after the currently selected column, click Add Column; to delete a column, select it and click Remove Column.

Column Name: Name of the column. Must be unique within the table. Suggestion: For a new column, replace any default name, such as COLUMN1.

Type: Data type for the column. The drop-down list includes only selected frequently used data types. To specify any other type for the column, you must use the Columns panel of the Create/Edit Table (with advanced options) dialog box.

Size: For VARCHAR2 data, the maximum size of the column data; for NUMBER data, the maximum number of digits.

Not Null: If this option is checked, the column must contain data; you cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data.

Primary Key: If this option is checked, the column is the primary key, or part of the primary key, for the table. The primary key is the column, or set of columns, that uniquely identifies each row in the table. A primary key column cannot be null.

If you want to have the primary key values automatically populated by a convenient method that uses a before-insert trigger and a sequence, then before you finish creating the table, you must check the Advanced box and use the Primary Key tab, starting with the Populate Primary Key Column field.

To add another column, click Add Column. When you are finished adding columns, either click OK or click the DDL tab to review the CREATE TABLE statement.

DDL tab (quick creation)

You can review and save the CREATE TABLE statement that SQL Developer will use to create a new table or that reflects any changes you have made to the table properties. If you want to make any changes, go back to the Table tab and make the changes there.

When you are finished, click OK.

Related Topics

6.73 Create/Edit Table (with advanced options)

The table dialog box is used for creating a new table or editing an existing table. The table properties are grouped in several panes.

To create a new table, the only things you must do are specify the schema and the table name, add the necessary columns, and click OK. Although it is not required, you should also specify a primary key using the Primary Key Constraint. For other table-related features, use the appropriate tabs; the order in which you visit tabs usually does not matter, although you might find it convenient to visit them in the sequence in this topic. If you are editing an existing table, you can visit the tabs in any order.

If you click OK before you are finished creating or editing the table, right-click the table name in the Connections navigator, select Edit, and continue creating or editing the table.

Schema: Database schema in which to create the table.

Name: Name of the table. Must be unique within a schema.

Table Type: The type of table:

Normal: A regular database table. It can be partitioned .
External: An external table.
Index Organized: An index-organized table.
Temporary Table: A temporary table, which is not stored permanently in the database. The temporary table definition persists in the same way as the definition of a regular table, but the table segment and any data in the temporary table persist only for the duration of either the transaction (Transaction option) or the session (Session option).
Sharded: A sharded table, which is partitioned across multiple shards (databases). The partition properties of the top-level or parent table are propagated to the child tables.
Duplicated: A duplicated table in a sharded database.

The following panes may also be available. (Some panes are available only for tables of specific types or with specific features.)

Columns pane

Constraints

Primary Key Constraint

Unique Constraints

Foreign Keys Constraints

Column Sequences pane

Table Properties pane

LOB Parameters pane

Partitioning pane

Subpartition Templates pane

Partition Definitions pane

Columns pane

External Table Properties pane

Comment pane

DDL pane

6.73.1 Columns pane

Specifies properties for each column in the table.

Columns: Lists the columns currently in the table. To add a column, click the Add Column (+) icon; to delete a column, select it and click the Remove Column (X) icon; to move a column up or down in the table definition, select it and use the up-arrow and down-arrow buttons.

To copy in column definitions from another table, click the Copy Columns icon to display the Copy Columns dialog box.

Note:

After you add a column, to add another column, click the Add Column (+) icon.

Name: Name of the column. Must be unique within the table. Suggestion: For a new column, replace any default name, such as COLUMN1.

Datatype: Simple indicates a simple (non-object) data type; Complex indicates an object type. For a complex type, you must specify the schema and the type name (for example, MDSYS and SDO_GEOMETRY for the Oracle Spatial and Graph geometry type).

Type: Name of the data type. Most of the remaining information depends on the specific type.

Precision: For numeric data, the precision (total number of significant digits that can be represented) of the column data.

Scale: For numeric data, the scale (number of digits after the decimal point) of the column data.

Size: For character data, the maximum size of the column data.

Units: For character data, the units represented by the Size: BYTE for bytes or CHAR for characters. This attribute is important if the database can contain data in Unicode format, with multiple bytes for each character.

Default: For relevant types, the default value inserted into the column if no value is specified when a row is inserted.

Cannot be NULL: If this option is checked, the column must contain data; you cannot specify no value or an explicit null value for this column when you insert a row. If this option is not checked, the column can contain either data or no data. A primary key column cannot be null.

Comment: Optional descriptive comment about the column.

To add another column, click the Add Column (+) icon.

6.73.2 Constraints

You can click the plus sign (+) icon to add one or more constraints. For each, specify the type of constraint: Primary Key, Unique, Foreign Key, or Check. (See the information for each type.)

6.73.3 Primary Key Constraint

Specifies the primary key for the table. The primary key is the column, or set of columns, that uniquely identifies each row in the table.

An index is automatically created on the primary key.

Name: Name of the constraint to be associated with the primary key definition. Must be unique within the database.

Enabled: If this option is checked, the primary key constraint is enforced: that is, the data in the primary key column (or set of columns) must be unique and not null.

Available Columns: Lists the columns that are available to be added to the primary key definition.

Selected Columns: Lists the columns that are included in the primary key definition.

To add a column to the primary key definition, select it in Available Columns and click the Add (>) icon; to remove a column from the primary key definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the primary key definition, select it in Selected Columns and use the arrow buttons.

The remaining fields (Populate Primary Key Column through Trigger Name) appear only when you are creating a table. They are not available when you are editing an existing table.

Populate Primary Key Column: When you are creating a table, if you want to use a trigger and a sequence to have a unique value automatically inserted into the primary key column when you insert a new row, specify the primary key column.

From: An existing sequence that you select, or a new sequence whose name you enter. (For a new sequence, SQL Developer creates the sequence automatically using the name that you enter.)

Trigger Name: The name for the before-insert trigger that will be automatically created. This trigger uses the sequence to generate a new value for the primary key when a row is inserted. For an example of using this technique, see the tutorial section Create a Table (TRANSACTIONS).

6.73.4 Unique Constraints

Specifies one or more unique constraints for the table. A unique constraint specifies a column, or set of columns, whose data values must be unique: each data value must not be null, and it must not be the same as any other value in the column.

For a multicolumn unique constraint, the combination of values must be unique, and no column in the constraint definition can have a null value. For example, if you specify the office_name and city columns for a unique constraint, you could not have two Sales offices in Chicago, but you could have a Sales office in Chicago and a Sales office in Atlanta.

Unique Constraints: Lists the unique constraints currently defined on the table. To add a unique constraint, click the Add button; to delete a unique constraint, select it and click the Remove button.

Note:

After you add a unique constraint, to add another unique constraint, click the Add button.

Name: Name of the unique constraint. Must be unique within the database.

Enabled: If this option is checked, the unique constraint is enforced.

Available Columns: Lists the columns that are available to be added to the unique constraint definition.

Selected Columns: Lists the columns that are included in the unique constraint definition.

To add a column to the unique constraint definition, select it in Available Columns and click the Add (>) icon; to remove a column from the unique constraint definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the unique constraint definition, select it in Selected Columns and use the arrow buttons.

6.73.5 Foreign Keys Constraints

Specifies one or more foreign keys for the table. A foreign key specifies a column ("local column"), each of whose data values must match a value in the primary key or unique constraint of another table.

Foreign Keys: Lists the foreign keys currently defined on the table. To add a foreign key, click the Add button; to delete a foreign key, select it and click the Remove button.

Note:

After you add a foreign key, to add another foreign key, click the Add button.

Name: Name of the foreign key definition. Must be unique within the database.

Enabled: If this option is checked, the foreign key is enforced.

Referenced Schema: Name of the schema containing the table with the primary key or unique constraint to which this foreign key refers.

Referenced Table: Name of the table with the primary key or unique constraint to which this foreign key refers.

Referenced Constraint: Name of the primary key or unique constraint to which this foreign key refers.

Associations: Local Column: Lists the column in the currently selected (local) table that is included in the foreign key definition. For each local column in the foreign key definition, select the name of a column in the local table.

Associations: Referenced Column on [table]: For each local column, identifies the column in the other (foreign) table that must have a value matching the value in the local column.

6.73.6 Check Constraints

Specifies one or more check constraints for the table. A check constraint specifies a condition that must be met when a row is inserted into the table or when an existing row is modified.

Check Constraints: Lists the check constraints currently defined on the table. To add a check constraint, click the Add button; to delete a check constraint, select it and click the Remove button.

Note:

After you add a check constraint, to add another check constraint, click the Add button.

Name: Name of the check constraint definition. Must be unique within the database.

Enabled: If this option is checked, the check constraint is enforced.

Condition: Condition that must be met for a row. Can be any valid CHECK clause (without the CHECK keyword). For example, to indicate that the value in a numeric column named RATING must be from 1 to 10, you can specify: rating >=1 and rating <= 10

To add another check constraint, click the Add button.

6.73.7 Indexes pane

Specifies properties for each index on the table.

Indexes: Lists the indexes currently defined on the table. (Note, however, that this display does not include any indexes related to primary key and unique key constraints.) To add an index, click the Add Index (+) icon; to delete an index, select it and click the Remove Index (X) icon.

Note:

After you add an index, to add another index, click the Add Index (+) icon.

Name: Name of the index. Must be unique within the schema.

Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.

For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Create a Table (BOOKS)), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.

Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.

Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).

6.73.8 In-Memory pane

Specifies In-Memory Column Store (IM column store) options for the table. The IM column store is an optional, static SGA pool that stores copies of tables and partitions in a special columnar format optimized for rapid scans. The IM column store does not replace the buffer cache, but acts as a supplement so that both memory areas can store the same data in different formats. The IM column store is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Note:

These IM column store features are not related to Oracle TimesTen In-Memory Database.

Define In-Memory Properties: Lets you define IM column store properties.

In-Memory: Enables or disables the IM column store feature for the table.

Compression (for table and for column clauses): The compression method for table data stored in the IM column store. (This data is called in-memory data.) Options: None (no compression), DML (optimized for DML operations and performs little or no data compression), QUERY LOW (least compression except for DML, resulting in the best query performance, QUERY HIGH (more compression than QUERY LOW but less than CAPACITY LOW), CAPACITY and CAPACITY LOW (more compression than QUERY HIGH, resulting in excellent query performance).

Priority: The data population priority for table data in the IM column store. Table data is populated in the IM column store before data for database objects with lower priority. The highest priority is Critical, and the lowest possible priority is None.

Distribute: Applicable only if you are using Oracle Real Application Clusters (Oracle RAC). It controls how table data in the IM column store is distributed across Oracle RAC instances.

Duplicate: Applicable only if you are using Oracle Real Application Clusters (Oracle RAC) on an engineered system. It controls how table data in the IM column store is duplicated across Oracle RAC instances.

Column Clauses: You can enable and disable specific table columns for the IM column store, and identify the data compression method for specific columns.

6.73.9 Storage pane

Lets you specify Storage Options for the table.

Related Topics

Hive Tables: Storage

6.73.10 Column Sequences pane

Enables you to specify sequences and before-insert triggers to be used in populating a column with values. This approach is especially convenient for automatically populating primary key column values with unique values.

Column: Name of the column for which a sequence and a trigger are to be used to insert unique values. The data type of the column must be numeric.

Sequence: None causes no sequence to be used; Existing Sequence uses the sequence that you specify; New Sequence creates a new sequence with a default or specified name.

Trigger: Before-insert trigger that automatically inserts the next value of the specified sequence into the column when a new row is inserted.

6.73.11 Table Properties pane

Enables you to specify table properties such as compression, parallelism, and storage options.

Compress (heap-organized tables only): If this option is checked, data segments are compressed to reduce disk use. This clause is especially useful in environments such as data warehouses, where the amount of insert and update operations is small, and in OLTP environments.

Parallel: If this option is checked, parallel creation of the table is enabled, and the default degree of parallelism is set for queries and the DML INSERT, UPDATE, DELETE, and MERGE statements after table creation. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.

Related Topics

Hive Tables: Table Properties

6.73.12 LOB Parameters pane

Specifies storage options for LOB (large object) columns, enabling you to override the default storage options.

Column: Name of the LOB column.

LOB Parameters: If this option is checked, the specified values for the remaining field are used. If this option is not checked, the default values for all fields are used.

Segment: LOB segment ID.

Tablespace: Name of the tablespace for the LOB data.

Store in Row: If this option is checked, the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information.

Cache: Specifies how Oracle Database should store blocks in the buffer cache:

CACHE: For data that is accessed frequently, indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
NOCACHE: For data that is not accessed frequently, indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. NOCACHE is the default for LOB storage.
CACHE READS: LOB values are brought into the buffer cache only during read operations but not during write operations.

Retention: If this option is checked, old versions of this LOB column and retained. You can specify this option only if the database is running in automatic undo mode and if you do not specify a Pct Version value.

Logging: <DEFAULT> means to use the Oracle Database default. ON means that the table creation and any subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

Chunk: The number of bytes to be allocated for LOB manipulation. If the value is not a multiple of the database block size, then the database rounds up in bytes to the next multiple. The maximum value is 32768 (32K), which is the largest Oracle Database block size allowed. The default CHUNK size is one Oracle Database block.

Pct Version: Specifies the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. You can specify a Pct Version value whether the database is running in manual mode (where it is the default) or automatic undo mode (where Retention is the default). You cannot specify both a Pct Version value and the Retention option.

Free Pools: Specifies the number of groups of free lists for the LOB segment, usually the number of instances in a Real Application Clusters environment or 1 for a single-instance database. You can specify this option only if the database is running in automatic undo mode. You cannot specify both a Free Pools value and the Free Lists fields.

Extents - Initial: Size of the first extent of the table. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Next: Size of the next extent to be allocated to the table. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Min: Minimum number of extents allocated when the table is created.

Extents - Max: Maximum number of extents allocated when the table is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Extents - Pct Increase: Percentage that each extent grows over the previous extent.

Buffer Pool: <DEFAULT> means to use the Oracle Database default. KEEP means to put blocks from the segment into the Keep buffer pool; maintaining an appropriately sized Keep buffer pool lets Oracle retain the database object in memory to avoid I/O operations. RECYCLE means to put blocks from the segment into the Recycle pool; an appropriately sized Recycle pool reduces the number of objects whose default pool is the Recycle pool from taking up unnecessary cache space.

Free Lists: Number of free lists for each of the free list groups for the table. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

Free List Groups: Number of groups of free lists for the table. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.

6.73.13 Partitioning pane

Specifies partitioning options for a partitioned table, which is a table that is organized into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables; however, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. Also, partitioning is entirely transparent to applications.

Partition By: The type of partitioning: RANGE partitions the table on ranges of values from the column list (which for an index-organized tablet must be a subset of the primary key columns of the table); HASH partitions the table using the hash method (rows assigned to partitions using a hash function on values found in columns designated as the partitioning key); LIST partitions the table on lists of literal values from column (useful for controlling how individual rows map to specific partitions);

For a sharded table, the available options are Reference and Consistent Hash. Reference is used for a child table. Consistent Hash is used for the top-level or parent sharded table.

Available: Lists the columns whose values are available to be used in assigning rows to partitions.

Selected: Lists the column whose values are to be used in assigning rows to partitions.

To add a column to the partitioning definition, select it in Available Columns and click the Add (>) icon; to remove a column from the partitioning definition, select it in Selected Columns and click the Remove (<) icon. To move all columns from available to selected (or the reverse), use the Add All (>>) or Remove All (<<) icon. To move a column up or down in the partitioning definition, select it in Selected Columns and use the arrow buttons.

Subpartition By: The partitioning type to be used to create subpartitions within each range partition. Use the Available and Selected column lists select and deselect a column for subpartitioning.

Related Topics

Hive Tables: Partitions

6.73.14 Subpartition Templates pane

Specifies subpartitioning options for a partitioned table. The options depend on the subpartition type, and might include the following.

Hash Quantity: Hash subpartition quantity.

Tablespaces: Available and Selected tablespaces for storage of the data in a subpartition.

Subpartition Templates: Specifications (subpartition templates) to control the placement of rows in each subpartition. Click the Add (+) icon to add a subpartition template that is appropriate for the subpartition type.

Subpartition Details: For each subpartition template, specify a name and (if relevant) a value or set of values that is appropriate for the subpartition type.

Storage: Enables you to specify a tablespace for the subpartition.

6.73.15 Partition Definitions pane

Defines each partition for a partitioned table. The options depend on the partition type, and might include the following.

Partitions: Specifications to control the placement of rows in each partition. Click the Add (+) icon to add a partition specification that is appropriate for the partition type.

Partition Details: For each partition specification, specify a name and (if relevant) a value or set of values that is appropriate for the subpartition type.

Storage: Enables you to specify a tablespace for the partition.

Subpartitions: Enables you to specify subpartition information.

6.73.16 Partition Sets pane

Beginning with the 18.1 release, SQL Developer provides support for composite sharding. The composite sharding method enables you to create multiple shard spaces for different subsets of data in a table partitioned by consistent hash. A shard space is a set of shards that store data that corresponds to a range or list of key values.

When sharding by consistent hash on a primary key, there is often a requirement to differentiate subsets of data within a sharded database in order to store them in different geographic locations, allocate to them different hardware resources, or configure high availability and disaster recovery differently. Usually this differentiation is done based on the value of another (non-primary) column, for example, customer location or a class of service. With composite sharding, data is first partitioned by list or range across multiple shardspaces, and then further partitioned by consistent hash across multiple shards in each shard space.

With composite sharding, as with the other sharding methods, tablespaces are used to specify the mapping of partitions to shards. To place subsets of data in a sharded table into different shardspaces, a separate tablespace set must be created in each shardspace. To store user-defined subsets of data in different tablespaces, Oracle Sharding groups partitions into sets and associates each set of partitions with a tablespace set. Support for partition sets can be considered a logical equivalent of a higher level of partitioning which is implemented on top of partitioning by consistent hash. For more information, see Oracle Database Using Oracle Sharding.

Partition Set By pane

Specifies partitioning options for a partition set.

Partition Set Type: The type of partitioning for the partition set.

Range partitions the table on ranges of values from the column list.
List partitions the table on lists of literal values from the column.

Partition Sets pane

Defines each partition set for a partitioned table.

Partition Sets: Click the Add (+) icon to add a partition set that is appropriate for the partition type.

Define Subpartitions: Select this option to create subpartitions for the partition set. Each partition set, by default, inherits the properties of the table-level subpartition templates, if defined. However, if subpartition templates are defined as the partition set level, then they take precedence over table-level subpartition templates.

Name and Values: For each partition set specification, specify a name and (if relevant) a value or set of values that is appropriate for the subpartition type.

Default: Select this option to indicate the default partition set. A row that does not match the values provided for the partition sets is put in the default partition set.

Table Compression: Type of compression for table data in the tablespace.

Storage: Enables you to specify a tablespace for the partition set. See Storage Options.

Related Topics

Oracle Database VLDB and Partitioning Guide

6.73.17 Index Organized Properties pane

Specifies options for an index-organized table, which is a table in which the rows, both primary key column values and nonkey column values, are maintained in an index built on the primary key. Index-organized tables are best suited for primary key-based access and manipulation.

PCTTHRESHOLD: The percentage of space reserved in the index block for an index-organized table row; must be large enough to hold the primary key. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50; the default is 50.

Key Compression: If this option is checked, key compression is enabled, which eliminates repeated occurrence of primary key column values in index-organized tables. In the box to the right of this field, you can specify the prefix length, which is the number of prefix columns to compress. (This value can be from 1 to the number of primary key columns minus 1; the default prefix length is the number of primary key columns minus 1.)

Include Column: Column at which to divide an index-organized table row into index and overflow portions. The primary key columns are always stored in the index. The Include Column can be either the last primary key column or any non-primary-key column. All non-primary-key columns that follow the Include Column are stored in the overflow data segment.

Mapping Table: If this option is checked, SQL Developer creates a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table. If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.

Overflow: Specifications for the overflow segment. The options are the same as in the Storage Options dialog box.

6.73.18 External Table Properties pane

Specifies options for an external table, which is a read-only table whose metadata is stored in the database but whose data in stored outside the database. Among other capabilities, external tables enable you to query data without first loading it into the database.

Access Driver: The access driver of the external table. The access driver is the API that interprets the external data for the database: ORACLE_LOADER or ORACLE_DATAPUMP. You must specify the ORACLE_DATAPUMP access driver if you specify the AS subquery clause to unload data from one Oracle database and reload it into the same database or a different Oracle database.

Access Type: Type of data to be automatically converted during loads and unloads: BLOB or CLOB.

Default Directory: A default directory object corresponding to a directory on the file system where the external data sources may reside. The default directory can also be used by the access driver to store auxiliary files such as error logs.

Project Column: Determines how the access driver validates the rows of an external table in subsequent queries. ALL processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value would raise an error, such as a data type conversion error, the row is rejected even if that column was not referenced in the select list. REFERENCED processes only those columns in the select list.

The ALL setting guarantees consistent result sets. The REFERENCED setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the ALL setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

Reject Limit: The number of conversion errors can occur during a query of the external data before an Oracle Database error is returned and the query is aborted.

Access Parameters: Values to the parameters of the specific access driver for this external table.

Location Specifications: One or more external data sources. Each is usually a file, but it need not be. Oracle Database does not interpret this clause; it is up to the access driver to interpret this information in the context of the external data. Use the Add (+) icon to add each location specification.

6.73.19 Comment pane

Optional descriptive comment about the table.

6.73.20 DDL pane

You can review and save the CREATE TABLE statement that SQL Developer will use to create a new table or that reflects any changes you have made to the table properties. If you want to make any changes, go back to the relevant tabs and make the changes there.

To save the SQL statement to a script file, click Save and specify the location and file name.

When you are finished, click OK.

Related Topics

Create Table (quick creation) (dialog box including advanced features)
Tables
Hive Tables

6.73.21 Hive Tables

For tables created using an Oracle Hive connection, the information is different for the following:

6.73.21.1 Hive Tables: Partitions

Select one or more columns to use for partitioning, and under Partitions add one item for each partition. Use the tabs below Partitions to define the following information for each:

Details: Specification details.
Location: Partition location.
Storage Format: Predefined file format, Input format class, and Output format class.
SerDe: Java class for the SerDe (Serializer and Deserializer) interface, used to transform a string or binary record into a Java object that Hive can manipulate. For more information, see http://blog.cloudera.com/blog/2012/12/how-to-use-a-serde-in-apache-hive/.

6.73.21.2 Hive Tables: Buckets

Select one or more columns to use for bucket sorting, and under Bucket Sort Columns add one item for each combination of Expression and Order.

6.73.21.3 Hive Tables: Skew

Select one or more columns to use for creating a skewed table, and under Skewed Values add one item for each column to be used. Skewed tables can be used to improve performance for tables where one or more columns have skewed values (values that appear very often). For more information, see https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-SkewedTables.

6.73.21.4 Hive Tables: Storage

Define properties as needed for Native (Delimited or SerDe) or Non-Native storage types.

6.73.21.5 Hive Tables: Table Properties

Add any table properties (Name and Value for each) as needed.

Related Topics

Create Table (quick creation) (dialog box including advanced features)
Tables
Hive Tables

6.74 Storage Options

When you create or edit a table or an index, you can override the default storage options.

Tablespace: Name of the tablespace for the table or index.

Tablespace Set: Name of the tablespace set for a sharded table

Pct Free: Percentage of space in each of the data blocks of the table or index reserved for future updates. You can enter a value from 0 through 99.

Pct Used: Minimum percentage of used space that Oracle maintains for each data block. A block becomes a candidate for row insertions when its used space falls below the Pct Used value. You can enter a value from 1 through 99.

Logging: <DEFAULT> means to use the Oracle Database default. ON means that the table creation and any subsequent direct loader (SQL*Loader) and direct-path INSERT operations against the table, partition, or LOB storage are logged in the redo log file. OFF means that these operations are not logged in the redo log file.

Ini Trans: Number of update transaction entries for which space is initially reserved in the data block header.

Max Trans: Number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Extents - Initial: Size of the first extent of the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Next: Size of the next extent to be allocated to the table or index. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Extents - Min: Minimum number of extents allocated when the table or index is created.

Extents - Max: Maximum number of extents allocated when the table or index is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Pct Increase: Percentage that each extent grows over the previous extent.

Buffer Pool: <DEFAULT> means to use the Oracle Database default. KEEP means to put blocks from the segment into the Keep buffer pool; maintaining an appropriately sized Keep buffer pool lets Oracle retain the database object in memory to avoid I/O operations. RECYCLE means to put blocks from the segment into the Recycle pool; an appropriately sized Recycle pool reduces the number of objects whose default pool is the Recycle pool from taking up unnecessary cache space.

Free Lists: Number of free lists for each of the free list groups for the table or index. The default and minimum value for this parameter is 1, meaning that each free list group contains one free list.

Free List Groups: Number of groups of free lists for the table or index. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.

Related Topics

Create/Edit Table (with advanced options) (dialog box including advanced features)
Create/Edit Index (dialog box)

6.75 Create/Edit Tablespace

This dialog box is used for creating a new tablespace or editing an existing tablespace. (Tablespaces appear on the DBA navigator under Storage.) The tablespace properties are grouped under several tabs.

Name: Name of the tablespace.

Tablespace Type: Permanent (contains persistent schema objects that are stored in data files), Temporary (contains schema objects only for the duration of a session, and stored in temp files), or Undo (a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode).

6.75.1 File Specifications tab

Specifies data files for the tablespace.

File Specifications: Contains one entry for each data file in the tablespace. To add a file, click the Add (plus sign) icon; to remove a file from the tablespace (but not delete the file itself), click the Remove (X) icon.

File Specification Properties: Displays properties of the selected data file.

Name: Name of the data file.

Directory: Name of the directory or folder for the data file. If not specified, the default location for tablespace data files is used.

File Size: Maximum size for the file; you can specify K (kilobytes) or M (megabytes) for the unit of measure.

Reuse Existing File: If this option is enabled and if a file with the same name already exists, the existing file is used. If this option is disabled and if a file with the same name already exists, a warning message is displayed.

Auto Extend: If this option is enabled, automatic extension is performed for a new or existing data file or temp file.

Next Size: The size in bytes of the next increment of disk space to be allocated automatically when more extents are required. The default is the size of one data block.

Max Size: The maximum disk space allowed for automatic extension of the data file.

6.75.2 Properties tab

Specifies general properties for the database role.

File Type: Small File (a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (2^^22) blocks) or Big File (contains only one data file or temp file, which can contain up to approximately 4 billion (2^^32) blocks).

Online Status: Online (makes the tablespace available immediately after creation to users who have been granted access to the tablespace) or Offline (make the tablespace unavailable immediately after creation).

Block Size: You can specify a nonstandard block size for the tablespace. The integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

Logging: Logging (causes the creation of a database object, as well as subsequent inserts into the object, to be logged in the redo log file), No Logging (causes these operations not to be logged in the redo log file), or File System Logging (valid only for logging of SecureFiles LOB segments; specify this if you want to log only metadata changes).

Force Logging: If this option is enabled, Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any NOLOGGING setting for individual objects. The database must be open and in READ WRITE mode.

Segment Management: Auto (the database manages the free space of segments in the tablespace using a bitmap; also called automatic segment-space management), or Manual (the database to manage the free space of segments in the tablespace using free lists).

Extent Management Type: Local (uses bitmaps in the tablespaces themselves to manage extents) or Dictionary (uses the data dictionary to manage extents).

Auto Allocate: If this option is enabled, Oracle Database determines the optimal size of additional extents, with a minimum extent size of 64 KB.

Uniform Size (available only if Auto Allocate is disabled): You can specify an extent size or use the default size of 1 MB. All extents in the tablespace are of this size. Locally managed temporary tablespaces can only use this type of allocation.

Create Sharded: If this option is enabled, a sharded tablespace is created.

6.75.3 Default Parameters tab

Table Compression: Type of compression for table data in the tablespace: No Compress (no compression performed), Compress (basic table compression: Oracle Database attempts to compress data during direct-path INSERT operations when it is productive to do so), Compress for All Operations (OLTP table compression: Oracle Database compresses data during all DML operations on the table), or Compress for Direct Load (same as Compress).

Define Extents: Enables you to specify default values for extent-related parameters (potentially overriding any standard Oracle Database default values).

Initial Extent: Size of the first extent of the tablespace. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Next Extent: Size of the next extent to be allocated to the tablespace. Specify K (kilobytes) or M (megabytes) for the unit associated with the number.

Min Extents: Minimum number of extents allocated when the tablespace is created.

Max Extents: Maximum number of extents allocated when the tablespace is created. Unlimited (if checked) means that there is no maximum (and any specified maximum is ignored).

Pct Increase: Percentage that each extent grows over the previous extent.

6.75.4 DDL tab

Displays the SQL statements that SQL Developer will use to create a new tablespace or to edit an existing tablespace. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

6.76 Create Trigger

The following information applies to a trigger, which is which is a stored PL/SQL block associated with a table, a schema, or the database, or an anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java. The trigger is automatically executed when the specified conditions occur.

Schema: Database schema in which to create the trigger.

Name: Name of the trigger. Must be unique within the database.

Add New Source in Lowercase: If this option is checked, new text is entered in lowercase regardless of the case in which you type it. This option affects only the appearance of the code, because PL/SQL is not case-sensitive in its execution.

Trigger tab

Trigger Type: The type of object on which to create the trigger: TABLE, VIEW, SCHEMA, or DATABASE. (The remaining items depend on the type of trigger.)

Table Owner or View Owner: For a trigger on a table or a view, the name of the owner of the table or the view.

Table Name or View Name : For a trigger on a table or a view, the name of the table or the view.

Before or After: For a trigger on a table, select Before to cause the database to fire the trigger before executing the triggering event, or select After to cause the database to fire the trigger after executing the triggering event.

Statement Level or Row Level: For a trigger on a table, Statement Level fires the trigger once before or after the triggering statement that meets the optional trigger constraint defined in the WHEN condition; Row Level fires the trigger once for each row that is affected by the triggering statement and that meets the optional trigger constraint defined in the WHEN condition.

Insert, Update, Delete: For a trigger on a table or a view, Insert fires the trigger whenever an INSERT statement adds a row to a table or adds an element to a nested table; Update fires fire the trigger whenever an UPDATE statement changes a value in one of the columns specified in Selected Columns (or in any column if no columns are specified); Delete fires the trigger whenever a DELETE statement removes a row from the table or removes an element from a nested table.

Referencing - Old: For a trigger on a table, the correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old value of the current row.

Referencing - New: For a trigger on a table, the correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to new value of the current row.

Available Columns: For a trigger on a table, lists the columns from which you can select for use in an Update trigger definition.

Selected Columns: For a trigger on a table, lists the columns used in an Update trigger definition.

When: For a trigger on a table, an optional trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. This condition must contain correlation names and cannot contain a query.

Schema: For a trigger on a schema, the name of the schema on which to create the trigger.

Available Events: For a trigger on a schema or database, lists events from which you can select for use in the trigger definition.

Selected Events: For a trigger on a schema or database, lists events used in the trigger definition.

DDL Tab

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

Related Topics

Triggers

6.77 Create Type (User-Defined)

This dialog box is displayed when you right-click Types in the Connections navigator and select Create Type to create a user-defined type. After you complete the information in this dialog box and click OK, a SQL Worksheet is displayed in which you must specify the appropriate definition of the type.

Schema: Database schema in which to create the type.

Name: Name of the type. Must be unique within its schema.

Type: Select the type of data type to be created: array type, object type specification, object type specification and type body, or table type.

For more information about creating a user-defined type, see the CREATE TYPE statement in Oracle Database SQL Language Reference.

Related Topics

Types

6.78 Create/Edit User

This dialog box is used for creating a new database user or editing an existing database user. The user properties are grouped under several tabs.

To create or edit a database user, the user associated with your database connection must have the DBA role. You should also be familiar with the main concepts and techniques documented in Oracle Database Administrator’s Guide and in the chapter about managing security for Oracle Database users in Oracle Database Security Guide.

6.78.1 User tab

Specifies general properties for the database user.

User Name: The user name string. For an existing user, this field is read-only; to change the name, you must drop the user and create a new user with the desired name.

New Password: Password string for the new user, or new password for an existing user. You must also type the same password string for Confirm Password.

Password Expired: If this option is selected, the password is marked as expired, and the user must change the password before being permitted to connect to the database.

Account Locked: If this option is selected, the user will not be permitted to connect to the database until a DBA user unlocks the account associated with this user.

Edition Enabled: If this option is selected, the user can create multiple versions of editionable objects in the schema using editions.

Sharded User: If this option is selected, a user profile for a sharded database is created. The sharded user gets duplicated on all the shards in the sharded database, so you can use the same user credentials to connect to any of the shards (or databases) in the sharded database. A connection with a sharded user is indicated with a different icon in the Connections Navigator. Also, for a sharded user, the Enable Shard DDL mode is set by default for any connection.

Default Tablespace and Temporary Tablespace: Tablespaces that the database is associated with. For a sharded database, select the tablespace set created.

6.78.2 Granted Roles tab

Specifies roles to be granted to the user. For each role, you can check Granted to grant the role, Admin to permit the user to grant the role to other users, and Default to use the default settings for Granted and Admin.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None, Default All, Default None); then, you can specify other settings for individual roles.

6.78.3 System Privileges tab

Specifies privileges to be granted to the user. For each privilege, you can check Granted to grant the privilege, and Admin to permit the user to grant the privilege to other users.

For convenience, you can click buttons to affect all settings (Grant All, Revoke All, Admin All, Admin None); then, you can specify other settings for individual privileges.

6.78.4 Quotas tab

Specifies disk usage limits on specified tablespaces for the user. If you check Unlimited, there is no disk usage limit on the tablespace.

6.78.5 SQL tab

Displays the SQL statements that SQL Developer will use to create (after executing a CREATE USER statement) a new user or to edit an existing user. This display is read-only; if you want to make any changes, go back to the relevant tabs and make the changes there.

Related Topics

Users (Other Users)

6.79 Create/Edit User Defined Report

The following information applies to a user-defined report. You can create a simple report based on a SQL query or, if you click Advanced, a report with specialized features such as a child report.

Advanced: If this option is not checked, the box contains basic information and the Property subtab. If this option is checked, the box also contains subtabs for SQL Query, Child Reports, Binds, Frill Down, and PDF.

Database connection dropdown: Select the database connection to use for the report.

Style: The style or type of report:

Table: Tabular presentation (default)
Chart: Bar or pie chart
Gauge: Dial or status meter
Code: Formatted code
Script: Executable script
PL/SQL DBMS Output: Output in PL/SQL DBMS format

You are encouraged to experiment with different style options to see which best meets your needs.

Description: Optional description of the report.

Tool Tip: Popup text that will be displayed when you hover over the report name in the Reports navigator.

Test: Tests the report definition by running it in a separate window. This feature enables you to test the report before creating it.

SQL: Area for entering the SQL query for the report. You can use the folder icon to select a file with the SQL text, and the eraser icon to erase the contents of the SQL box.

SQL Query (advanced)

Area for entering the SQL query for the report. You can use the folder icon to select a file with the SQL text, and the eraser icon to erase the contents of the SQL box.

SQL Variants: Lets you add and remove Minimum Version and Maximum Version information.

Child Reports (advanced)

Lets you add and remove child reports to a master report.

For an example of creating a child report (where you can click a department ID in the top part to display employees in that department in the bottom part), go to the Start Page (Help > Start Page). On the Get Started tab under Featured Online Demonstrations, and click Reporting Features. On the displayed page, find the title containing Adding a Child Report.

Add Child: Lets you specify the name of the child report, after which a node is added for that child report, along with SQL Query, Property, and PDF subtabs for the child report.

Binds (advanced)

Name: Name of the bind variable.

Prompt: String displayed when the user is prompted to enter a value. Example: Table name

Default: Default value if the user does not enter a value at the prompt. To accept the Oracle SQL value, specify NULL_VALUE.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the bind variable name.

Drill Down (advanced)

You can click Add Report to specify one or more reports to be enabled for drill-down display. To remove a report from drill-down, select it and click Remove Report.

Property

Displays options relevant to the Style (report type) that you selected. (Relevant for Table, Chart, and Gauge styles.)

PDF (advanced)

Lets you specify layout and formatting options for when the report is exported as PDF. For example, you can specify a header and footer; security options including password-protection, encryption, and restrictions on printing and copying; and page layout options such as margin sizes and portrait or landscape orientation.

Related Topics

6.80 Create/Edit User Defined Report Folder

The following information applies to a folder for organizing user-defined reports. Each folder can contain reports and other folders (subfolders). For example, you can create a folder named Sales, and then under that folder create folders named Sales by District and Sales by Product.

Name: Name of the folder.

Description: Optional description of the folder.

ToolTip: Optional tooltip text to be displayed when the mouse pointer stays briefly over the folder name in the Reports navigator display.

Related Topics

6.81 Create/Edit View

The view dialog box is used for creating or editing a view or materialized view. You can use the SQL Query tab or a series of panes to specify the query part of the view definition, and you can use one or more other panes (depending on the type of view) for other parts of the definition.

For Edit View, you cannot change the definition of an existing view, but you can define, modify, or drop view constraints.

If you click OK before you are finished creating or editing the view, right-click the view name in the Connections navigator, select Edit, and continue creating or editing the view.

Schema: Database schema in which to create the view.

Name: Name of the view. Must be unique within a schema.

Advanced: If this option is checked, the dialog box changes to include a pane that provides an extended set of features for creating the view.

6.81.1 SQL Query tab or pane

As a tab (if you did not check the Advanced box), it contains the SQL code for the query part of the view definition, using the SELECT and FROM keywords and usually a WHERE clause with whatever syntax is needed to retrieve the desired information.

As a pane (if you checked the Advanced box), it presents options for building specific parts of the query.

For example, the following query, from the Create a View tutorial topic, selects columns from the PATRONS and TRANSACTIONS tables, ordering them first by values in the PATRON_ID column in the PATRONS table and then by values in the TRANSACTION_TYPE column in the TRANSACTIONS table. The result is a listing by patron ID of all patrons who had transactions, and for each listed patron the transaction information listed by transaction type

CREATE VIEW patrons_trans_view AS
  SELECT p.patron_id, p.last_name, p.first_name, 
    t.transaction_type, t.transaction_date
  FROM patrons p, transactions t
  WHERE p.patron_id = t.patron_id
  ORDER BY p.patron_id, t.transaction_type;

SQL Parse Results: If you click Test Syntax, displays any SQL syntax errors, or displays a message indicating no errors if there are no syntax errors.

Revert: Cancels any edits you have made in the SQL Query box, and displays the contents of the box before these edits.

Test Syntax: Checks the statement in the SQL Query box for any SQL syntax errors.

Test Query: Displays a dialog box that runs the SQL query and indicates the result. If there is an error in the query, such as an invalid character or a missing expression, the error is displayed.

6.81.2 View Properties or Materialized View Properties pane

Options for a standard view:

Force on create: If this option is checked, the view is created even if it has errors in its definition. This option is useful if you want to create the view regardless of any errors, and go back and correct the errors later. If this option is not checked, the view is not created is its definition contains any errors.

Query Restriction: If this option is checked, you can enable one of the following options

Read Only: Prevents the view from being used to add, delete, or change data in the underlying table or tables.
Check Option: If this option is checked, it prohibits any changes to the underlying table or tables that would produce rows that are not included in this view.

Options for a materialized view:

Refresh Options:

Method: The method of refresh operation to be performed:

Complete Refresh: Executes the defining query of the materialized view, even if a fast refresh is possible.
Fast Refresh: Uses the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.
Force Refresh: Performs a fast refresh if one is possible; otherwise, performs a complete refresh.
Never: Do not perform refresh operations.

When: The type of refresh operation to be performed:

On Demand: Performs a refresh when one of the DBMS_MVIEW refresh procedures is called.
On Commit: Performs a fast refresh whenever the database commits a transaction that operates on a master table of the materialized view. This may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Specify: Performs refresh operations according to what you specify in the Start on and Next fields.
Never: Does not perform a refresh operation.

Type: Refresh type, which determines the type of materialized view:

Primary Key: Creates a primary key materialized view, which allows materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Row ID: Creates a rowid materialized view, which is useful if the materialized view does not include all primary key columns of the master tables.

Start on: Starting date and time for the first automatic refresh operation. Must be in the future.

Next: Time for the next automatic refresh operation. The interval between the Start on and Next times establishes the interval for subsequent automatic refresh operations. If you do not specify a value, the refresh operation is performed only once at the time specified for Start on.

Constraints: If this option is checked, more rewrite alternatives can be used during the refresh operation, resulting in more efficient refresh execution. The behavior of this option is affected by whether you select Enforced or Trusted.

Enforced: Causes only enforced constraints to be used during the refresh operation.

Trusted: Enables the use of dimension and constraint information that has been declared trustworthy by the database administrator but that has not been validated by the database. If the dimension and constraint information is valid, performance may improve. However, if this information is invalid, then the refresh procedure may corrupt the materialized view even though it returns a success status.

Materialized View Options:

Parallel: If this option is checked, parallel operations will be supported for the materialized view, and you can specify a number for the default degree of parallelism for queries and DML on the materialized view after creation.

Enable Cache: If this option is checked, the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This setting is useful for small lookup tables. If this option is not checked, the blocks are placed at the least recently used end of the LRU list.

Build Type: Specifies when to populate the materialized view. Immediate indicates that the materialized view is to be populated immediately. Deferred indicates that the materialized view is to be populated by the next refresh operation. If you specify Deferred, the first (deferred) refresh must always be a complete refresh; until then, the materialized view has a staleness value of unusable, so it cannot be used for query rewrite.

Enable Query Rewrite: If this option is checked, the materialized view is enabled for query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized views.

Prebuilt Option: If this option is checked, an existing table is registered as a preinitialized materialized view. This option is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view, and the table should reflect the materialization of a subquery. Reduced Precision authorizes the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by subquery. No Reduced Precision requires that the precision of the table or materialized view columns match exactly the precision returned by subquery, or the create operation will fail.

Index Storage Options:

Use Index: If this option is checked, a default index is created and used to speed up incremental (fast) refresh of the materialized view. If this option is not checked, this default index is not created. (For example, you might choose to suppress the index creation now and to create such an index explicitly later.)

Use Tablespace: If this option is checked, you can specify the tablespace in which the materialized view is to be created. If this option is not checked, the materialized view is created in the default tablespace of the schema containing the materialized view.

6.81.3 DDL tab or pane

You can view a SQL CREATE statement that reflects the current definition of the object, or a SQL ALTER statement to modify an existing object to reflect your changes.

To save the SQL statement to a script file, click Save and specify the location and file name.

Related Topics

Views

6.81.4 Quick-Pick Objects pane

Specifies objects that you can use in the SELECT, FROM, and WHERE clauses of the view definition. Identify the tables and views on which this view is based, and the columns in those tables and views that are used in the definition of this view. To see the results of your quick-pick specification, either check Auto-Query or click Query.

Schema: Database schema containing the objects to be selected.

Type Filter - Filter Types: Enables you to limit the display of objects available for selection to certain types of database objects (for example, to show only tables or views).

Name Filter: Enables you to limit the display of objects available for selection according to a character string in the name, with the percent sign (%) as a wildcard character. For example, to limit the display of available tables and views to those whose names start with the string EM, specify the following name filter: EM%

Auto-Query: If this option is enabled, the display of available objects is automatically refreshed when you specify or change the Type Filter or Name Filter value.

Query: Refreshes the display of available objects based on the Type Filter and Name Filter values.

Available: Lists the objects (typically, tables and views in a hierarchical display) from which you can select objects to use in the SELECT, FROM, and WHERE clauses of the view definition.

Selected: Lists the objects (typically, columns) that you can use in the SELECT, FROM, and WHERE clauses of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from selected to available, use the Remove All (<<) icon. To move an object up or down in the selected list, select it in Selected and use the arrow buttons.

For the example in DDL tab or pane, select the DEPTNO and SAL columns from the EMP table.

6.81.5 FROM Clause pane

Specifies the tables and views that you can use in the FROM clause of the view definition.

Type Filter - Filter Types: Indicates whether or not you have limited the types of database objects to be displayed in the Available List (by clicking the Filter Types and selecting any combination of Materialized Views, Tables, and Views).

Naming Filter: Substring for limiting object names to appear as available. For example, to display only objects with names that start with EM, specify EM% (with the percent sign as a wildcard character).

Auto-Query: If this option is enabled, the display of available objects is automatically refreshed when you specify or change the Type Filter or Name Filter value.

Query: Refreshes the display of available objects based on the Type Filter and Name Filter values.

Available: Lists the tables and views that are available to be selected for use in the FROM clause of the view definition.

Selected: Lists the tables and views that you can use in the FROM clause of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from available to selected, use the Add All (<<) icon; to move all objects from selected to available, use the Remove All (<<) icon.

If you attempt to remove any objects that have dependencies in the SQL query, a Confirm Remove box warns you that the dependencies will be removed with the FROM expression; to cancel the remove operation, click No.

Alias: Alias for the table or view.

For the example in DDL tab or pane, select the EMP table.

Join: If you select two tables and click this button, the Edit Join dialog box is displayed.

6.81.6 SELECT Clause pane

Specifies objects that you can use in the SELECT clause of the view definition.

SELECT List: Lists the objects (typically, columns) that you can currently use in the SELECT clause. To add an object, click the Add (+) icon; to delete an object, select it and click the Delete (X) icon; to move an object up or down in the view definition, select it and use the up-arrow and down-arrow buttons.

Note:

After you add an object, to add another object, click the Add (+) icon.

Expression: Column name or an expression. For expressions, you can type them, or you can use the Expression Palette to add object names and function names.

Validate: Checks the validity of the Expression entry.

For the example in DDL tab or pane, select DEPTNO column and the MIN(emp.sal) and MAX(emp.sal) functions.

6.81.7 WHERE Clause pane

Specifies the WHERE clause of the view definition.

WHERE: The text of the WHERE clause, without the WHERE keyword. You can type the text completely; or you can type some of the text and use the Expression Palette to add object names, function names, and operators.

Example (from the Create a View tutorial exercise): p.patron_id = t.patron_id

6.81.8 GROUP BY Clause pane

Specifies a clause to be used to group the selected rows based on the value of columns for each row and return a single row of summary information for each group. The GROUP BY clause groups rows but does not guarantee the order of the result set; to order the groupings, use the ORDER BY clause.

Available: Lists the tables and views, and the columns in each, that are available to be selected for use in the GROUP BY clause of the view definition.

Selected: Lists the tables and views, and the columns in each, that you can use in the GROUP BY clause of the view definition.

To add an object as selected, select it in Available and click the Add (>) icon; to remove an object as selected, select it in Selected and click the Remove (<) icon. To move all objects from available to selected, use the Add All (<<) icon; to move all objects from selected to available, use the Remove All (<<) icon.

6.81.9 HAVING Clause pane

Specifies an expression that must be satisfied for rows to be processed by the GROUP BY clause. For example, HAVING MIN(salary) < 30000 causes the GROUP BY clause to consider only rows where the minimum value of the relevant salary values is less than 30000.

HAVING: You can type the complete expression text, or you can use the Expression Palette to add object names, function names, and operators to the expression text.

6.81.10 ORDER BY Clause pane

Specifies one or more columns or column expressions whose values will be used to sort the results returned by the view. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

ORDER BY List: Lists the objects (typically, columns) that you can currently use in the ORDER BY clause. To add an object, click the Add (+) icon; to delete an object, select it and click the Delete (X) icon; to move an object up or down in the view definition, select it and use the up-arrow and down-arrow buttons.

Note:

After you add an object, to add another object, click the Add (+) icon.

ORDER BY Expression Filter: For each column or column expression, you can type the text completely into the Expression box; or you can type some of the text and use the Expression Palette to add object names, function names, and operators.

Validate: Tests the validity of the syntax for the expression.

Order: ASC for ascending (expression values sorted in ascending order); DESC for descending (expression values sorted in descending order).

Nulls Ordering: NULLS FIRST to have null expression values appear before non-null values; NULLS LAST to have null expression values appear after non-null values. ("Before" and "after" positions are determined by the Order value.)

6.82 Create Window

This interface is used for creating a new Oracle Scheduler window.

(To create a window, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_WINDOW procedure.)

Name: Name of the window. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the window. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the window is created.)

Resource Plan: The resource plan that automatically activates when the window opens. When the window closes, the system switches to the appropriate resource plan, which is usually the plan that was in effect before the window opened, but can also be the plan of a different window. Only one resource plan can be associated with a window. It null, the resource plan in effect when the window opens stays in effect for the duration of the window. If an empty string, the resource manager is disabled for the duration of the window. If the window is open and the resource plan is dropped, then the resource allocation for the duration of the window is not affected.

Duration: The length of time that the window stays open. Can range from one minute to 99 days.

Priority: Relevant when two windows overlap. Because only one window can be in effect at one time, the window priority determines which window opens. The two possible values for this attribute are HIGH and LOW. A high priority window has precedence over a low priority window, therefore, the low priority window does not open if it overlaps a high priority window.

Description: Optional descriptive text.

When to Open: Repeating or Schedule:

For Repeating, specify the Repeat Interval and optionally the Start Date and End Date.
For Schedule, select the name of the Schedule to be used.

Related Topics

6.83 Create Window Group

This interface is used for creating a new Oracle Scheduler window group.

(To create a window group, SQL Developer internally uses the DBMS_SCHEDULER.CREATE_GROUP procedure.)

Name: Name of the window group. It can optionally be prefixed with a schema name. Cannot be null. It is converted to uppercase unless enclosed in double-quotes.

Enabled: Enables the window group. (Causes the DBMS_SCHEDULER.ENABLE procedure to be called after the window group is created.)

Comments: Optional comment text.

Members

Available Members: Lists all Scheduler windows.

Selected Members: List windows to be added to the window group. Use the arrow icons to move selected windows or all windows from one list to the other.

Related Topics

6.84 Create XML Document from XML Schema

Use to specify the filename and the directory in which the XML file from an XML Schema is created, and options for the creation.

File Location

XML File: Name of the XML file to be created.

Directory: Directory for the file. You can click Browse to select a directory.

Use Registered Schemas: Select to use the schemas registered with SQL Developer. To view all the currently registered XML schemas, add new schemas to support additional namespaces and elements, remove existing schemas, and unload schemas from memory, select Tools > Preferences, XML Schemas.

Use File System Schema: Enter or browse to select the directory for the XML schema.

Options

Use to specify the default XML namespace and other options in creating the instance document.

Target Namespace: Select the XML namespace (xmlns attribute). Available namespaces are either schemas already registered with SQL Developer or specified in the file system from the previous wizard page.

Root Element: Select the root element. SQL Developer will automatically provide the root element from the schema selected as the target namespace.

Depth: Number of levels to display in the XML tree structure.

Encoding: Select the encoding that the XML parser will use to understand foreign characters.

Generate Only Required Elements: Select to generate XML elements with a required attribute in the instance document.

6.85 Create XML Schema

This dialog box enables you to create an XML schema object that can be associated with XML document instances.

Name: Name of the XML schema object.

After you click OK, an editing pane is displayed for specifying the URL of the XML schema.

6.86 Custom Filters

This dialog box is displayed if you right-click and select Customize Filters in the History tab for a .sql file or a SQL Trace (.trc) file. You can modify an existing filter or create a new filter.

Filter List: Names of the available filters. To edit an existing filter, select its name; the details for that filter are displayed in the dialog box. To remove an existing filter, select its name and click Remove.

To create a new filter, click Add and specify the filter name.

To restore the filters to those at SQL Developer installation, click Restore Defaults. This deleted any filters that have been added since installation.

Simple Expression: Create the filter by selecting a column and operator and by specifying a value. To select a value from a list, click Insert.

Complex Expression: Create the filter by entering a complex expression.

6.87 Customize Toolbars

This dialog box is displayed if you click View, Show Toolbars, Customize Main Toolbar, then Customize.

To add a command icon to the main toolbar, drag its icon from the dialog box to the desired location on the main toolbar.

To remove a command icon from the main toolbar, drag its icon from the main toolbar to the dialog box.

New Toolbar: Lets you add a "placeholder" in the main toolbar, into which you can drag icons.

Reset Toolbars: Undoes any main toolbar customizations, restoring it to the content when SQL Developer was originally installed. (To reset the entire user interface to its original form, see Restoring the Original "Look and Feel".)

6.88 Data Pump Export

This wizard is displayed if you expand a connection in the DBA navigator, right-click Export Jobs, and select Data Pump Export Wizard.

To use the Data Pump Export wizard, you must understand the concepts and techniques for Oracle Data Pump, as documented in Oracle Database Utilities.

Related Topics

Using DBA Features in SQL Developer

6.88.1 Source page

Connection: Database connection to use to export the specified information for the specified types of objects.

Data or DDL: Specify whether to export only the data (applies to tables and views), only the object definitions (the "DDL), or both the data and the object definitions.

Types to be Exported: Specify whether to export the database or just one or more tablespaces, schemas, or tables. (Your selection will affect the display of some remaining wizard pages.)

6.88.2 Tablespaces page

If you specified Tablespaces for Types to be Exported, select one or more tablespaces to be exported, and use the arrow icons to move them to the Selected Source Tablespaces box.

6.88.3 Schemas page

If you specified Schemas for Types to be Exported, select one or more schemas to be exported, and use the arrow icons to move them to the Selected Source Schemas box.

6.88.4 Tables page

If you specified Tables for Types to be Exported, select one or more tables to be exported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables tab: To select one or more tables, see Specify Objects under Shared Wizard pages.

Include/Exclude Filter tab: Optionally, filter the objects to be included or excluded, using the same interface as for the Filter page.

6.88.5 Filter page

Lets you specify data and metadata filtering to limit the type of information that is exported. You can specify multiple filters by adding rows.

If you specify any Include or Exclude rows, you must put any object names in single quotes. Example: Filter Type = TABLE, and Expression = not in ('EMPLOYEES')

6.88.6 Table Data page

See Data under Shared Wizard Pages.

6.88.7 Options page

Lets you specify Data Pump Export options for the job.

6.88.8 Output Files page

Lets you specify one or more output (dump) files and certain options such as:

Delete Existing Dump Files — Deletes existing dump files before creating new ones.
Append Timestamp to Dump, Log and Job names — Includes the timestamp in dump file names, logs and in job names.
Compression — Compresses data before pushing to the dump file. For this, you need Oracle Database Release 11g or later versions and the Advanced Compression License option enabled.
Copy files to Oracle Object Storage Service (OSS) — Loads data to a URI (Universal Resource Identifier) using Oracle Object Storage Service file transfer and imports to tables in Cloud Wallet connections that have the DBMS_CLOUD package. When you select this option, the next page displayed in the wizard will be OSS Transfer.
- Oracle Directory path override— If the Oracle Directory path is relative and the full path is not provided, specify a full path that starts with ‘/’ for the Oracle Directory.
- Data Pump Export Job Name— Name for the export job. By default, this field is filled in with a job name.
Proceed to summary — Skips the next page and proceeds to the Summary page in the wizard.

6.88.9 OSS Transfer page

The Oracle Data Pump Export wizard supports the export and transfer of existing schemas and data to Oracle Cloud Object Storage Service and import to Oracle Cloud databases that have the DBMS_CLOUD package installed.

Note:

To export from SQL Developer release 18.3 to the Autonomous Transaction Processing cloud service, you need to specify the VERSION parameter as 12.2.

To display the OSS Transfer page, select the option Copy files to Oracle Object Storage Service (OSS) in the Output Files (previous) page in the wizard.

The prerequisites for OSS transfer are:

A local database is running on the same system as the generated export files. The SQL Developer operating system user requires read access to the directory where the dumps are generated.
The Cloud connection has the DBMS_CLOUD package installed and a predefined credential is available.

Connection for import: Choose a Cloud Wallet type connection from the drop-down list.

Credential for DBMS_Cloud: Choose a credential for import from the drop-down list. The credential authenticates Data Pump to the Cloud Object Storage service you are using for your source files.

Target URL (Bucket): Choose the target URL that points to the location of the file in the object store. File names are encoded and appended.

Proceed with Full Import after Transfer: If you enable this option, a full import with default options selected is executed after the export and file transfer. The full import will have a log name with a sysdate added.

OSS Only (Exp dmp exists): This option is visible only when the file transfer has failed subsequent to a successful export. In this instance, the wizard will reopen to the Start page. Click Next and you are taken directly to the OSS Transfer page. By default, the OSS Only option is selected, indicating that the export does not need to be executed again. To run the export again, remove the selection.

After the wizard is finished, you can cancel the job at the end of export or transfer, but you cannot cancel during the export, transfer or import. To check the status of each operation, look at the log files. To view the log file, in the DBA pane, right-click Export Jobs and select Open.

6.88.10 Job Schedule page

Lets you specify scheduling options for the Data Pump Export job. The options include whether to start the job immediately or later, and any repeat options.

6.88.11 Summary page

See Summary under Shared Wizard Pages.

On the Summary page, you can click the PL/SQL tab to see the PL/SQL procedure that will perform the job.

6.89 Data Pump Import

This wizard is displayed if you expand a connection in the DBA navigator, right-click Import Jobs, and select Data Pump Import Wizard.

To use the Data Pump Import wizard, you must understand the concepts and techniques for Oracle Data Pump.

Related Topics

Using DBA Features in SQL Developer

6.89.1 Type page

Connection: Database connection to use to import the specified information for the specified types of objects.

Job Name: Name to be given to this import job.

Data or DDL: Specify whether to import only the data (applies to tables and views), only the object definitions (the "DDL), or both the data and the object definitions.

Types to be Imported: Specify whether to import the database from the dump file or files, or just one or more tablespaces, schemas, or tables. (Your selection will affect the display of some remaining wizard pages.)

Choose Import Files: Specify one or more dump files (that were created using Data Pump Export) from which to import the objects. To add files, click Add Row. You can select Directory to import from an Oracle Database directory.

You can import files to Autonomous Data Warehouse Cloud without using Oracle directories. You can open, read, and close a dump file from a URI (Universal Resource Identifier) by selecting a Credential from the drop-down list. For CREDENTIAL:NULL, the default_credential parameter needs to be defined.

6.89.2 Tablespaces page

If you specified Tablespaces for Types to be Imported, select one or more tablespaces to be imported, and use the arrow icons to move them to the Selected Source Tablespaces box.

6.89.3 Schemas page

If you specified Schemas for Types to be Imported, select one or more schemas to be imported, and use the arrow icons to move them to the Selected Source Schemas box.

6.89.4 Tables page

If you specified Tables for Types to be Imported, select one or more tables to be imported, and use the arrow icons to move them to the Selected Source Schemas box.

Tables tab: To select one or more tables, see Specify Objects under Shared Wizard Pages.

Include/Exclude Filter tab: Optionally, filter the objects to be included or excluded, using the same interface as for the Filter page.

6.89.5 Filter page

Lets you specify data and metadata filtering to limit the type of information that is imported. You can specify multiple filters by adding rows.

6.89.6 Table Data page

See Data under Shared Wizard Pages.

6.89.7 Remapping page

Lets you specify options for the relevant Data Pump Import "remapping" parameters (REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE).

6.89.8 Options page

Lets you specify Data Pump Import options for the job. The specific options depend on selections made on preceding wizard pages. Various options are implemented using DBMS_DATAPUMP subprograms, documented in Oracle Database PL/SQL Packages and Types Reference.

Note that the Regenerate Object Ids option calls the DBMS_DATAPUMP.the METADATA_TRANSFORM procedure and specifies OID as 0 (zero), which inhibits the assignment of the exported OID during type or table creation; instead, a new OID will be assigned.

6.89.9 Job Schedule page

Lets you specify scheduling options for the Data Pump Import job. The options include whether to start the job immediately or later, and any repeat options.

6.89.10 Summary page

See Summary under Shared Wizard Pages.

On the Summary page, you can click the PL/SQL tab to see the PL/SQL procedure that will perform the job. For information about the Data Pump PL/SQL API, see Oracle Database Utilities.

6.90 Database Copy

This wizard is displayed if you click Tools, then Database Copy. You can copy objects, schemas, or a tablespace from a source connection to a destination connection. The specific options depend on what you are copying.

Some pages include Proceed to Summary, which lets you accept the default values for remaining pages and go directly to the Summary page by clicking Next.

6.90.1 Source/Destination page

Source Connection: Database connection from which to copy the objects, schemas, or tablespace.

Destination Connection: Database connection into which to copy the objects, schemas, or tablespace.

Note: For Schemas Copy or Tablespace Copy, the Source Connection user and the Destination Connection user must have DBA privileges. (DBA privileges are not required for Objects Copy.)

Copy Options: Specify whether to copy database objects, schemas, or tablespaces.

Objects Copy: Copies types of database objects and specific objects of those types.

Schemas Copy: Copies one or more schemas. If you select multiple schemas from the source connection, you can maintain the individual schemas in the destination connection or consolidate them all into one schema in the destination connection.

Tablespace Copy: Copies the objects from a source tablespace to a destination tablespace.

Copy DDL: Copies the object definitions. For destination objects with the same names as source objects of the same type (for example, if both contain a table named EMPLOYEES), specify whether to not perform the copy (that is, do not replace the destination objects) or to perform the copy (that is, replace the existing destination objects with the source objects).

Copy Data: Copies the data for any tables and views that are copied. If you do not select this option, any copied tables or views are empty in the destination connection.

Truncate Destination Data Before Copying: If a table or view of the same name already exists in the destination connection (for example, if an EMPLOYEES table exists in the source and destination), this option deletes any existing data in the destination object before copying the data from the source connection. If this option is not selected, the copied data is appended to the existing data in the destination object.

6.90.2 Object Types page

See Types under Shared Wizard Pages.

6.90.3 Select Schemas page

If you specified Schemas Copy, select one or more schemas from the Available Source Schemas list and click the right-arrow to move them to Selected Source Schemas.

6.90.4 Select Tablespace page

If you specified Tablespace Copy, select the Source Tablespace and Destination Tablespace. Objects in the source tablespace are copied into the destination tablespace.

6.90.5 Specify Objects page

See Specify Objects under Shared Wizard Pages.

6.90.6 Specify Data page

See Data under Shared Wizard Pages.

6.90.7 Summary page

See Summary under Shared Wizard Pages.

Related Topics

6.91 Database Differences

This wizard is displayed if you click Tools, then Database Diff. You can find differences between objects of the same type and name (for example, tables named CUSTOMERS) accessible from two different connections, and optionally generate DDL to update the objects in the destination schema to reflect the differences in the source schema.

Use the Source/Destination page to specify the source and destination database connections. Database objects in the schemas associated with these connections will be compared. The schemas for the source and destination connections can be in the same database or different databases.

Some pages include Proceed to Summary, which lets you accept the default values for remaining pages and go directly to the Summary page by clicking Next.

6.91.1 Source/Destination page

Source Connection: Database connection that has access to the source objects to be compared.

Destination Connection: Database connection that has access to the destination objects to be compared.

DDL Generation Options: Check the options or features to be included if you later generate a script to update the objects in the destination schema to reflect differences in the source schema: comments, schema name (prefixed to schema objects), constraints, password values, constraint indexes, referential constraints. Options that are not checked are ignored or not performed during the script generation.

Schema: This option, if you also select Consolidate Schema under DDL Comparison Options, causes object names to be prefixed with the target schema name in the generated script (for example, CREATE TABLE "HR"."SKILLS" instead of CREATE TABLE "SKILLS"). If you select Maintain Schema under DDL Comparison Options, this Schema option is ignored and object names are prefixed with the source schema name in the generated script.

DDL Comparison Options: Check various options, including differences to be ignored, when objects in the source and destination schemas are compared.

Note:

The DDL Generation Options apply to how the DDL will be generated, whereas the DDL Comparison Options apply to how the comparison is made between the two objects.

Consolidate Schema and Maintain Schema enable you either to compare the source objects with objects owned by the target connection user (schema) or with any object accessible by the target connection. The resulting behavior is largely based on which option you select and which objects your connection has access to.

Consolidate: Compares the source objects with those owned by the target schema; in other words the selected objects are "consolidated" into the target schema.

If the target connection does not own an object by that name (that is, target-schema.object-type.object-name), the object will not be found and a comparison cannot be done. If the target connection does own object target-schema.object-type.object-name, source-schema.objectname will compared with target-schema.object-type.object-name.
Maintain: Maintains the schema on the source objects and applies it in the target object processing.

If the target connection does not have access to those schema objects, they will not be found and a comparison cannot be done. If the target connection does has access to those schema objects, source-schema.object-type.object-name will be compared with target-schema.object-type.object-name.

Match Constraints By: Controls whether constraints are matched for comparison by the names (Name) or definitions (Definition).

Ignore Segment Attributes: Causes differences in segment specification to be ignored.

Ignore Table Column Position: Causes differences in the positions of table columns to be ignored when objects are compared. For example, if this option is enabled (checked), two tables would not be considered different in the only difference is that Column1 and Column2 appear first and second in one table but second and first in the other table.

Ignore Storage: Causes differences in storage specification to be ignored.

Ignore High Values: Causes differences in partition high values for tables to be ignored.

Ignore Tablespace: Causes differences in the tablespace specified for the object to be ignored.

Logical Compare: Causes meaningless SQL formatting differences in definitions of objects (such packages, package bodies, procedures, and functions) to be ignored.

Ignore Partitioning: Causes differences in partitioning specifications for the object to be ignored

Report Constraint Name Difference: If Match constraints by Definition is selected, causes the constraint name to be displayed when differences are reported.

6.91.2 Types to Diff page

See Types under Shared Wizard Pages.

6.91.3 Specify Objects page

See Specify Objects under Shared Wizard Pages.

6.91.4 Summary page

See Summary under Shared Wizard Pages.

The results are displayed in a Diff Report window, where you can see the DDL statements to update the objects in the destination schema to reflect differences from the source schema. To create a file containing these DDL statements, click the Generate Script (SQL) icon in that window. In the Generate SQL dialog box you can specify the Save As destination or type (Single File, Separate Files, Type Files, Separate Directories, Worksheet, Clipboard), the location, and the encoding.

To display only objects with differences that have the same name in both connections, select Show Equal Objects. To display all objects that have differences or that exist in one connection but not the other, select Show New Objects.

Related Topics

6.92 Database Export (Unload Database Objects and Data)

Use this wizard to export database objects and optionally data. For a selected database connection, you can export some or all objects of one or more types of database objects to output files, a SQL Worksheet, or the clipboard. The output may contain SQL data definition language (DDL) statements to create these objects. It may also contain SQL statements to insert data into these objects and other formatted files.

Note:

For some needs you can use special SQL Developer worksheet "hints" instead of this wizard to generate output in desired formats, as explained in SQL Worksheet "Hints" for Formatting Output.

The number of pages and the options available depend on the potential scope of the export operation. For example, if you clicked Tools, then Database Export, all pages are available; however, if you right-clicked a table name and selected Export, fewer pages are required.

In several pages, if you select Proceed to summary, clicking Next takes you to the Export Summary page.

See Also:

Cart Versus Database Export Wizard to consider whether you may want to use the Cart instead of the Database Export wizard

6.92.1 Source/Destination page

Contains up to four main areas for specifying the database connection and the DDL, data, and target options. If you specify any options, they override the defaults for Database: Utilities: Export user preferences.

Connection: Select the database connection with the objects to be exported.

Other options on this page: See the Database: Utilities: Export preferences for explanations of individual options.

Export DDL: If this option is enabled, DDL statements are included in the export operation. Select the options to apply to the DDL that is generated.

Export Data: If this option is enabled, data is included in the operation. Select the options to apply to the data that is unloaded.

Note:

An Export Format Error is displayed if you attempt to export both DDL and Data when Format is loader.

Format: Select the desired output format for the data to be unloaded. Depending on the selected format, other options may appear. For example, for xls (Microsoft Excel file), you can specify worksheet names for the data and the SELECT statement.

For CLOB data, exporting is supported only if the format is loader (SQL*Loader) or pdf (PDF). Some export types export only a subset of the string followed by an ellipsis (...).

To paste unloaded data into a Microsoft Excel file, specify Export Data but not Export DDL, select text for Format, and select Clipboard for Save As; and after completing the unload, paste from the clipboard into Excel.

For exporting date fields in date format instead of timestamp format: If you encounter problems exporting dates, check that the following line is in the sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file to have dates unloaded in date format: AddVMOption -Doracle.jdbc.mapDateToTimestamp=false

For exporting large tables to Microsoft Excel files:

If you encounter problems exporting to an .xls file, try exporting instead to XLSX (.xlsx file) format. However, if you must export to an .xls file, try adding the following line to the sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file to increase heap size and then restarting SQL Developer: AddVMOption -Xmx1024M
If the number of table rows exceeds 65,536, SQL Developer writes the rows to multiple worksheets within the spreadsheet file.

Save As: Specifies how or where target statements and data are to be saved:

Single File: A single file contains both DDL and data. When you are unloading DDL, only Insert format can be specified for data.
Separate Files: Each object is saved to a separate file in the specified directory.
Type Files: Objects of the same type are saved to a single file in the specified directory.
Separate Directories: A directory for each object type being exported is created. Files are created in the appropriate directory.
Worksheet: Statements are sent to a SQL Worksheet window.
Clipboard: Statements are copied to the clipboard.

6.92.2 Types to Export page

Specify object types to be exported and options for the export operation.

Object Types: Check the types of objects that you want to export. You can click Toggle All to check and uncheck all individual types. You must select at least one object type.

6.92.3 Specify Objects page

See Specify Objects under Shared Wizard Pages.

6.92.4 Specify Data page

See Data under Shared Wizard Pages.

6.92.5 Export Summary page

See Summary under Shared Wizard Pages.

If a script is created and if Save As was specified as Worksheet, the file is displayed in a SQL Worksheet window, where you can run it as a script and perform other operations.

6.93 DDL Panel for Creating or Editing an Object

You can review and save the SQL statement that SQL Developer will use to create or edit the object, to reflect any changes you have made to the object's properties. If you want to make any changes, go back to the relevant panels and make the changes there.

To save the SQL statement to a script file, click Save and specify the location and file name.

6.94 Debugger - Attach to JPDA

This dialog box is displayed when you right-click a database connection name and select Remote Debug. Use this dialog box if you are using the Java Platform Debugger Architecture (JPDA) and you would like the debugger to listen so that a debuggee can attach to the debugger.

Host: Name or IP address of the remote host on which SQL Developer should listen for the database to connect.

Port: Listening port number on the remote host. You can choose any valid port number that is not in use by another process.

Timeout: The number of seconds that SQL Developer will wait for the remote database to make a debugging connection.

Don't Show Dialog Box Before Connecting: If this option is checked, this dialog box will not be displayed before future connections for remote debugging.

Related Topics

Remote Debugging

6.95 Deploy or Import Application

Use this wizard to deploy or import an Application Express application into a specified target schema.

Deploy to Connection or Specify File to Import

Choose Connection to Deploy Application: For a deploy operation, specify the database connection for the target schema into which to deploy the application.

Specify File to Import: For an import operation, specify the location and name of the SQL file containing the exported application (usually the output of a previous "export application" operation).

Choose Import Options

Specifies options for the application to be deployed or imported.

Workspace: Name of the Application Express workspace.

Parse As Schema: Schema against which all of the application's SQL and PL/SQL will be parsed.

Application Name: Name of the application.

Application Alias: Alias for the application. It is recommended that you never hard code the application ID into your application, but instead use the application alias or a built-in substitution string (such as APP_ID and APP_ALIAS).

Build Status: RUN_ONLY or RUN_AND_BUILD

Application ID: Specify whether to have an application ID assigned automatically, to use an existing listed ID, or to specify a new ID. Use these options to avoid application ID conflicts, such as when you need to have two versions of the same application in the same instance. For example, you might be migrating an application to a production instance and still need to maintain the development version.

ID Currently Used by and Overwrite: If the specified Application ID is currently used by another application, you can enable Overwrite to have the application ID instead associated with the application being deployed or imported.

Summary

Displays the selected options for the application to be deployed or imported. To make any changes, click Back. To perform the operation, click Finish.

Related Topics

Applications (Application Express 3.0.1 and Later)

6.96 Deploy Objects to Cloud

This dialog box is displayed when you click the Deploy Cloud icon in the Cart window.

You can deploy objects and data files to the Cloud, except as noted in Unsupported Cart Object for Cloud Deployment.

Starting with the SQL Developer 18.3 release, you can deploy tables, local and cloud storage files from the Cart to Cloud Wallet connections that have the PL/SQL package, DBMS_CLOUD, installed. This feature is available only with the Autonomous Data Warehouse service.

For more information about the DBMS_CLOUD package, see Package DBMS_CLOUD.

Related Topics

Using the Cart

6.96.1 Cloud

Connection: Name of a previously created Cloud connection or a cloud wallet connection. (When you create a Cloud connection, you can use it immediately within the SQL Developer graphical interface; however, to use that connection with the SQL Developer command line interface, you must first exit the SQL Developer session in which you created the connection.)

Schema: Applicable for cloud wallet connections that have the DBMS_CLOUD package. Specify the schema where the object is to be deployed.

Title: A short descriptive string identifying this deployment. The name will be appended with the date and time stamp.

Server: Server for the Cloud service (for example, a WebLogic Server system).

Port: Port to be used for the deployment. For example: 2222

6.96.2 Authorization

SFTP User: Secure FTP user name that you received in email when you signed up for the Cloud service.

SFTP Password: Password for the SFTP user.

6.96.3 Deploy File

File: .zip file to contain the files to be used by SQL Developer for the deployment.

6.96.4 Deploy DDL

Specify whether not to replace or to replace existing destination objects that have the same schema name and object name combination.

6.96.5 Deploy Data

Truncate destination data: If you specified not to replace existing destination objects, then for each existing destination table, this controls whether any existing data is truncated (deleted) before the source table data is appended.

Disable constraints before moving data: Controls whether to disable referential integrity constraints before deploying data to destination tables. (You may want to disable constraints to expedite the deployment, and then reapply the constraints after the deployment.)

Transfer Cloud Storage: This field is applicable only for Cloud Wallet connections. Deploying tables and local files to Cloud Wallet connections requires temporary cloud storage. Provide the cloud storage location and credential. For information on how to create a credential, see CREATE_CREDENTIAL Procedure.

Format for Load Data from Table

Note:

The options in the Format for Load Data section applies only for tables dropped into the Cart. For data files, the format is defined in the Data Import Wizard when the file is added to the cart.

Delimiter: Identifies the character used to separate the data into columns. The delimiter character is not included in the data loaded. If the preview page does not show the data separated into columns, the correct delimiter is probably not specified. Examine the data in the preview area to determine the correct delimiter.

Line Terminator: Identifies the terminator for each line. The line terminator is not included in the data loaded. If the preview page shows the data in one single row, the correct terminator is not specified.

Left Enclosure and Right Enclosure: Enclosures are used for character data and are optional. Enclosures are not included in the data loaded.

Related Topics

6.96.6 Deployment Console

Starting with the SQL Developer 18.3 release, you can monitor the deployment of objects from the Cart to Cloud Wallet database connections that have the DBMS_CLOUD package installed, using the deployment console. The deployment console is started when you execute Deploy Objects to Cloud from the cart.

The console provides an overall view of the deployment. The details of each operation in the deployment along with the execution status is displayed. If one operation displays an error, the overall deployment also shows an error.

Figure 6-1 Deployment Console

Description of "Figure 6-1 Deployment Console"

You can also view cloud deployments for a connection in the Connections navigator pane. Click the Cloud Deployments node to view a summary of all deployments.

Figure 6-2 Cloud Deployments Node in Connections Navigator

Description of "Figure 6-2 Cloud Deployments Node in Connections Navigator"

Select a specific operation in a deployment to see a detailed view. This includes the Log and a Bad File Preview, in case of errors when loading data.

Figure 6-3 Detailed View of an Operation in a Deployment

Description of "Figure 6-3 Detailed View of an Operation in a Deployment"

6.96.7 Unsupported Cart Object for Cloud Deployment

An error box is displayed if any of the following conditions apply on an attempt to deploy objects from the Cart to the Cloud:

The objects include any of the following object types: database link, directory, external table, materialized view, materialized view log, or synonym.
The objects include any with a data type that is not supported for Cloud deployment (such as SDO_GEOMETRY).
The objects are from a non-Oracle database connection.

6.96.8 Other Errors (Invalid or Missing Entries)

An error box is displayed if any entries are invalid or any required entries are omitted. Check the error text and the dialog box entries, ensure that all required information is entered and all entries are valid, and click Apply again.

6.97 Describe Object Window

This window is displayed when you select a database object name in the SQL Worksheet, right-click, and select Describe. The information is read-only, and is displayed using tabs that are appropriate for the type of object.

For example, if the display is for a table, the information displayed is similar to that in the Create/Edit Table (with advanced options) dialog box.

6.98 Diff Objects

This dialog box is displayed if you click the Diff Objects icon in the Cart window.

Open Configuration icon: Opens a previously saved XML configuration file, to use its settings as defaults for this use of the dialog box.

Save Configuration icon: Saves the current settings in the dialog box to an XML file, which you can later open to use for Cart operations of this type.

Destination: Destination with objects to be compared with the source objects: Cart for another currently open cart tab, or Connection for a database connection that has access to the destination objects to be compared.

DDL Generation Options: Check the options or features to be included if you later generate a script to update the objects in the destination schema to reflect differences in the source schema: comments, schema name (prefixed to schema objects), constraints, password values, constraint indexes, referential constraints. Options that are not checked are ignored or not performed during the script generation.

Schema: This option, if you also select Schema: Consolidate under DDL Comparison Options, causes object names to be prefixed with the target schema name in the generated script (for example, CREATE TABLE "HR"."SKILLS" instead of CREATE TABLE "SKILLS"). If you select Schema: Maintain under DDL Comparison Options, this Schema option is ignored and object names are prefixed with the source schema name in the generated script.

DDL Comparison Options: Check various options, including differences to be ignored, when objects in the source and destination schemas are compared.

Note:

The DDL Generation Options apply to how the DDL will be generated, whereas the DDL Comparison Options apply to how the comparison is made between the two objects.

Schema: Consolidate and Schema: Maintain enable you either to compare the source objects with objects owned by the target connection user (schema) or with any object accessible by the target connection. The resulting behavior is largely based on which option you select and which objects your connection has access to.

Consolidate: Compares the source objects with those owned by the target schema; in other words the selected objects are "consolidated" into the target schema.

If the target connection does not own an object by that name (that is, target-schema.object-type.object-name), the object will not be found and a comparison cannot be done. If the target connection does own object target-schema.object-type.object-name, source-schema.objectname will compared with target-schema.object-type.object-name.
Maintain: Maintains the schema on the source objects and applies it in the target object processing.

If the target connection does not have access to those schema objects, they will not be found and a comparison cannot be done. If the target connection does has access to those schema objects, source-schema.object-type.object-name will be compared with target-schema.object-type.object-name.