She asks: which sql function enables you to eliminate those extra spaces for consistency?
Related Topics Show
1.1 About SQL DeveloperOracle SQL Developer is a graphical version of SQL*Plus that gives database developers a convenient way to perform basic tasks. You can browse, create, edit, and delete (drop); run SQL statements and scripts; edit and debug PL/SQL code; manipulate and export (unload) data; and view and create reports. You can connect to any target Oracle Database schema using standard Oracle Database authentication. Once connected, you can perform operations on objects in the database. You can connect to schemas for MySQL and selected third-party (non-Oracle) databases, such as Microsoft SQL Server, Sybase Adaptive Server, and IBM DB2, and view metadata and data in these databases; and you can migrate these databases to Oracle Database. 1.2 Installing and Getting Started with SQL DeveloperTo install and start SQL Developer, you simply download a ZIP file and unzip it into a desired parent directory or folder, and then type a command or double-click a file name. You should read the Oracle SQL Developer Installation Guide before you perform the installation. After you have read the installation guide, the basic steps are:
1.3 SQL Developer User InterfaceThe SQL Developer window generally uses the left side for navigation to find and select objects, and the right side to display information about selected objects. Figure 1-1 shows the main window. Note: This text explains the default interface. However, you can customize many aspects of the appearance and behavior of SQL Developer by setting preferences. If you ever need to restore the default interface, see Restoring the Original "Look and Feel". The menus at the top contain standard entries, plus entries for features specific to SQL Developer as shown in the following figure. You can use shortcut keys to access menus and menu items: for example Alt+F for the File menu and Alt+E for the Edit menu; or Alt+H, then Alt+S for Help, then Search. You can also display the File menu by pressing the F10 key (except in the SQL Worksheet, where F10 is the shortcut for Explain Plan). To close a window that has focus (such as the SQL Developer main window, a wizard or dialog box, or the Help Center) and any of its dependent windows, you can press Alt+F4. The main toolbar (under the menus) contains Icons to perform various actions, which by default include the following:
However, you can add and remove main toolbar icons by clicking View, Show Toolbars, Customize Main Toolbar, then choosing desired options. To undo any main toolbar customization, select Reset Toolbars. The left side of the SQL Developer window has panes for the Connections and Reports navigators (any other navigators that you might open), icons for performing actions, and a hierarchical tree display for the currently selected navigator, as shown in the following figure. The Connections navigator lists database connections that have been created. To create a new database connection, import an XML file with connection definitions, or export or edit current connections, right-click the Connections node and select the appropriate menu item. The Files navigator (marked by a folder icon; not shown in the preceding figure) displays your local file system using a standard hierarchy of folders and files. You can double-click or drag and drop files to open them, and you can edit and save the files. For example, if you open a .sql file, it is displayed in a SQL Worksheet window. The Files navigator is especially useful if you are using versioning with SQL Developer. The Reports navigator (not shown in the preceding figure) lists informative reports provided by SQL Developer, such as a list of tables without primary keys for each database connection, as well as any user-defined reports. Icons above the Connections node and metadata tree perform the following actions on the currently selected object:
The metadata tree in the Connections pane displays all the objects (categorized by object type) accessible to the defined connections. To select an object, expand the appropriate tree node or nodes, then click the object. The right side of the SQL Developer window has tabs and panes for objects that you select or open, as shown in the following figure, which displays information about a table named EMPLOYEES. (If you hold the mouse pointer over the tab label -- EMPLOYEES in this figure -- a tooltip displays the object's owner and the database connection.) For objects other than subprograms, icons provide the following options:
To switch among objects, click the desired tabs; to close a tab, click the X in the tab. If you make changes to an object and click the X, you are asked if you want to save the changes. For tables and views, this information is grouped under tabs, which are labeled near the top. For example, for tables the tabs are Columns, Data (for seeing and modifying the data itself), Indexes, Constraints, and so on; and you can click a column heading under a tab to sort the grid rows by the values in that column. For most objects, the tabs include SQL, which displays the SQL statement for creating the object. You can export (unload) data from a detail pane or from the results of a SQL Worksheet operation or a report by using the context menu and selecting Export. The Messages - Log area is used for feedback information as appropriate (for example, results of an action, or error or warning messages). If this area is not already visible, you can display it by clicking View and then Log. The Compiler - Log area is used for any messages displayed as a result of a Compile or Compile for Debug operation. The Utilities - Log area is used for displaying the operations that are run for database import, export, or copy. 1.3.1 Menus for SQL DeveloperThis topic explains menu items that are specific to SQL Developer. File menu Data Modeler: Lets you open, close, import, export, and print Data Modeler designs, and use Data Modeler reports (see Data Modeler in SQL Developer). Edit menu Extended Paste: Displays the Paste dialog box, in which you select a clipboard item (from potentially many) to be pasted into the current location. Duplicate Selection: When you have selected text while editing a function or procedure, creates a copy of the selected text at the current location. Wrap Selection: When you have selected text while editing a function or procedure, wraps the selected text. View menu Contains options that affect what is displayed in the SQL Developer interface. Breakpoints: Displays the Breakpoints pane, which shows breakpoints, both system-defined and user-defined (see Running and Debugging Functions and Procedures). Cart: Displays the Cart window (see Using the Cart). Change Management: Displays the Change Management window (see Change Manager Support in SQL Developer). Components: Displays the Component Palette: Configure Component Palette dialog box. Connections: Displays the Connections navigator. DBA: Displays the DBA navigator (see Using DBA Features in SQL Developer). Data Miner: Lets you display the Data Miner Navigator, Workflow Jobs, Workflow Property Inspector, and Component Palette. (For information about Oracle Data Miner, click Help, then Data Mining.). Data Modeler: Lets you display the Browser and Thumbnail Diagram panes of the Data Modeler in SQL Developer. DBMS Output: Displays the output of DBMS_OUTPUT package statements (see DBMS Output Pane). Debugger: Displays panes related to debugging (see Running and Debugging Functions and Procedures). Extension Diagnostics: Displays the Extensions - Log pane. Files: Displays the Files navigator, which is marked by a folder icon. You can use the Files navigator to browse, open, edit, and save files that are accessible from the local system. Find DB Object: Displays the Find Database Object pane (see Finding Database Objects). Log: Displays a pane with these tabs:
Map View: Displays the Map View pane (see Map Visualization of Spatial Data). Migration Projects: Lets you display any captured models and converted models (see SQL Developer User Interface for Migration). OWA Output: Displays Oracle Web Agent (MOD_PLSQL) output (see OWA Output Pane). Processes: Displays the Processes pane. Properties: Displays the Property Inspector. REST Data Services: Lets you display the REST Development pane (see REST Development) or the ORDS Administration pane (see Oracle REST Data Services Administration). Recent Objects: Displays a pane with names of recently opened objects. You can double-click a name in the list to go to its editing window (see Using Recent Objects). Reports: Displays the Reports navigator (see SQL Developer Reports). SQL History: Displays information about SQL statements that you have executed. You can select statements and append them to or overwrite statements on the worksheet (see SQL History). Snippets: Displays snippets (see Using Snippets to Insert Code Fragments). Task Progress: Displays the Task Progress pane. Unit Test: Displays the Unit Test navigator (see SQL Developer: Unit Testing). Show Status Bar: Controls the display of the status bar at the bottom of the SQL Developer window. Show Toolbars: Controls the display of the following toolbars: Main toolbar (under the SQL Developer menus), the Connections Navigator toolbar, the DBA navigator toolbar, the Data Miner Workflow Property Inspector toolbar, and (if a package or subprogram is open) the Code Editor toolbar. OLAP: Lets you display the OLAP Templates and Snippets pane. Navigate menu Contains options for navigating to panes and in the execution of subprograms. Back: Moves to the pane that you most recently visited. Forward: Moves to the pane after the current one in the list of visited panes. Toggle Bookmark: If you are editing a function or procedure, creates or removes a bookmark (see Using Bookmarks When Editing Functions and Procedures). Remove Bookmarks from File: Removes bookmarks from the currently active editing window for a function or procedure (see Using Bookmarks When Editing Functions and Procedures). Remove All Bookmarks: Removes bookmarks from open editing windows for functions and procedures (see Using Bookmarks When Editing Functions and Procedures). Go to Bookmark: Displays a dialog box so that you can go to a specified bookmark (see Using Bookmarks When Editing Functions and Procedures). Go to Next Bookmark: Goes to the next bookmark in the currently active editing window for a function or procedure (see Using Bookmarks When Editing Functions and Procedures). Go to Previous Bookmark: Goes to the previous bookmark in the currently active editing window for a function or procedure (see Using Bookmarks When Editing Functions and Procedures). Go to Line: Goes to the specified line number and highlights the line in the editing window for the selected function or procedure. (To display line numbers, enable Show Line Numbers under the Code Editor: Line Gutter preferences.) Go to Last Edit: Goes to the last line that was edited in the editing window for a function or procedure. Go to Recent Files: Displays the Recent Files dialog box, in which you can specify a function or procedure to go to. Run menu Contains options relevant when a function or procedure is selected or when it is open for debugging. Run [name]: Starts execution of the specified function or procedure. Debug [name]: Starts execution of the specified function or procedure in debug mode. The remaining items on the Debug menu match commands on the debugging toolbar, which is described in Running and Debugging Functions and Procedures. Team menu Contains options related to support for the Subversion version management and source control system, and for any other such systems (such as CVS) that you have added as extensions to SQL Developer through the "check for updates" feature. See Using Versioning for more information. The commands on the Team menu depend on which version management and source control systems are available for use with SQL Developer. Tools menu Invokes SQL Developer tools. Data Modeler: Starts the Data Modeler in SQL Developer if it not already active; otherwise, contains the commands About Data Modeler, Design Rules, and General Options (user preferences). Database Copy: Enables you to copy objects, schemas, or a tablespace from one database to another (see the Database Copy wizard). Database Diff: Enables you to compare two schemas to find differences between objects of the same type and name (for example, tables named CUSTOMERS) in two different schemas, and optionally to update the objects in the destination schema to reflect differences in the source schema (see the Database Differences interface). Database Export: Enables you to export (unload) some or all objects of one or more object types for a database connection to a file containing SQL statements to create these objects and optionally to export table data (see the Database Export (Unload Database Objects and Data) interface). Migration: Displays the Migration Submenu, which contains commands related to migrating third-party databases to Oracle. Real Time SQL Monitor (requires the Oracle Tuning Pack): Displays information about any query currently executing and queries that are done executing for a selected connection. To see detailed information about a query, right-click its row and select Show SQL Details. The information is especially useful for real-time monitoring of long-running SQL statements. Cursor statistics (such as CPU times and IO times) and execution plan statistics (such as number of output rows, memory, and temporary space used) are updated close to real-time during statement execution. (Internally, this feature calls the DBMS_SQLTUNE.REPORT_SQL_MONITOR subprogram.) Monitor Sessions: Displays the status of one or more sessions, using information from the V$RSRC_SESSION_INFO view, which shows how the session has been affected by the Oracle Database Resource Manager. SQL Worksheet: Displays a worksheet in which you can enter and execute SQL and PL/SQL statements using a specified connection (see Using the SQL Worksheet). Unit Test: Displays the Unit Test Submenu, which contains commands related to unit testing. Data Miner: Enables you to show the Data Miner navigator and drop the Data Miner repository. (For information about Data Miner, click Help, then Data Mining). OLAP: Enables you to create a view that pivots an Oracle OLAP fact table so that the measures identify rows instead of columns. (For information about Oracle OLAP, click Help and check for related topics in the Table of Contents.) REST Data Services: Lets you manage connections for REST administration and development, and install and uninstall Oracle REST Data Services. External Tools: Displays the External Tools dialog box, with information about user-defined external tools that are integrated with the SQL Developer interface. From this dialog box can add external tools (see Create/Edit External Tool). The Tools menu also contains items for any user-defined external tools. Features: Displays the Manage Features and Updates dialog box. Preferences: Enables you to customize the behavior of SQL Developer (see SQL Developer Preferences). Window menu Contains options relating to the appearance and behavior of the user interface. Team: Lets you display the Versions and Pending Changes. Switch Window Layout: In relevant contexts, lets you switch between editing and debugging mode. Configure Window: Lets you perform operations on the selected window or window group, such as maximizing, floating, minimizing, and docking. Reset Windows to Factory Settings: Restores the SQL Developer interface to the way it was after SQL Developer was installed; however, this does not undo any main toolbar customizations. (See also Restoring the Original "Look and Feel", and Reset Toolbars in the Customize Toolbars dialog box.) Assign File Accelerator: Lets you create SQL Worksheet Bookmarks. Close Window: Closes the currently selected window in the SQL Worksheet. Close All Documents: Closes all open windows in the SQL Worksheet. Close Other Documents: Closes all open windows in the SQL Worksheet except for the currently selected window. Documents: Displays a Documents dialog box for performing operations on open windows. Help menu Displays help about SQL Developer and enables you to check for SQL Developer updates. Search: Displays the Help Center window. Table of Contents: Displays the Help Center window. In this window you can click these icons:
Start Page: Displays a page with links for options for learning about SQL Developer. It contains the tabs Get Started and Community (the latter including the SQL Developer discussion forum). Data Mining: Displays an introductory help topic about Oracle Data Mining and its graphical user interface, Data Miner, which is integrated into SQL Developer. Check for Updates: Checks for any updates to the selected optional SQL Developer extensions, as well as any mandatory SQL Developer extensions. The available updates may include the JTDS JDBC Driver for Microsoft SQL Server and the MySQL JDBE Driver, which enable you to create connections to databases other than Oracle Database. (If the system you are using is behind a firewall, see the SQL Developer user preferences for Web Browser and Proxy.) About: Displays version-related information about SQL Developer and its components. 1.3.2 Restoring the Original "Look and Feel"If you have made changes to the SQL Developer user interface ("look and feel"), such as accidentally repositioning navigators and panes, you can restore the window to the way it was after SQL Developer was installed by clicking Window, then Reset Windows to Factory Settings. (However, if you have customized the main toolbar and want to restore it, you must either also select Reset Toolbars in the Customize Toolbars dialog box or restore the entire user interface.) To restore the entire user interface (for example, including undoing all changes to user preferences), follow these steps.
If you have made changes to the SQL Developer shortcut key (accelerator key) mappings, you can restore the mappings to the defaults for your system by clicking Tools, then Preferences, then Shortcut Keys, then More Actions, then Load Keyboard Scheme, and then selecting Default. 1.4 Database ObjectsYou can create, edit, delete (drop), and add to the Cart most types of objects in an Oracle database by using the context menu (right-click, or Shift+F10) in the Connections navigator or by clicking the Actions button in the detail pane display. For some objects, you can do other operations, as appropriate for the object type. For some object types the context menu includes Open, which generates a tabular overview display of information about objects of that type. For example, selecting Open for the Tables node in the Connections navigator displays a list of all tables for the connection, and for each table the table name, the number of rows, the table status, the number of columns, the comment (if any), and other information. For some object types the context menu includes Use as Template, which displays the appropriate Create dialog box with information for the selected object filled in. You should then change the object name and any other aspects of the definition, as needed, to create a new object of that type. Note: The actions available from context menus and Actions buttons depend on the Oracle Database release number for the specified database connection. If an action mentioned in the text is not available with a connection, it may be that the feature was not available in that release of Oracle Database. The dialog boxes for creating and editing many objects contain a tab or pane named DDL or SQL, where you can see the statement that SQL Developer will use to perform the actions you specify (CREATE to create an object, ALTER to apply changes to an existing object). You can search for specific objects associated with an Oracle database connection by clicking the Search icon. If you have connected to a MySQL database or a third-party (non-Oracle) database, such as Microsoft SQL Server, Sybase Adaptive Server, or IBM DB2, you can view their objects using the Connections navigator. (For information about connecting to MySQL and third-party databases, see the SQL Developer user preferences for Database: Third Party JDBC Drivers.) 1.4.1 Applications (Application Express 3.0.1 and Later)Effective with Oracle Application Express 3.0.1, if you use SQL Developer to connect to a schema that owns any Application Express applications, the Connections navigator has an Application Express node. You can click an application name to display tabs (Application, Pages, LOVs, Lists, Templates, Breadcrumbs, and so on) with information about the application. You can perform the following operations on an Application Express application by right-clicking the application name in the Connections navigator and selecting an item from the menu:
The following operations are available only by right-clicking the Application Express node in the Connections navigator and selecting an item from the menu:
1.4.2 Cache Groups (Oracle TimesTen In-Memory Database)A cache group describes a collection of in-memory database tables that map to all or a subset of the tables in an Oracle database. A cache group can consist of all or a subset of the rows and columns in these tables. Multiple cache groups can be used to cache different sets of related tables in the Oracle database. 1.4.3 ChainsA chain is an Oracle Scheduler object that enables you to implement dependency scheduling, in which jobs are started depending on the outcomes of one or more previous jobs. A chain consists of multiple steps that are combined using dependency rules. The dependency rules define the conditions that can be used to start or stop a step or the chain itself. Conditions can include the success, failure, or completion-codes or exit-codes of previous steps. Logical expressions, such as AND/OR, can be used in the conditions. In a sense, a chain resembles a decision tree, with many possible paths for selecting which tasks run and when. 1.4.4 CredentialsA credential is an Oracle Scheduler object that is a user name and password pair stored in a dedicated database object. A job uses a credential to authenticate itself with a database instance or the operating system so that it can run. 1.4.5 Database DestinationsA database destination is an Oracle Scheduler object that defines a location for running a job. There are two types of destinations: an external destination specifies a remote host name and IP address for running a remote external job; a database destination specifies a remote database instance for running a remote database job. If you specify a destination when you create a job, the job runs on that destination. If you do not specify a destination, the job runs locally, on the system on which it is created. 1.4.6 Database Links (Public and Private)A database link is a database object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system; however, to access non-Oracle systems you must use Oracle Heterogeneous Services. After you have created a database link, you can use it to refer to tables and views in the other database. The Connections navigator has a Database Links node for all database links (public and private) owned by the user associated with the specified connection, and a Public Database Links node for all public database links on the database associated with the connection. You can perform the following operations on a database link by right-clicking the database link name in the Connections navigator and selecting an item from the menu:
1.4.7 Destination GroupsA destination group is an Oracle Scheduler object whose members are a list of Scheduler destination objects. In a database destination group, the members are database destinations, for running remote database jobs. In an external destination group, members are external destinations, for running remote external jobs. 1.4.8 DirectoriesA directory object specifies an alias for a directory (called a folder on Windows systems) on the server file system where external binary file LOBs (BFILEs) and external table data are located. To create a directory (that is, a directory object), you can use SQL Developer or the SQL statement CREATE DIRECTORY. You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility. All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users. 1.4.9 EditionsEdition-based redefinition, introduced in Oracle Database 11g Release 2 (11.2), enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time. To upgrade an application while it is in use, you copy the objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users. To specify the current edition, right-click the edition name and select Set Current Edition. To create an edition under an existing edition, right-click the edition name and select Create Edition. To delete an edition (and optionally all editions under it), right-click the edition name and select Drop Edition. 1.4.10 File WatchersA file watcher is an Oracle Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. 1.4.11 FunctionsA function is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Functions return a value; procedures do not return a value.) You can perform the following operations on a function by right-clicking the function name in the Connections navigator and selecting an item from the menu:
1.4.12 IndexesAn index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Indexes are automatically created on primary key columns; however, you must create indexes on other columns to gain the benefits of indexing. You can perform the following operations on an index by right-clicking the index name in the Connections navigator and selecting an item from the menu:
1.4.13 Java SourcesJava sources can be created and managed in the database. You can create a Java source object by right-clicking the Java node in the Connections navigator, selecting Load Java, and specifying the Java source name and either entering the source code or loading a Java source, class, or resource from a file (BFILE). (A CREATE OR REPLACE AND RESOLVE JAVA SOURCE statement is executed using the information you specify.) 1.4.14 JobsA job is an Oracle Scheduler object that is a collection of metadata that describes a user-defined task. It defines what needs to be executed (the action), when (the one-time or recurring schedule or a triggering event), where (the destinations), and with what credentials. A job has an owner, which is the schema in which it is created. 1.4.15 Job ClassesA job class is an Oracle Scheduler object that enables the Scheduler administrator to group jobs for logical purposes, such as to assign the same set of attribute values to member jobs, to set service affinity for member jobs, to set resource allocation for member jobs, or to group jobs for prioritization. 1.4.16 OperatorsAn operator is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. Operators return a value. You can perform the following operations on an operator by right-clicking the name in the Connections navigator and selecting an item from the menu:
1.4.17 Materialized ViewsA materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. 1.4.18 Materialized View LogsA materialized view log is a table associated with the master table of a materialized view. When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh. 1.4.19 Multitenant Container Database (CDB)A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. 1.4.20 PackagesA package is an object that contains subprograms, which are programming objects that can be stored and executed in the database server, and called from other programming objects or applications. A package can contain functions or procedures, or both. You can perform the following operations on a package by right-clicking the package name in the Connections navigator and selecting an item from the menu:
1.4.21 ProceduresA procedure is a type of PL/SQL subprogram, which is a programming object that can be stored and executed in the database server, and called from other programming objects or applications. (Procedures do not return a value; functions return a value.) You can perform the following operations on a procedure by right-clicking the procedure name in the Connections navigator and selecting an item from the menu:
1.4.22 ProgramsA program is an Oracle Scheduler object that describes what is to be run by the Scheduler. A program includes an action, a type, and the number of arguments that the stored procedure or external executable accepts. (A program is a separate entity from a job. A job runs at a certain time or because a certain event occurred, and invokes a certain program.) 1.4.23 QueuesA queue is an object in which messages are enqueued and dequeued. Queues are managed by Oracle Streams Advanced Queueing (AQ). 1.4.24 Queue TablesA queue table is a table that holds messages to be used with Oracle Streams Advanced Queueing (AQ). 1.4.25 Recycle BinThe Recycle bin (applicable only to Oracle Database Release 10g and later) holds objects that have been dropped (deleted). The objects are not actually deleted until a commit operation is performed. Before the objects are actually deleted, you can "undelete" them by selecting them in the Recycle bin and selecting Flashback to Before Drop from the context menu. The Recycle bin is available only for non-system, locally managed tablespaces. Thus, to take advantage of the Recycle bin to be able to recover dropped tables for a database user, create or edit the
user to have a non-system default tablespace (for example, You can perform the following operations on an object in the Recycle bin by right-clicking the object name in the Recycle bin in the Connections navigator and selecting an item from the menu:
1.4.26 Replication Schemes (Oracle TimesTen In-Memory Database)A replication scheme is a configuration, using SQL statements and a transaction-based log, whereby committed changes are copied from their source to one or more subscriber databases. The goal is to enable high efficiency and low overhead during the replication. 1.4.27 SchedulesA schedule is an Oracle Scheduler object that specifies when and how many times a job is run. Schedules can be shared by multiple jobs. For example, the end of a business quarter may be a common time frame for many jobs; instead of having to define an end-of-quarter schedule each time a new job is defined, job creators can point to a named schedule. 1.4.28 SequencesSequences are used to generate unique integers. You can use sequences to automatically generate primary key values. 1.4.29 Synonyms (Public and Private)Synonyms provide alternative names for tables, views, sequences, procedures, stored functions, packages, materialized views, Java class database objects, user-defined object types, or other synonyms. The Connections navigator has a Synonyms node for all synonyms (public and private) owned by the user associated with the specified connection, and a Public Synonyms node for all public synonyms on the database associated with the connection. 1.4.30 TablesTables are used to hold data. Each table typically has multiple columns that describe attributes of the database entity associated with the table, and each column has an associated data type. You can choose from many table creation options and table organizations (such as partitioned tables, index-organized tables, and external tables), to meet a variety of enterprise needs. To create a table, you can do one of the following:
You can perform the following operations on a table by right-clicking the table name in the Connections navigator and selecting an item from the menu:
You can perform the following operations on a column in a table by right-clicking the column name in the Connections navigator and selecting an item from the menu:
The Model tab in a table display includes Open in Data Modeler, which enables you to open the table and sometimes related tables in a Data Modeler diagram view.
Related Topics
1.4.30.1 Flashback Table SupportFor tables in Oracle Database Release 11.1 and later, the table display includes the Flashback tab, which provides a view of the modified and original data in the table. If you have appropriate privileges, you can click the Undo SQL subtab to select and review the syntax required to undo changes. 1.4.31 TriggersTriggers are stored PL/SQL blocks associated with a table, a schema, or the database, or anonymous PL/SQL blocks or calls to a procedure implemented in PL/SQL or Java. Oracle Database automatically executes a trigger when specified conditions occur. A crossedition trigger is intended to fire when DML changes are made in a database while an online application that uses the database is being patched or upgraded with edition-based redefinition. The body of a crossedition trigger is designed to handle these DML changes so that they can be appropriately applied after the changes to the application code are completed. To create a crossedition trigger, you must be enabled for Editions. 1.4.32 TypesA data type associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a function or procedure. These properties cause Oracle Database to treat values of one data type differently from values of another data type. Most data types are supplied by Oracle, although users can create data types. 1.4.33 Users (Other Users)Database users are accounts through which you can log in to the database. In the Connections navigator, you can see the Other Users in the database associated with a connection, but the objects that you are allowed to see for each user are determined by the privileges of the database user associated with the current database connection. If you are connected as a user with the DBA role, you can create a database user by right-clicking Other Users and selecting Create User, and you can edit an existing database user by right-clicking the user under Other Users and selecting Edit User. 1.4.34 ViewsViews are virtual tables (analogous to queries in some database products) that select data from one or more underlying tables. Oracle Database provides many view creation options and specialized types of views (such as materialized views) to meet a variety of enterprise needs. Editioning views are shown in a separate navigator node if the connection is to an Oracle Database release that supports Editions. An editioning view selects a subset of the columns from a single base table and, optionally, provides aliases for them. In providing aliases, the editioning view maps physical column names (used by the base table) to logical column names (used by the application). You can perform the following operations on a view by right-clicking the view name in the Connections navigator and selecting an item from the menu:
1.4.35 Window GroupsA window group is an Oracle Scheduler object that is a list of Oracle Scheduler Windows. Scheduler jobs that are scheduled to be run in a window group will be activated in that time span and using that resource plan for all windows in the group. You can group windows for ease of use in scheduling jobs. If a job must run during multiple time periods throughout the day, week, and so on, you can create a window for each time period, and then add the windows to a window group. You can
then set the For example, if you had a window called "Weekends" and a window called "Weeknights," you could add these two windows to a window group called "Downtime." The data warehousing staff could then create a job to run queries according to this Downtime window group—on weeknights and weekends—when the queries could be assigned a high percentage of available resources. 1.4.36 WindowsA window is an Oracle Scheduler object that can be used to automatically start jobs or to change resource allocation among jobs during various time periods of the day, week, and so on. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am". Windows work with job classes to control resource allocation. Each window specifies the resource plan to activate when the window opens (becomes active), and each job class specifies a resource consumer group or specifies a database service, which can map to a consumer group. A job that runs within a window, therefore, has resources allocated to it according to the consumer group of its job class and the resource plan of the window. 1.4.37 XML DB RepositoryOracle XML DB Repository is a component of Oracle Database that is optimized for handling XML data. The Oracle XML DB repository contains resources, which can be either folders (directories, containers) or files. To create a subfolder of an existing folder, right-click the folder name and select Create Subfolder. To delete a folder (and optionally all subfolders under it), right-click the folder name and select Drop Folder. 1.4.38 XML SchemasXML schemas are schema definitions, written in XML, that describe the structure and various other semantics of conforming instance XML documents. You can edit an XML schema by right-clicking the XML schema name in the Connections navigator and selecting Open from the menu; you can delete a selected schema by selecting Drop Schema from the menu. 1.4.39 Captured and Converted Database Objects (for Migration)If you are migrating a third-party database to Oracle, the Captured Models and Converted Models navigators can display models that include database objects, such as tables and procedures. A captured object represents an object in the captured third-party database, and a converted object represents an Oracle model of that object as it will be created in the Oracle database. The context menu for each captured object includes Convert to Oracle, which creates a corresponding converted object. The context menu for each converted object includes Generate, which creates the corresponding Oracle Database object. (The context menus will contain other items as appropriate for the object.) For information about the related Oracle Database objects, see the following:
1.5 Database ConnectionsA connection is a SQL Developer object that specifies the necessary information for connecting to a specific database as a specific user of that database. You must have at least one database connection (existing, created, or imported) to use SQL Developer. You can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database. You can also connect to schemas for MySQL and Hive, and selected third-party (non-Oracle) databases, such as Microsoft SQL Server, Sybase Adaptive Server, and IBM DB2, and view metadata and data. When you start SQL Developer and whenever you display the database connections dialog box, SQL Developer automatically reads any connections defined in the tnsnames.ora file on your system, if that file exists. You can specify the tnsnames.ora location in the Database : Advanced preferences.By default, tnsnames.ora is located in the $ORACLE_HOME/network/admin directory, but it can also be in the directory specified by the TNS_ADMIN environment variable or registry value or (on Linux systems) the global configuration directory. On Windows systems, if the tnsnames.ora file exists but its connections are not being used by SQL Developer, define TNS_ADMIN as a system environment variable. For information about the tnsnames.ora file, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference. You can create additional connections (for example, to connect to the same database but as different users, or to connect to different databases). Each database connection is listed in the Connections navigator hierarchy. To create a new database connection, right-click the Connections node and select New Database Connection. Use the dialog box to specify information about the connection. You can also create a new database connection by selecting an existing connection in that dialog box, changing the connection name, changing other connection attributes as needed, and clicking Save or Connect. See Create/Edit/Select Database Connection To create (automatically generate) a database connection for each unlocked user account in the Oracle database instance on the local system, right-click the Connections node and select Create Local Connections. The connections are placed in a folder named Auto-Generated Local Connections. For these autogenerated connections (except for the one named Note: To use the Create Local Connections feature, you must specify an Oracle Home: in the Database: Advanced user preferences, click Configure for Use Oracle Client. The Oracle Home must be for Release 11.2.0.3 or later. To edit the information about an existing database connection, right-click the connection name in the Connections navigator display and select Properties. Use the dialog box to modify information about the connection, but do not change the connection name. You can use folders in the Connections navigator to organize connections into groups: for example, one folder for connections on your local system, another for connections on the test system, and another for connections on the production system. To export information about the existing database connections into a JSON file that you can later use for importing connections, right-click Connections in the Connections navigator display and select Export Connections. Use the dialog box to specify the connections to be exported. To import connections that had previously been exported (adding them to any connections that may already exist in SQL Developer), right-click Connections in the Connections navigator display and select Import Connections. Use the dialog box to specify the connections to be imported. To browse just the objects for a specific connection, right-click the connection name in the Connections navigator display and select Schema Browser. A separate Schema Browser tab for that connection is opened in the Connections navigator, where you can click object types to view and perform operations on available objects of that type in the schema associated with the connection. To perform limited database management operations if you are connected AS SYSDBA, right-click the connection name in the Connections navigator display and select Manage Database. You can click to refresh the read-only display of memory (SGA and PGA) and tablespace information. If a listener is running with a static listener configured for the database, you can also click to start and stop the database. To perform remote debugging 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, right-click the connection name in the Connections navigator display and select Remote Debug. Use the dialog box to specify remote debugging information. To estimate or compute statistics for objects in a database schema, right-click the connection name in the Connections navigator display and select Gather Schema Statistics. Statistics are used to optimize SQL execution. To generate documentation in HTML format (comparable to Javadoc for Java classes) about a schema, right-click the connection name in the Connections navigator display and select Generate DB Doc. To view the generated documentation, open the index.html file in the output directory that you specified. (Note that Generate DB Doc is also used for migration projects: right-click the migration project name in the Migration Projects navigator display and select Migration Project Doc; you can specify whether to include child nodes of the selected node and child master-detail per-row reports.) To reset an expired password for the database user associated with a connection, right-click the connection name in the Connections navigator display and select Reset Password. To rename a connection, right-click the connection name in the Connections navigator display and select Rename Connection. To delete a connection (that is, delete it from SQL Developer, not merely disconnect from the current connection), right-click the connection name in the Connections navigator display and select Delete. Deleting a connection does not delete the user associated with that connection. To connect using an existing connection, expand its node in the Connections navigator, or right-click its name and select Connect. A SQL Worksheet window is also opened for the connection.To create a separate unshared worksheet for a connection, click in the worksheet and use Ctrl+Shift+N. To disconnect from the current connection, right-click its name in the Connections navigator and select Disconnect. To reconnect to a connection where the session had been terminated, right-click its name in the Connections navigator and select Reconnect. To enable Oracle REST Data Services access to objects in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Enable REST Services (available only if Oracle REST Data Services has been installed on the system containing the database associated with the connection). To drop support for Oracle REST Data Services access to objects in the schema associated with a connection, right-click its name in the Connections navigator and select REST Services, then Drop REST Services. To specify a preference for using an OCI (thick, Type 2) driver (if available) instead of a JDBC (thin) driver for basic and TNS (network alias) database connections, enable the Use OCI/Thick driver option under the Database : Advanced user preferences. 1.5.1 Using Folders to Group ConnectionsYou can use folders in the Connections navigator to organize connections into groups: for example, one folder for connections on your local system, another for connections on the test system, and another for connections on the production system. To create a folder to hold connections, right-click the name in the Connections navigator of a connection to be added to the folder, select Add to Folder and then New Folder, and specify the folder name (such as Local Connections). To add more connections to a folder, right-click the name in the Connections navigator of a connection to be added to the folder, and select Add to Folder and then the name of the folder into which to add the connection. To move a connection from one folder to another folder, right-click the connection name under its current folder, select Add to Folder, and then either the name of the destination folder or New Folder to move the connection to a new folder to be created. To remove a connection from the folder, right-click the connection name under the folder and select Remove from Folder. (This does not delete the connection; it is moved to the top level in the Connections navigator hierarchy display.) To remove a folder, right-click the folder name select Remove Folder. (This does not delete any connections that are in the folder; these connections are moved to the top level in the Connections navigator hierarchy display.) To rename a folder, right-click the folder name, select Rename Folder, and specify the new name. 1.5.2 Sharing of ConnectionsBy default, each connection in SQL Developer is shared when possible. For example, if you open a table in the Connections navigator and two SQL Worksheets using the same connection, all three panes use one shared connection to the database. In this example, a commit operation in one SQL Worksheet commits across all three panes. If you want a dedicated session, you must duplicate your connection and give it another name. Sessions are shared by name, not connection information, so this new connection will be kept separate from the original. 1.5.3 Advanced Security for JDBC Connection to the DatabaseYou are encouraged to use Oracle Advanced Security to secure a JDBC or OCI connection to the database. Both the JDBC OCI and the JDBC Thin drivers support at least some of the Oracle Advanced Security features. If you are using the OCI driver, you can set relevant parameters in the same way that you would in any Oracle client setting. The JDBC Thin driver supports the Oracle Advanced Security features through a set of Java classes included with the JDBC classes in a Java Archive (JAR) file and supports security parameter settings through Java properties objects. 1.5.4 Connections with Operating System (OS) AuthenticationWhen you create a connection to an Oracle database that is using operating system (OS) authentication, you can omit the user name and password; that is, specify a connection name and all the other necessary information, except do not specify a user name or password. If you omit the user name and password trying to create a connection to a system that is not configured for external authentication, an error message is displayed. See Also: For information about using external authentication, including the use of the OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT database initialization parameters, see Oracle Database Security Guide 1.5.5 Connections with Proxy AuthenticationProxy authentication enables one JDBC connection to act as a proxy for other JDBC connections. If you use the Proxy Connection option when you create a database connection, the connection will be used to connect as the specified user for the connection, but authenticated using the user name and either the password or distinguished name of the proxy user. For information about using a middle tier server for proxy authentication, see Oracle Database Security Guide. For example, to create connection for a user named PROXY_USER but connecting using the user name and password of existing database user SCOTT, follow these steps.
In this example, when you connect using the proxy_conn connection, the user name and password for user SCOTT are used to connect to the database, but the connection sees those objects that the PROXY_USER user is permitted to see. The preceding instructions cause two sessions to be started (one for the proxy user and one for the proxy client) when the connection is opened. If you want to have a single session (with no second password or distinguished name required) started, you can follow these steps instead after you create the proxy user and grant it appropriate privileges:
1.5.6 Connections with SSH AuthenticationSSH (Secure Shell) authentication enables you to specify that the SSH protocol should be used to authenticate users attempting to connect to the database. To enable SSH authentication, click View, and then click SSH. In the SSH Hosts pane, right-click and select New SSH Host. Name: A name for the SSH session. Host: SSH server. SQL Developer will create an SSH session to this host, using the specified details. Port: SSH port. The default port is 22. Username: User name that will be used to authorize the SSH session. Use Key File: Specifies that a key file should be used to provide authentication. The key file contains a private key that should correspond to a public key registered with the server. The key file must be in OpenSSH format. The server verifies that SQL Developer has access to the proper private key and thus the user is who he or she claims to be. Key File: Path to the key file. When you create an SSH (Secure Shell), you can create one or more local port forwards and/or remote port forwards. Name: A name to be given to the local or remote port forward. Host: Host name for the forward. Port: Port on the specified host. Automatically assign [local|remote] port: Lets a port be assigned automatically. Use specific [local|remote] port: Specify the port to be used. For detailed information about on the SSH protocol and authorization, see the OpenBSD man page for You may also find the following article helpful: "SSH Tunnel - Local and Remote Port Forwarding Explained With
Examples" ( When using SSH authentication, note the following:
1.6 Entering and Modifying DataYou can use SQL Developer to enter data into Tables and to edit and delete existing table data. To do any of these operations, select the table in the Connections navigator, then click the Data tab in the table detail display. (If you click one of the other tabs -- Columns, Constraints, Grants, Statistics, Triggers, and so on -- you can view the information and perform relevant context menu (right-click) operations.) The following figure shows the Data pane for a table named BOOKS, with a filter applied to show only books whose rating is 10, and after the user has double-clicked in the Title cell for the first book. Icons and other controls under the Data tab provide the following options:
When you enter a cell in the grid, you can directly edit the data for many data types, and for all data types you can click the ellipsis (...) button to edit the data. For binary data you cannot edit the data in the cell, but must use the ellipsis button. In the data grid, the context menu (right-click) includes the following commands:
You can copy and paste data between table grid cells and cells in a Microsoft Excel worksheet. To copy table data to the clipboard, click the column header (for all column data) or select specific cells and press Ctrl+C; to copy the column header text along with the table data, press Ctrl+Shift+C. To sort the display of rows by values within a column, double-click the column header; to switch between ascending and descending sorting, double-click the up/down arrow in the column header. In the Data pane for a table or view, you can split the display vertically or horizontally to see two (or more) parts independently by using the split box (thin blue rectangle), located to the right of the bottom scroll bar and above the right scroll bar. In the Data pane, the acceptable format or formats for entering dates may be different from the date format required by SQL*Plus. 1.7 Running and Debugging Functions and ProceduresYou can use SQL Developer to run and debug PL/SQL subprograms (functions and procedures).
In both cases, a code editing window is displayed. The following figure shows the code editing window being used to debug a procedure named LIST_A_RATING2, which is used for tutorial purposes in Debug a PL/SQL Procedure. In the code editing window, under the tab with the name of the subprogram, is a toolbar, and beneath it is the text of the subprogram, which you can edit. You can set and unset breakpoints for debugging by clicking to the left of the thin vertical line beside each statement with which you want to associate a breakpoint. (When a breakpoint is set, a red circle is displayed.) The toolbar under the tab for the subprogram name includes the icons shown in the following figure.
The Debugging - Log tab under the code text area contains the debugging toolbar and informational messages. The debugging toolbar has the icons shown in the following figure.
The Breakpoints tab displays breakpoints, both system-defined and user-defined. The Smart Data tab displays information about variables, using your Debugger: Smart Data preferences. You can also specify these preferences by right-clicking in the Smart Data window and selecting Preferences. The Data tab displays information about variables, using your Debugger: Data preferences. You can also specify these preferences by right-clicking in the Data window and selecting Preferences. The Watches tab displays information about watches. If the function or procedure to be debugged is on a remote system, see also Remote Debugging. 1.7.1 Using Bookmarks When Editing Functions and ProceduresWhen you are editing a long function or procedure, you may find it convenient to create bookmarks in the code so that you can easily navigate to points of interest. To create or remove a bookmark, click Navigate, then Toggle Bookmark. When a bookmark is created, an icon appears to the left of the thin vertical line. To go to a specific bookmark, click Navigate, then Go to Bookmark. To go to the next or previous bookmark, click Navigate, then Go to Next Bookmark or Go to Previous Bookmark, respectively. To remove all bookmarks from the currently active editing window for a function or procedure or from all open editing windows, click Navigate, then Remove Bookmarks from File or Remove All Bookmarks, respectively. You can also go to a specific line or to your last edit by clicking Navigate, then Go to Line or Go to Last Edit, respectively. 1.7.2 Remote DebuggingRemote debugging allows you to debug a procedure that is initiated from a program other than SQL Developer. You can also use remote debugging to debug stored procedures initiated from computers other than your local system. Remote debugging involves many of the steps as for local debugging; however, you need to set up the SQL Developer debugger to listen. Do the following before you start the remote debugging:
Then, follow the steps that you would for local debugging. 1.7.3 Displaying SQL Trace (.trc) FilesIf you have any SQL Trace (.trc) output files, you can display them in SQL Developer as an alternative to using the TKPROF program to format the contents of the trace file. To open a .trc file in SQL Developer and see an attractive, effective display of the information, click File, then Open, and specify the file; or drag the file's name or icon into the SQL Developer window. You can then examine the information in the List View, Statistics View, and History panes, with each pane including options for filtering and controlling the display. 1.7.4 Using the PL/SQL Hierarchical ProfilerFor an Oracle Database Release 11.1 or later connection, you can use the PL/SQL hierarchical profiler to identify bottlenecks and performance-tuning opportunities in PL/SQL applications. Profiling consists of the two steps: running the PL/SQL module in profiling mode, and analyzing the reports. In addition, some one-time setup work is required the first time you use profiling in SQL Developer. To initiate profiling, right-click the name of the function or procedure in the Connections navigator hierarchy and select Profile, or click the Profile button on the PL/SQL source editor toolbar. After the function or procedure is run in profiling mode, the profiler reports are located at the Execution Profiles tab of the object viewer window. You can review subprogram-level execution summary information, such as:
1.7.5 Setting Expression WatchesA watch enables you to monitor the changing values of variables or expressions as your program runs. After you enter a watch expression, the Watches window displays the current value of the expression. As your program runs, the value of the watch changes as your program updates the values of the variables in the watch expression. A watch evaluates an expression according to the current context which is controlled by the selection in the Stack window. If you move to a new context, the expression is reevaluated for the new context. If the execution point moves to a location where any of the variables in the watch expression are undefined, the entire watch expression becomes undefined. If the execution point returns to a location where the watch expression can be evaluated, the Watches window again displays the value of the watch expression. To open the Watches window, click View, then Debugger, then Watches. To add a watch, right-click in the Watches window and select Add Watch. To edit a watch, right-click in the Watches window and select Edit Watch. 1.7.6 Semantic AnalysisFor advice on grammatical issues with SQL statements in the code editor, use the Semantic Analysis Info Tips feature. If this option is enabled in Preferences, semantic issues in the code are indicated with a squiggle line. When you hover over the squiggle line, a tooltip is displayed with options to resolve the problem. Additionally, there are red, yellow, and purple markers in the right gutter margin that help you to locate the squiggle lines in the code. Red denotes errors when compiling the PLSQL module, purple indicates parse errors, and yellow highlights warnings. When you hover over the markers in the right gutter margin, you see a preview of the code that has the problem. Click the preview to go to the corresponding position in the editor. To enable semantic analysis, select Preferences from the Tools menu, expand Code Editor, select Completion Insight, and then select the Semantic Analysis Info Tips checkbox. To configure the color and style of the markers and squiggles, expand Code Editor and select PL/SQL Syntax Colors. In the Available Styles box, select PLSQL Errors to control the color of backend errors (red by default), select SQL&PLSQL Syntax errors to determine the color of syntax errors detected by SQL Developer (purple by default), select PLSQL Warnings to control the color of semantic analysis issues detected by SQL Developer (yellow by default). 1.7.6.1 SQL Injection DetectionBeginning with the 18.1 release, SQL Developer provides interactive advisories in the code editor identifying code that is at risk of SQL injection. SQL injection occurs when applications that use client-supplied data in SQL statements are maliciously exploited, thereby gaining unauthorized access to a database to view or manipulate restricted data. For more information about SQL injection, see Oracle Database PL/SQL Language Reference. The code editor displays warnings when user-specified data from function parameters is used in a dynamic SQL statement in such a way that it might be exploited by a malicious user to cause unexpected behavior. Data may flow through multiple local variables, functions, and operators before being used. The most effective way to make the PL/SQL code invulnerable to SQL injection attacks is to use bind arguments with static SQL. Passing data through bind arguments is safe, and also provides better performance than using dynamic SQL. If you must use dynamic SQL, avoid the warning by sanitizing the data using the appropriate DBMS_ASSERT function. The following example safely enquotes the passed value:
Alternatively, this example does not enquote the value, but throws an exception if the passed value is unsafe:
The SQL injection warnings in the code are indicated by a squiggle line and by yellow markers in the right gutter margin. You can hover over each warning to see the sequence of lines tracing the value from the parameter to its use in a dynamic SQL statement. The following figure is an example of a SQL injection warning flagged in the editor. 1.8 Using the SQL WorksheetYou can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file. You can display a SQL Worksheet by right-clicking a connection in the Connections navigator and selecting Open SQL Worksheet, by selecting Tools and then SQL Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the Select Connection dialog box, select the database connection to use for your work with the worksheet. You can also use that dialog box to create and edit database connections. (You can have a SQL Worksheet window open automatically when you open a database connection by enabling the appropriate SQL Developer user preference under Database Connections.) To create a separate unshared worksheet for a connection, click in the worksheet and use Ctrl+Shift+N. The SQL Worksheet has Worksheet and Query Builder tabs, as shown in the following figure (where the Worksheet tab is selected): SQL Worksheet toolbar (under the Worksheet tab): Contains icons for the following operations:
The context menu (right-click, or Shift+F10) includes the preceding Worksheet toolbar operations, plus the following operations (some depending on the type of object displayed in the worksheet):
Enter SQL Statement: The statement or statements that you intend to execute. For multiple statements, each non-PL/SQL statement must be terminated with either a semicolon or (on a new line) a slash (/), and each PL/SQL statement must be terminated with a slash (/) on a new line. SQL keywords are automatically highlighted. To format the statement, right-click in the statement area and select Format SQL. You can drag some kinds of objects from the Connections navigator and drop them into the Enter SQL Statement box:
To view details for any object, you can select its name in the Enter SQL Statement box and select Popup Describe from the context menu (or press Shift+F4). For example, if you select a table name and press Shift+F4, information about Columns, Constraints, Grants, and so on is displayed; or if you select a procedure name and press Shift+F4, information about Code, Grants, Dependencies, References, and Details is displayed. Tabs display panes with the following information:
To toggle the relative heights of the Enter SQL Statement area and the area for tabs and display panes, press Ctrl+Alt+L. You can also manually adjust the heights. 1.8.1 SQL*Plus Statements Supported in SQL WorksheetThe SQL Worksheet supports SQL*Plus statements. SQL*Plus statements must be interpreted by the SQL Worksheet before being passed to the database; any statements that are not supported by the SQL Worksheet are ignored and not passed to the database. The following SQL*Plus statements are supported by the SQL Worksheet: @ @@ / acc[ept] apex a[ppend] archive_log bre[ak] bti[tle] cd c[hange] clear comp[ute] conn[ect] copy ctas def[ine] del desc[ribe] disc[onnect] exit find format get help information i[nput] l[ist] load oerr ho[st] passw[ord] pau[se] print quit rem[ark] reserved_words r[un] sav[e] set (valid SET options corresponding to options in SHOW ALL output) seterrorl show (SHOW ALL and individual options shown in SHOW ALL output) shu[tdown] spo[ol] {filename[.ext] | OFF} sta[rt] startup timi[ng] tti[tle] undef[ine] var[iable] whenever xquery For information about SQL*Plus statements, you can enter the 1.8.2 Script RunnerThe script runner emulates a limited set of SQL*Plus features. You can often enter SQL and SQL*Plus statements and execute them by clicking the Run Script icon. The Script Output pane displays the output. The SQL*Plus features available in the script runner include @, @@, CONNECT, EXIT, QUIT, UNDEFINE, WHENEVER, and substitution variables. For example, to run a script named c:\myscripts\mytest.sql, type @c:\myscripts\mytest in the Enter SQL Statement box, and click the drop-down next to the Execute Statement icon and select Run Script. The following considerations apply to using the SQL Developer script runner:
If you have SQL*Plus available on your system, you may want to use it instead of the script runner. 1.8.3 Execution PlanThe Execute Explain Plan icon generates the execution plan, which you can see by clicking the Explain Plan tab. The execution plan is the sequence of operations that will be performed to execute the statement. An execution plan shows a row source tree with the hierarchy of operations that make up the statement. For each operation, it shows the ordering of the tables referenced by the statement, access method for each table mentioned in the statement, join method for tables affected by join operations in the statement, and data operations such as filter, sort, or aggregation. In addition to the row source tree, the plan table displays information about optimization (such as the cost and cardinality of each operation), partitioning (such as the set of accessed partitions), and parallel execution (such as the distribution method of join inputs). 1.8.4 Autotrace PaneThe Autotrace pane displays trace-related information when you execute the SQL statement by clicking the Autotrace icon. Most of the specific information displayed is determined by the SQL Developer Preferences for Database: Autotrace/Explain Plan. If you cancel a long-running statement, partial execution statistics are displayed. This information can help you to identify SQL statements that will benefit from tuning. For example, you may be able to optimize predicate handling by transitively adding predicates, rewriting predicates using Boolean algebra principles, moving predicates around in the execution plan, and so on. To use the autotrace feature, the database user for the connection must have the SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY privileges. 1.8.5 DBMS Output PaneThe PL/SQL DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure. The DBMS Output pane is used to display the output of that buffer. This pane contains icons and other controls for the following operations:
1.8.6 OWA Output PaneOWA (Oracle Web Agent) or MOD_PLSQL is an Apache (Web Server) extension module that enables you to create dynamic web pages from PL/SQL packages and stored procedures. The OWA Output pane enables you to see the HTML output of MOD_PLSQL actions that have been executed in the SQL Worksheet. This pane contains icons for the following operations:
1.8.7 SQL HistoryYou can click View, then History (or press F8 in the SQL Worksheet) to view a dockable window with SQL statements and scripts that you have executed, and optionally select one or more statements to have them either replace the statements currently on the SQL Worksheet or be added to the statements currently on the SQL Worksheet. You can click on a column heading to sort the rows by the values in that column. The SQL history list will not contain any statement that can include a password. Such statements include (but are not necessarily limited to) CONNECT, ALTER USER, and CREATE DATABASE LINK. You can control the maximum number of statements in the history by setting the SQL History Limit preference (see Database: Worksheet preferences). Append: Appends the selected statement or statements to any statements currently on the SQL Worksheet. You can also append the selected statement or statements by dragging them from the SQL History window and dropping them at the desired location on the SQL Worksheet. Replace: Replaces any statements currently on the SQL Worksheet with the selected statement or statements. Clear History: Removes the selected statement or statements (or all statements if no statements are selected) from the SQL history. (You will be asked to confirm this action.) Filter: If you type a string in the text box and click Filter, only SQL statements containing that string are displayed. 1.8.8 Query BuilderThe Query Builder tab in the SQL Worksheet enables you to display and build SQL queries graphically. You can create a SELECT statement by dragging and dropping table and view names and by graphically specifying columns and other elements of the query. While you are building the query, you can click the Worksheet tab to see the SELECT statement reflecting current specifications, and then click the Query Builder tab to continue building the query if you want. In the area below the graphical display of tables and views, you can specify one or more lines with the following information: Output: Specifies whether to include the expression in the statement output. Expression: Column name or expression. Aggregate: Aggregation function to be used (Avg, Avg Distinct, Count, and so on). Alias: Column alias to be used. Sort Type: Ascending or Descending sorting of results. Sort Order: Order to use in sorting results if multiple columns or expressions are to be used (for example, sorting first by department and then by salary within each department). Grouping: Specifies whether to insert a GROUP BY clause. Criteria: An expression with one or more criteria that must be satisfied for a result to be returned. You can specify any WHERE clause (without the WHERE keyword). For example, for Or: You can specify one or more OR clauses to be added to the query criteria. For example, if Expression = 1.8.9 Command-Line Interface for SQL FormattingAs an alternative to using the SQL Developer graphical interface for formatting a Before invoking the command-line interface for SQL formatting, start the SQL Developer graphical interface, so that the Database: SQL Formatter preferences (which are used for the formatting) are loaded and available. The following example takes the SQL code in C:\sqldeveloper\sqldeveloper\bin>sdcli format input=c:\temp\myfile.sql output=c:\temp\myfile_out.sql 1.8.10 SQL Worksheet "Hints" for Formatting OutputYou can use special SQL Worksheet "hints" to generate output in several formats, such as CSV and SQL INSERT statements. (These hints do not work in SQL*Plus but do work in SQLcl.) You must use Run Script (F5), not Execute Statement, to see the formatted output. The hints must be in lowercase. Some example statements showing the available special SQL Worksheet hints are: SELECT /*ansiconsole*/ * FROM books; -- Best appearance for ANSI terminal display SELECT /*csv*/ * FROM books; -- Comma-separated values SELECT /*delimited*/ * FROM books; -- (same as csv) SELECT /*fixed*/ * FROM books; -- Fixed-width fields with trailing blanks SELECT /*html*/ * FROM books; -- Marked-up HTML table SELECT /*insert*/ * FROM books; -- SQL INSERT statements SELECT /*json*/ * FROM books; -- JSON object format SELECT /*loader*/ * FROM books; -- Pipe-delimited format suitable for SQL*Loader SELECT /*text*/ * FROM books; -- Plain text SELECT /*xml*/ * FROM books; -- Tagged XML The following example shows the output generated by the first statement ( "BOOK_ID","TITLE","AUTHOR_LAST_NAME","AUTHOR_FIRST_NAME","RATING" "A1111","Moby Dick","Melville","Herman",10 "A2222","Get Rich Really Fast","Scammer","Ima",1 "A3333","Finding Inner Peace","Blissford","Serenity", "A4444","Great Mystery Stories","Whodunit","Rodney",5 "A5555","Software Wizardry","Abugov","D.",10 1.8.11 Gauges: In the SQL Worksheet and User-Defined ReportsYou can use graphical gauges to display query results in the SQL Worksheet and in user-defined reports. In both cases, you need to specify the name of the value column for the gauge data, and minimum and maximum values on the gauge, and the values to be shown as low and high on the gauge (usually between the minimum and maximum values). In the SQL Worksheet, the required structure for the value to be selected is: 'SQLDEV:GAUGE: For example, to display the last name and the salary in gauge format, where the gauge shows from 1000 to 30000 with below 10000 as low and above 18000 as high, for employees with ID numbers less than a number to be specified, connect to the supplied HR schema and execute the following query: SELECT last_name, 'SQLDEV:GAUGE:1000:30000:10000:18000:' || salary FROM employees WHERE employee_id < :employee_id If you specify 104 as the bind variable value, the output appears as shown in the following figure: For a user-defined gauge report, the query must specify only the value column, the minimum and maximum values, and the low and high values, and optionally a WHERE clause. The required structure for the query (before any optional WHERE clause) is: SELECT For example, to create a report of salaries in gauge dial format, with the same values and WHERE clause as in the preceding query, right-click on User Defined Reports in the Reports navigator and select New Report. In the New Report dialog box, specify a report name; for Style, select Gauge; and enter the following on the subtabs:
Click Apply. Use the Reports navigator to view the newly created user-defined report. For Connection, specify one that connects to the HR sample schema. For the bind variable value, specify 104. 1.8.12 Entering OLAP DML Statements in the SQL WorksheetIf Oracle OLAP is available for the connection associated with a SQL Worksheet, you can enter OLAP
DML statements by prefixing a tilde (~) to the statement and clicking the Run Statement (for a selected individual statement) or Run Script (for all statements on the worksheet) icon. For example, to enter the ~show tod To see the output of OLAP DML statements, you must display the DBMS Output Pane (View > Dbms Output). For information about OLAP DML statements, see the Oracle OLAP DML Reference topic in the online help. 1.9 Using Snippets to Insert Code FragmentsSnippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. You can insert and edit snippets when you are using the SQL Worksheet or creating or editing a PL/SQL function or procedure. To display snippets, from the View menu, select Snippets. In the snippets window (on the right side), use the drop-down to select a group (such as Aggregate Functions or Character Functions). In most cases, the fragments in each group do not represent all available objects in that logical grouping, or all formats and options of each fragment shown. For complete and detailed information, see the Oracle Database documentation. To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the snippets window and drop it into the desired place in your code; then edit the syntax so that the SQL function is valid in the current context. To see a brief description of a SQL function in a tooltip, hold the pointer over the function name. For example, you could type SELECT and then drag CONCAT(char1, char2) from the Character Functions group. Then, edit the CONCAT function syntax and type the rest of the statement, such as in the following: SELECT CONCAT(title, ' is a book in the library.') FROM books; 1.9.1 User-Defined SnippetsYou can create and edit snippets. User-defined snippets are intended mainly to enable you to supplement the Oracle-supplied snippets, although you are also permitted to replace an Oracle-supplied snippet with your own version. When you create a user-defined snippet, you can add it to one of the Oracle-supplied snippet categories (such as Aggregate Functions) or to a category that you create. If you add a snippet to an Oracle-supplied category and if your snippet has the same name as an existing snippet, your snippet definition replaces the existing one. (If you later upgrade to a new version of SQL Developer and if you choose to preserve your old settings, your old user-defined snippets will replace any Oracle-supplied snippets of the same name in the new version of SQL Developer.) To create a snippet, do any of the following:
To edit an existing user-defined snippet, click the Edit User Snippets icon in the Snippets window. Information about user-defined snippets is stored in a file named UserSnippets.xml under the directory for user-specific information. 1.10 Finding Database ObjectsYou can find various types of objects (tables, columns, declarations within functions or procedures, and so on) associated with an Oracle database connection, and open editing panes to work with those objects. To open the Find Database Object pane, right-click a connection name in the Connections navigator and select Find DB Object. You can also click View, then Find DB Object. Select the desired database connection, the types of objects for which to search, and a search string. The following figure shows Find Database Objects pane with results from a search for all objects associated with a connection named In this example, the search finds nine objects across four object types. In the display of results, you can click any of the object names to bring up the object in an appropriate editor. Connection: Database connection to use for the search. Name: An object name or a string containing one or more wildcard characters. For example: All Schemas: Select one or more schemas, or all schemas, in which to perform the search. In this example, the blue solid square reflects the fact that the schema you selected is the schema to search; however, you click to select all schemas, or you can expand All Schemas and individually select schemas. Types: Type of object for which to restrict the search. You can search all columns, or all or individual identifier types, identifier usages, and source lines. All Dependencies: Extends object search to all dependent objects. For example, if a table is referenced by several views and if that table is found by a search, specifying All Dependencies will also find those dependent views. Click the Go icon to display objects that meet the specified criteria. To view or edit one of the objects (or the parent object that contains the specified object), double-click or right-click its name in the results display. 1.11 Using Recent ObjectsYou can easily access database objects that you have opened during the current SQL Developer session, even if you have closed them, by using the Recent Objects window. To display this window, click View, then Recent Objects. The Recent Objects window displays the names of database objects that you have opened, grouped by object type. You can double-click an object name to open it in the appropriate editor. You can right-click an object name to perform other operations relevant to that object type. For example, if you were viewing or editing the HR.EMPLOYEES table, then closed the table and disconnected from the HR schema, and if you then double-click EMPLOYEES in the Recent Objects window, the connection is reestablished and the table is opened for editing. 1.12 Using VersioningSQL Developer provides integrated support for the Subversion and Git versioning and source control systems, and you can add support for other such systems as extensions by clicking Help, then Check for Updates. Available extensions include Subversion and GIT. The SQL Developer documentation does not provide detailed information about the concepts and operations of such systems; it assumes that you know them or can read about them in the product documentation.
To access the versioning features of SQL Developer, use the Team menu. If you create any versioning repositories, you can use the hierarchical display in the Files navigator, which is marked by a folder icon. (If that navigator is not visible, click View, then Files.) You can also view a hierarchical display of repositories and their contents in the Versioning navigator. 1.12.1 About Subversion and SQL DeveloperBefore you can work with a Subversion repository through SQL Developer, you must create a connection to it. When you create a local Subversion repository, a connection to it is automatically created, and this can be seen in the Versioning navigator. You can subsequently edit the connection details. To connect to an existing Subversion repository (if no connection to that repository exists), use the Versioning navigator, as follows:
Existing files must be imported into the Subversion repository to bring them under version control. Files are then checked out from the Subversion repository to a local folder known as the "Subversion working copy". Files created in (or moved into) SQL Developer must be stored in the Subversion working copy. Files newly created within SQL Developer must be added to version control. Changed and new files are made available to other users by committing them to the SQL Developer repository. The Subversion working copy can be updated with the contents of the Subversion repository to incorporate changes made by other users. 1.12.2 Pending ChangesThe Pending Changes window is displayed if you request Pending Changes, or when you initiate an action that changes the local source control status of a file. This window shows files that have been added, modified or removed (locally or remotely), files whose content conflicts with other versions of the same file files that have not been added to source control files that are being watched, and files for which editors have been obtained. You can use this information to detect conflicts and to resolve them where possible. The Outgoing pane shows changes made locally, the Incoming pane shows changes made remotely, and the Candidates pane shows files that have been created locally but not yet added to source control. You can double-click file names to edit them, and you can use the context menu to perform available operations. 1.13 Using DBA Features in SQL DeveloperSQL Developer enables users with DBA (database administrator) privileges to view (and in some cases edit) certain information relevant to DBAs and, in some cases, to perform DBA operations. To perform DBA operations, use the DBA navigator, which is similar to the Connections navigator in that it has nodes for all defined database connections. (If the DBA navigator is not visible, select View, then DBA.) If no connections appear in the DBA navigator display, you must add at least one connection. To add a connection to the DBA navigator, click the plus (+) icon or right-click Connections and select Add Connection; then select a connection. (You can also specify a new connection by clicking the plus (+) icon in the Select Connection dialog box.) You should add only connections for which the associated database user has DBA privileges (or at least privileges for the desired DBA navigator operations) on the specified database. If you attempt to perform a DBA navigator operation for which the database connection user does not have sufficient privileges, you will receive an error. The following options are available from the DBA navigator hierarchy: Connections To perform limited database management operations, you can right-click the connection name in the DBA navigator display and select Manage Database. For example, if a listener is running with a static listener configured for the database, you can start and stop the database, force database startup, and restrict access to the database. You can right-click an item (node) at any level in the DBA navigator hierarchy to display a context menu with commands relevant to that item. Typical commands include the following:
The pane with information about an item opened from the DBA navigator typically contains icons and other controls for the following:
With information displays that are in grid form:
For information that is read-only (not editable) using the DBA navigator, you may have other options within SQL Developer to specify relevant values. For example, to change the value of any globalization support (NLS) parameter, you can use the Database: NLS preferences pane to change the value for use with all SQL Developer connections (current and future), or you can use the ALTER SESSION statement in the SQL Worksheet window to change the value for the current connection only. Detailed explanations of various DBA options, including usage and reference information, are available in appropriate manuals in the Oracle Database Documentation Library on the Oracle Technology Network (OTN). 1.13.1 Container Database (CDB)(Available only for Release 12c connections.) Includes options for managing a multitenant container database (CDB) and the pluggable databases (PDBs) within it. You can right-click to perform the following operations:
1.13.2 Database ConfigurationIncludes the following options related to database configuration management. 1.13.2.1 Initialization ParametersFor each database initialization parameter, displays the name, value (current value), default value, description, and other information. You can modify the values of some parameters. 1.13.2.2 Automatic Undo ManagementAutomatic undo management is a mode of the database in which undo data is stored in a dedicated undo tablespace. The only undo management that you must perform is the creation of the undo tablespace; all other undo management is performed automatically. The Automatic Undo Management option displays information about automatic undo management and any recommendations relating to its use. You can change the Retention value. 1.13.2.3 Current Database PropertiesFor each current database initialization parameter, displays the name, value (current value), and description. 1.13.2.4 Restore PointsDisplays restore points that can be used for recovery; lets you create and delete restore points. A restore point is a name associated with a timestamp or an SCN of the database. A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication. 1.13.2.5 View Database Feature UsageDisplays database features and the number of detected usages for each. 1.13.3 Database StatusIncludes options for displaying status information about the database. 1.13.3.1 StatusIncludes tabs for displaying information about the Database Status, Oracle Host, Oracle Home, and TNS Listener. 1.13.3.2 DB InstanceDisplays the database Instance Viewer. 1.13.3.3 Instance ViewerThe database instance viewer enables a DBA user to see a graphical representation of information about the instance associated with a connection. The types of information displayed include:
You can interact with the instance viewer display, including:
You can control the amount of history shown in graphs that include a time axis by using the Database: Instance Viewer preferences. 1.13.4 Data GuardData Guard is applicable only for Oracle Database 12c or later connections, and requires the Data Guard Broker. It displays a console for the Oracle Data Guard feature, which is described in detail in Oracle Data Guard Concepts and Administration. When Oracle Data Guard is available for a particular database connection, the Data Guard node is automatically displayed for the connection in the DBA panel. Console The Data Gguard console enables a DBA user to see a graphical tree-like representation of the primary and standby databases associated with a connection. You can drill down for more detail by hovering over a graphical element to show a dark blue border and then double-clicking in that element. You can expand or contract a database by clicking the round icon next to it in the console. You can right-click a database to perform the following operations:
1.13.5 Data PumpIncludes options for using the Oracle Data Pump Export and Import utilities, which are described in detail in Oracle Database Utilities. 1.13.5.1 Export JobsDisplays any Data Pump Export jobs. You can right-click and select Data Pump Export Wizard to create a Data Pump Export job. 1.13.5.2 Import JobsDisplays any Data Pump Import jobs. You can right-click and select Data Pump Import Wizard to create a Data Pump Import job. 1.13.6 Oracle ShardingIncludes options for displaying status information about a sharded database. Available only for Oracle Database 12c and later connections. Before using these options, you should be familiar with the Oracle Sharding and Oracle Data Guard concepts. These features are described in detail in the Oracle Database Administrator’s Guide and Oracle Data Guard Concepts and Administration. When a shard catalog (an Oracle database that supports shard deployment) is available for a particular database connection, the Oracle Sharding node is automatically displayed for the connection in the DBA panel. Sharded DB Viewer The sharded database viewer enables a DBA user to see a graphical representation of information about the sharded database associated with a connection. The viewer displays the regions, shard directors, shard spaces, shard groups, broker sets, and shards (or individual databases) for a sharded database. The red and gray shards signify primary and standby databases respectively. You can drill down for more detail by hovering over a graphical element to show a dark blue border and then double-clicking in that element. You can right-click a shard to perform the following operations:
Regions, Shard Directors, Shard Spaces, Shard Groups, Shards Right-click a node, such as Regions, and click Open for detailed information. Tablespace Sets The Tablespace Sets node is used to create a tablespace set, which is used in a sharded database as a logical storage unit for one or more sharded tables. A tablespace set consists of multiple tablespaces distributed across shards in a shard space. All tablespaces in a tablespace set share the same attributes. To create a tablespace set for a sharded database, right-click Tablespaces, and then click Create New. The Create Tablespace Set dialog is displayed. Properties Tablespace Set Name: Name for the tablespace set. Shard Space: Shard space that the tablespace set belongs to. For a description of the remaining fields, see Create/Edit Tablespace. In the SQL tab, ensure that the DDL that is generated includes the Enable Shard DDL mode, which indicates that the DDL is propagated to all the DDL shards in the database. 1.13.7 PerformanceIncludes options to create objects and view reports related to the use of database statistics. Oracle Database automatically persists the cumulative and delta values for most of the statistics at all levels (except the session level) in the Automatic Workload Repository (AWR). This process is repeated on a regular time period and the results are captured in an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period. A statistical baseline is a collection of statistic rates usually taken over a time period when the system is performing well at an optimal level. Use statistical baselines to diagnose performance problems by comparing statistics captured in a baseline to those captured during a period of poor performance. This enables you to identify specific statistics that may have increased significantly and could be the cause of the problem. AWR supports the capture of baseline data by enabling you to specify and preserve a pair or range of AWR snapshots as a baseline. Note: Most
Performance operations are performed internally by SQL Developer through calls to subprograms of the 1.13.7.1 SnapshotsSnapshots are sets of historical data for specific time periods that are used for performance comparisons by Automatic Database Diagnostic Monitor (ADDM). By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in AWR for 8 days. You can also manually create snapshots or change the snapshot retention period, but it is usually not necessary. AWR compares the difference between snapshots to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that must be captured over time. After the snapshots are created, ADDM analyzes the data captured in the snapshots to perform its performance analysis. 1.13.7.2 BaselinesA baseline is a set of snapshots from a specific time period that is preserved for comparison with other snapshots when a performance problem occurs. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. 1.13.7.3 Baseline TemplatesBaseline templates enable you to create baselines for a contiguous time period in the future. There are two types of baseline templates: Single (for a single contiguous time period) and Repeating (based on a repeating time schedule; useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis). 1.13.7.4 Automatic Database Diagnostic Monitor (ADDM)ADDM is an advisor that analyzes Automatic Workload Repository data on a regular basis, diagnoses the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system. 1.13.7.5 ASH (Active Session History) Report ViewerASH enables you to examine and perform detailed analysis on sampled activity of active sessions. To facilitate the identification of transient performance problems, Oracle Database samples active sessions every second. By capturing only active sessions, a manageable set of data is represented with its size being directly related to the work being performed, rather than the number of sessions allowed on the system. To view the report, specify the desired options (From Time, To Time, Filters) and click the Generate Report icon. 1.13.7.6 AWR (Automatic Workload Repository)An AWR report shows data captured between two snapshots (or two points in time). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that can be used to navigate quickly between sections. The following types of AWR reports are available:
To view a report, specify the desired options and click the Generate Report icon. 1.13.8 RMAN Backup/RecoveryIncludes options related to database backup and recovery. The options use the Oracle Database Recovery Manager (RMAN) feature, which is described in detail in Oracle Database Backup and Recovery User's Guide. You should be familiar with RMAN concepts and techniques before using these options. In any dialog box or wizard for RMAN operations, you can click the SQL or Summary tab to see the statements that will be used to implement the specified options.
1.13.8.1 Backup JobsDisplays the backup jobs that have been previously run; lets you create and run new backup. (Note that backup jobs are distinct from action jobs.) 1.13.8.2 Backup SetsDisplays the backup sets that have been created by previous backup jobs and that can be used for recovery. 1.13.8.3 Image CopiesDisplays the image copies that have been created by previous backup jobs and that can be used for recovery. 1.13.8.4 RMAN SettingsDisplays settings for backup and recovery. (These settings are stored in the server and are used and managed by RMAN.) 1.13.8.5 Scheduled RMAN ActionsFor Oracle Database 11.1 and later connections: Displays DBMS_SCHEDULER jobs that have been used to execute RMAN scripts; lets you view log files. 1.13.8.6 Using Action JobsAction jobs are applicable to connections to an Oracle Database Release 11.1 or later database. Most backup and recovery actions involve RMAN scripts. The action jobs dialog boxes let you save the generated RMAN to a disk; you can then copy the script to the server system and run the script there. Before you can execute the script for an action job, you must create a DBMS_SCHEDULER credential by running a procedure in the following format: BEGIN DBMS_SCHEDULER.CREATE_CREDENTIAL( username => 'user-name', password => 'password', database_role => NULL, windows_domain => NULL, comments => NULL, credential_name => 'credential-name' ); END; / Then, run the Action Jobs, Configure action. This action places some scripts in the server-side database home (in
After you perform the configuration, the Run Scheduler Job action of the Script Processing control becomes available in the RMAN dialog boxes, and you can click Apply to cause the RMAN script to be executed in the server using a DBMS_SCHEDULER job. After an RMAN job has been run, you can view the log file containing the output from RMAN by using the Action Jobs, View Latest Log action. This lets you check for any errors that may have occurred during the running of the RMAN script. Some RMAN jobs involve performing a database restart. Examples are setting the archive log mode and some whole database backup and restore operations. In such cases, after you click Apply you are asked to confirm that you want to proceed; and if you do proceed, the job is queued (with no waiting for the completion). Because of the restart, the SQL Developer connection must be disconnected and then connected again after the database is restarted. After the reconnection, examine the log file to see whether the job completed successfully. 1.13.9 Resource ManagerIncludes the following options related to database resource management. 1.13.9.1 Consumer Group MappingsA consumer group mapping specifies mapping rules that enable the Resource Manager to automatically assign each session to a consumer group upon session startup, based upon session attributes. The Consumer Group Mappings option displays, for each attribute, its priority, value, and associated consumer group. 1.13.9.2 Consumer GroupsA resource consumer group is a group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions. The Consumer Groups option displays, for each consumer group, its description and whether it is mandatory. 1.13.9.3 PlansA resource plan is a container for directives that specify how resources are allocated to resource consumer group; you specify how the database allocates resources by activating a specific resource plan. The Plans option displays, for each plan, its description and if its status is Active. 1.13.9.4 SettingsLists any active resource plans. 1.13.9.5 StatisticsLists various resource-related statistics (if applicable). 1.13.10 SQL Translator FrameWork(Available only for Release 12c connections.) Includes options for creating and managing SQL translation profiles and SQL translators. 1.13.10.1 SQL Translator ProfilesA SQL translation profile is a database schema object that directs how non-Oracle SQL statements are translated into Oracle SQL dialects. 1.13.10.2 SQL TranslatorsA SQL translator is a stored procedure that translates non-Oracle SQL statements into Oracle SQL dialects automatically. After a SQL translation profile is created, users can register a SQL translator with the translation profile to translate the non-Oracle SQL statements. 1.13.11 SchedulerIncludes the following options related to Scheduling Jobs Using SQL Developer. (The objects under Scheduler in the DBA navigator are for objects that owned by the SYS user and that can be created and modified only by users with DBA privileges. Other objects are listed under Scheduler for users in the Connections navigator.) 1.13.11.1 Global AttributesThe Global Attributes display lets you view and edit attributes such as the default time zone, the email sender and server, event expiry time, log history retention, and maximum job slave processes. 1.13.11.2 Job ClassesThe Job Classes display lets you view and edit information about job classes. The information for each job class includes the job class name, logging level, log history, resource consumer group, service, and comments. 1.13.11.3 External DestinationsThe External Destinations display lets you view information about external destinations for jobs. 1.13.12 SecurityIncludes the following options related to database security management. For profiles, roles, and users, you can perform relevant operations, such as creating new objects of that type and editing and dropping existing objects. 1.13.12.1 Audit SettingsThe Audit Settings display includes the audit trail setting, whether SYS user operations are audited, and the directory or folder for the audit file. 1.13.12.2 ProfilesA profile is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. The Profiles option displays any limits on activities and resource usage for each profile. 1.13.12.3 RolesA role is a set of privileges that can be granted to users or to other roles; you can use roles to administer database privileges. The Roles option displays the roles and their authentication settings. 1.13.12.4 UsersA database user is an account through which you can connect to the database. The Users option displays status and usage information about each database user. 1.13.13 StorageIncludes the following options related to database storage management. 1.13.13.1 Archive LogsA database user is an account through which you can connect to the database. The Users option displays status and usage information about each database user. 1.13.13.2 Control FilesA control file is a binary file that records the physical structure of a database and contains the names and locations of redo log files, the time stamp of the database creation, the current log sequence number, checkpoint information, and so on. The Control Files option displays, for each control file, its status, file name, and file directory. 1.13.13.3 DatafilesA data file is a physical file on disk that was created by Oracle Database and contains the data for a database. The data files can be located either in an operating system file system or Oracle ASM disk group. The Datafiles option displays, for each data file, its file name, tablespace, status, and other information. 1.13.13.4 Redo Log GroupsA redo log group contains one or more members: each online redo log member (which corresponds to an online redo log file) belongs to a redo log group. The contents of all members of a redo log group are identical.The Redo Log Groups option displays, for each redo log group, its status, number of members, and other information. 1.13.13.5 Rollback SegmentsA rollback segment records the before-images of changes to the database. The Rollback Segments option displays, for each rollback segment, its name, status, tablespace, and other information. 1.13.13.6 TablespacesA tablespace is a database storage unit that groups related logical structures together. The database data files are stored in tablespaces. The Tablespaces option displays, for each tablespace, its name; megabytes allocated, free, and used; and other information. 1.13.13.7 Temporary Tablespace GroupsA temporary tablespace group is a tablespace group that is assigned as the default temporary tablespace for the database. (A tablespace group enables a database user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions.) The Temporary Tablespace Groups option displays, for each tablespace group, its name, the number of tablespaces in the group, the total size of the tablespaces, and whether the group is the default temporary tablespace. 1.13.14 TuningProvides an option to monitor the database performance in real time. 1.13.14.1 Real Time SQL MonitorThe real time SQL monitor (Oracle Database Release 11.1 and later only) requires the Oracle Tuning Pack. This feature enables you to monitor the performance of SQL statements while they are executing. It displays information about any query currently executing and queries that are done executing for a selected connection. To see detailed information about a query, click the entry. The information is especially useful for real-time monitoring of long-running SQL statements. Cursor statistics (such as CPU times and IO times) and execution plan statistics (such as number of output rows, memory, and temporary space used) are updated close to real-time during statement execution. (Internally, this feature calls the DBMS_SQLTUNE.REPORT_SQL_MONITOR subprogram.) 1.14 Scheduling Jobs Using SQL DeveloperSQL Developer provides a graphical interface for using the DBMS_SCHEDULER PL/SQL package to work with Oracle Scheduler objects. To use the SQL Developer scheduling features, you must first understand the concepts and essential tasks for job scheduling, which are explained in the chapters about Oracle Scheduler concepts and scheduling jobs in Oracle Database Administrator's Guide. The Scheduler node for a connection appears in the Connections navigator and in the DBA navigator. The DBA navigator displays Scheduler objects owned by the SYS user and requiring DBA privileges for creating and modifying; the Connections navigator displays other Scheduler objects that may require specific privileges for creating and modifying. The Oracle Scheduler objects are grouped under object types that include:
You can right-click an item (node) at any level in the Scheduler hierarchy to display a context menu with commands relevant to that item. Typical commands include the following:
If you right-click the top-level Scheduler node, you can create an object of a specified type, and you can display the Scheduler Design Editor. The pane with information about an item opened from the Scheduler hierarchy typically contains icons and other controls for the following:
With information displays that are in grid form, you can often use the context (right-click) menu on a header or a data cell to perform operations like those for the table data grid. For example, right-clicking on a header lets you select options like Auto-fit, Columns, Sort, Delete Persisted Settings (such as any sort order specifications), and Filter Column; right-clicking any data cell lets you select options like Save Grid as Report, Single-Record View, Count Rows, Find/Highlight (find and optionally highlight values in the grid), and Export (unload data). Several Scheduler reports are also available. 1.14.1 Scheduler Design EditorThe scheduler design editor is a graphical interface for scheduling jobs using SQL Developer. To create, edit, or delete any Oracle Scheduler objects in the editor, you must be in write mode. You can use the editor toolbar toggle button or the right-click command to Switch to write mode and Switch to read-only mode. To copy existing objects to the schedule design editor, drag the objects from the Connections navigator onto the editing canvas. To create a new object, right-click in the canvas and select the context menu command for creating a new object of the desired type. To open an object, double-click it or right-click it and select Open. To delete an object, right-click it and select Drop. You will be prompted to confirm that you want to drop the object. To remove the object from the editing canvas without deleting the object itself, right-click it and select Clear. To enable a disabled object, right-click it and select Enable; to disable an enabled object, right-click it and select Disable. You can click, drag, and release to set a relationship between compatible objects. For example, to set a schedule for a job, you can drag the schedule onto the job, which causes a line to be drawn between the objects. You can unset (remove) that relationship by right-clicking the line and selecting the appropriate command (such as Unset Schedule), and then clicking OK when you are prompted to confirm the action. 1.15 Using the CartThe Cart is a convenient tool for collecting Oracle database objects from one or more database connections, and deploying, exporting, comparing, or copying those objects. You can put objects into one or more carts, each with its own tab. When the Cart window is opened, it contains an empty cart, although you can create new carts and open previously saved carts in new or existing cart tabs. For each of the Cart database utilities (Export, Diff [compare], Copy), you can create, save, and open utility-specific configuration settings. Objects in the Cart are not automatically synchronized with database activity; to update the contents of the cart with the current state of the database, click the Refresh icon. If an object does not exist after a refresh, the object is disabled in the Cart and is underlined to indicate the error. You can put objects into a cart tab in several ways:
Before you use the Cart, ensure that the Database: Cart and Database: Cart: Cart Deploy preferences reflect what you want. To display the Cart window, click View, then Cart. The following figure shows that window after some objects have been added in the default cart tab. New Cart icon: Opens a new empty cart tab. For this and any other card tab, you can right-click the tab name and select from the following:
Open Cart icon: Displays a dialog box where you specify:
Save Cart, Save As Cart, Save All Carts icons (active if one of more objects are selected): Displays a dialog box for saving the current cart or all carts to an XML file that you can later open. Specify:
For Save All Carts, the dialog box is displayed repeatedly, once for each currently open cart tab. Refresh Cart (active if one of more objects are selected): Refreshes the current cart tab; validates that what is in the cart is actually in the database. Deploy Cloud (Deploy to the Oracle Cloud; active if one or more objects are included in the selected cart): After you have added the objects to be deployed and specified any desired options, click this icon to display the Deploy Objects to Cloud dialog box, where you can specify further options and deploy the objects to the Oracle Cloud. You can deploy to a Database Schema Service connection or a Cloud Wallet connection that has the DBMS_CLOUD package installed. Cloud storage files can only be deployed to Cloud Wallet connections. Export (Create a deployment file; active if one or more objects are included in the selected cart): After you have added the objects to be deployed and specified any desired options, click this icon to display the Export Objects dialog box, where you can specify further options and generate the deployment scripts and optionally a .zip file that contains them. Database Diff: Displays the Diff Objects dialog box, for comparing the selected objects with the objects in another currently open cart tab or a database connection that has access to the destination objects to be compared. Copy: Displays the Copy Objects dialog box, for copying the selected objects to a database connection. Selected Objects: Contains objects that have been selected for inclusion in each open cart. Delete Row icon (red X): Lets you delete selected objects from the Selected Objects area. Include: Lets you select and deselect all objects or selected objects for inclusion in a Cart operation. Type: Type of database object. Owner: Owner of the database object. Name: Name of the database object. DDL: Lets you include or exclude the DDL (Data Definition Language) statements for a Cart operation. Data: For tables and views, lets you include or exclude the associated data for a Cart operation. Where: Optional WHERE clause (without the WHERE keyword) to filter the data rows from a table or view. Scripts (applies to Deploy to Cloud, Export, and Copy): Lets you optionally include one or more .sql scripts to be executed at specified locations in the generated master script. For each object, you can click the pencil icon in the Scripts cell; this displays a dialog box to specify the file and character set encoding for any combination of the following:
The default location for these scripts is the Open Script Directory preference under the Database: Utilities: Cart user preferences. Connection: Connection from which the object was added. You can click the pencil icon in a Connection cell to display the Map Connection dialog box, where you can change the Connection value for one or more objects in the currently selected cart. Arrow buttons: Let you move objects up or down in the order of Selected Objects. This affects the order in which objects are created in the generated scripts. 1.15.1 Adding Data Files to the CartYou can add local and cloud storage data files to the Cart to deploy to Oracle Cloud. To add local files to the cart, use the Files navigator in the left pane or click the Scripts drop-down and select Add Data File. Cloud storage files can only be added by using the Add Data File option. Add a Local File using Files Navigator This part is not applicable for local or cloud storage files that are added to the cart using Add Data File. Select the file (.csv, .dsv, .tsv) using Files navigator in the left pane and drag and drop into the cart, or drag and drop from any directory in the local computer into the cart. The Choose Configuration File Option dialog box is displayed. New Configuration File: Opens the Data Import Wizard without a configuration file. When you complete the steps in the wizard, you are prompted to save the configuration. You must have a saved configuration file to deploy the data file to the Cloud. Select Configuration File: Select the default configuration file if available, or select a configuration file. You can use the same configuration file for more than one data file dropped into the cart. The data file in the cart is used for deployment even if the configuration file is saved with a different data file. When you select an existing configuration file, it is validated to ensure that the selected data file is compatible with the file specified in the configuration file, and that a metadata table, if used, still exists and is compatible with the properties that are saved in the configuration file. You can select a configuration file that was created with a data file different from the file being dropped, but the configuration and data files should be compatible. Any differences are reported. Review the differences and run through the Data Import Wizard to ensure that the properties are valid. Description of Data File Attributes in Cart When you finish the Data Import Wizard (see Choose Configuration File Option) for a local or cloud storage file, a row for the data file is added to the cart. The columns in the cart are: Include: Select to indicate that the file should be included for any cart operations. Type: Data File is displayed by default. Owner: This field is not applicable for data files. Name: Displays the table name as specified in the wizard. You can edit this field to add a new table name, which will override the table name specified in the wizard. The new name in the cart does not affect the name of the table in the configuration file that is used to capture metadata. Therefore, you can load the file into different tables using the same configuration file. DDL: Select to include DDL statements for this file. Data: Select to include the associated data in this file. Scripts: Enables you to include one or more .sql scripts to be executed at specified locations in the generated master script. Data Source: Identifies the data file and overrides the file indicated in the configuration file. Select the row and hover to see the complete data file name. Click the pencil icon in the cell to open the Data Import Wizard. Configuration: Identifies the configuration file associated with this data file. Select the row and hover to see the complete configuration file name. Click the pencil icon in the cell to open the Data Import Wizard. The Deploy Cloud operation is the only functionality available for carts containing data file rows. Database operations such as Export, Diff, and Copy are not available. 1.15.2 Cart Versus Database Export WizardThe Cart and the Database Export (Unload Database Objects and Data) wizard enable you to perform the same basic operations. A suggested guideline for choosing which one to use is repeatability of the deployment/export operation:
1.16 Spatial Support in SQL DeveloperSQL Developer lets you work with Oracle Spatial and Graph data and metadata. Oracle Spatial and Graph (referred to as Spatial and Graph) facilitates the storage, retrieval, update, and query of collections of Spatial and Graph features in an Oracle database. Spatial and Graph includes a schema (MDSYS), a data type (SDO_GEOMETRY), a spatial indexing mechanism and index type, and operators, functions, and procedures for performing area-of-interest queries, spatial join queries, and other spatial analysis operations. SQL Developer support for Spatial and Graph includes the following:
1.16.1 Context Menu Operations on Spatial Data and MetadataIn the Connections navigator display, the context menu (right-click) for a table includes a Spatial submenu, which includes the following commands:
1.16.2 Map Visualization of Spatial DataYou can use the Map View window for displaying spatial geometry objects that are returned by a query. The SQL Developer Map View display lacks the rich features of a visualizing tool such as Oracle MapViewer, but it provides a quick and flexible way to see simple representations of spatial data. To display the Map View, click View, then Map View. You can resize and reposition the Map View window so that it adequately displays the geometries you want to see. The
following figure show the Map View window display of a query that returns all geometries for counties in the U.S. state of South Carolina. On the left is a map display pane with some icons above it. On the right is a Query List pane with one query (named You can visualize geometries in the Map View window by creating and executing a SQL query, or by selecting a geometry in a table data grid cell and using the context menu:
1.16.2.1 Visualizing Geometries by Creating and Executing a QueryTo visualize geometries by creating and executing a SQL query, follow these steps:
To execute the query and display the map in the left pane of the window, select (check mark) the query and click the Execute Checked Queries icon above Query List. To modify a query, select (check mark) it and click the Edit Query (pencil) icon above Query List. To delete a query from the list, select (check mark) it and click the Remove Query (X) icon above Query List. In the map display pane on the left, you can click icons above the pane to control aspects of the display:
1.16.2.2 Visualizing Geometries from the Table Data GridTo visualize one or more geometries from the table data grid, follow these steps:
1.17 Change Manager Support in SQL DeveloperA change plan is an Oracle Change Manager container for schema object changes. Using SQL Developer, you can create change plans and populate them change information. Later, a database administrator deploys the change plan to one or more destination databases using Oracle Enterprise Manager. A change plan can contain multiple change items, each of which describes changes to be made to a single schema object. There are three types of change item, reflecting three basic actions to be carried out at the destination database:
To deploy a change plan to a destination database, the DBA uses Enterprise Manager to create a Change Manager Schema Synchronization from the contents of the change plan. During this process, Change Manager detects and reports conflicts between the change plan and the database. (For example, the database may already contain an object that the change plan is trying to add.) The generated script carries out operations in the correct order. The Change Management Parameters pane contains user preferences that affect Change Manager support in SQL Developer, especially how objects are compared to determine if a change has occurred and how extensive the DDL statements are when the script for deploying changes is generated. See the following subtopics for more information related to support for Change Manager:
1.17.1 Change Plan Administrators and DevelopersChange plans are stored in the Enterprise Manager repository. An Enterprise Manager super administrator has unlimited access to all change plans. However, in most cases, it is preferable to create Enterprise Manager administrators with specific Change Plan privileges and limited access to other Enterprise Manager facilities. There are two general types of Change Plan privilege:
To create a Change Plan Administrator or one or more Change Plan Developers, you must use Enterprise Manager. 1.17.2 Developing Change Plans with SQL DeveloperThe SQL Developer user interface support for Change Manager includes the Change Management window and the Change Management Parameters user preferences. To display the Change Management window, click View, then Change Management. The Change Management window is displayed within the SQL Developer main window. At the top of the Change Management window are icons for the following operations:
Connection: Database connection for the Change Management repository. Change Plan: A change plan within the selected repository. To the right of the Change Plan are icons for the following operations: Add Plan (+) to create a new change plan to the repository, Remove Plan (X) to delete the selected plan from the repository, and Refresh to refresh the window with the current contents of the repository. Change Items: A list of change items for the selected change plan, with the following information about each item: Action, Database Object Type, Object Name, Schema, SQL Developer Connection, Enterprise Manager Connection, Global Name, Description. To delete a change item, select its row and click the Remove Item(s) (X) icon. To update the change items display to reflect the contents of the repository, click the Refresh icon. Related Topics
1.17.2.1 Creating a Repository ConnectionTo store and retrieve change plans, you must create a SQL Developer database connection to the Enterprise Manager repository database. This connection typically logs in as one of the repository database users created as a Change Plan administrator or developer. Create the database connection specifying the user name and password of a suitably privileged user, and the connection details for the schema Enterprise Manager repository. 1.17.2.2 Creating and Deleting Change PlansTo create a change plan:
To delete a change plan.
1.17.2.3 Adding and Updating Change ItemsTo add a change item to a change plan or to update (refresh) the change items in a change plan, you must be connected to the Enterprise repository as a user that has Edit privileges on the plan (or that has the Manage Change Plans resource privilege). In the Change Management window, select the relevant change plan from the Change Plan list. The current contents of the plan (if any) are listed in the Change Items display. To add a change item to the selected change plan, drag a database object from the Connections navigator (for example, a table named EMPLOYEES from a connection named HR) into the Change Items pane. In the dialog box, specify the type of change item to be created: Add Change Item, Drop Change Item, or Modify Change Item. 1.17.2.4 Using Change Plans to Capture Change in SQL DeveloperThere are two basic approaches to capturing changes in change plans: single database or multiple databases. The approach chosen may depend on the complexity of the work and the number of developers involved.
1.17.3 Change Manager Actions to Perform with Enterprise ManagerYou must use Enterprise Manager to perform any of the following operations related to change plans:
For information about performing these operations, see the Enterprise Manager help and documentation. 1.17.3.1 Creating Change Plans from Change Manager Schema ComparisonsYou can create change plans in Enterprise Manager and populate them from Change Manager schema comparisons. On the Databases page, select Schema Change Plans, then Create. Enter the name of the new change plan and click OK. In the resulting Change Plan page, click Create from Comparison. Select a version of a Schema Comparison, and a Conversion Assignment as follows:
Based on your selection, Change Manager populates the change clan with Add, Drop, and Modify change items that, if applied to the Change From objects, will convert them into the Change To objects. 1.17.3.2 Deploying Change PlansTo deploy change plans, Change Manager uses the script generation capability contained in its Schema Synchronizations application. You deploy a change plan by creating a synchronization that applies the changes in the change plan to a database that you select. To reduce the chance of incompatible change conflicts, the objects in the database should be identical or similar to the initial objects from which the change plan's change items were created. To deploy a change clan to a database, select the plan on the Change Plans page, and then click Create Synchronization from Change Plan. This takes you to the first page of the Create Schema Synchronization wizard. The process of creating a synchronization from a Change Plan is similar to creating a synchronization from scratch. However, in the first phase of synchronization processing, rather than comparing the source database (or baseline version) to the destination database, Change Manager applies the changes contained in the change plan to the destination database definitions (not to the actual objects). The result of this operation provides the basis for script generation and execution, the second and third phases of schema synchronization processing. Conflicts During change plan processing, Change Manager may detect conflicts between change items and the destination database. In general, a conflict means that the state of the destination database is not what was expected. Examples of conflicts include:
After changes have been applied to the destination definitions, Change Manager reports the results of the operations, including any conflicts encountered. Change Plans and Schema Synchronizations: Comparison Schema change plans and schema synchronizations are similar in some ways, and use some of the same underlying technology. However, they have different purposes and uses:
In other words, schema synchronization picks up any changes that have been made to the destination object or schema and makes those changes at the destination. Change plan deployment, however, makes only those changes specified in the change items. With schema synchronizations, there is no chance of conflict because the source definition always overrides the destination definition. With change plans, however, there is a possibility of conflict, because the specified changes might not be applicable at a destination database or might conflict with other changes. 1.18 Apache Hadoop Connectors Support in SQL DeveloperSQL Developer includes support for using connectors for Apache Hadoop to:
To use the Apache Hadoop connectors, you must do the following in SQL Developer:
The features require the relevant product to be installed in the Apache Hadoop cluster and Oracle Database. The features are supported for any Hadoop cluster and Oracle Database supported by the products that they use. The features require JDBC drivers for Apache Hive. To add the JDBC drivers, click Tools > Preferences > Database > Third Party JDBC drivers. 1.18.1 Copy to Hadoop: Overview and RequirementsCopy to Hadoop is a feature of Oracle Big Data SQL. SQL Developer makes it easy to identify and copy data from an Oracle Database table to the Apache Hadoop Distributed File System (HDFS) and create an Apache Hive external table over that data. The data that is copied can be stored as Oracle Data Pump files, Parquet files, or Optimized Row Columnar (ORC) format files. When stored as Oracle Data Pump files the data is retained as Oracle data types, providing an exact copy of the data in the database without any errors due to data type conversions. The data that is copied can be queried by Hive (and Hadoop technologies that access data through Hive), and by Oracle Big Data SQL. Using SQL Developer, you can copy data and create a new Hive table, or append data to an existing Hive external table that was created by Copy to Hadoop. Copy to Hadoop is supported with:
You can initiate Copy to Hadoop in SQL Developer by right-clicking the Tables icon under any Hive schema. You can initiate Append to an Existing Hive External Table by right-clicking the icon for that Hive table. Verification of Copy to Hadoop Installation Copy to Hadoop is installed as part of Oracle Big Data SQL installation. Using SQL Developer with Copy to Hadoop requires the following, which are created during Oracle Big Data SQL installation:
Check your Big Data SQL installation to verify that these directory objects and file exist. Setup Required to Use SQL Developer with Copy to Hadoop Using SQL Developer to run a Copy to Hadoop job with the Direct Copy method requires an SSH connection to a Hadoop client, a Hadoop edge node, or a node in the Hadoop cluster. The SSH connection is selected in the wizard. The file #!/bin/bash export CP2HADOOP_HOME= Additional setup steps are required on the Hadoop client, the Hadoop edge node, or the node in the Hadoop cluster:
(Note: Additionally, create the HDFS directory 1.18.2 Oracle Loader for Hadoop: Overview and RequirementsOracle Loader for Hadoop is a tool for high-speed loading of data from Hadoop into a table in Oracle Database. It loads data in parallel and can use direct path load when the database table is partitioned. Oracle Loader for Hadoop runs as a MapReduce job. It partitions and converts the data into Oracle binary format in Hadoop, offloading the work to the cluster to minimize use of database CPU during the load. Oracle Loader for Hadoop loads from a variety of formats. It can load data from text files, compressed files, sequence files, Hive tables, and data that can be accessed via a Hive table such as JSON, Parquet, ORC, and HBase. Full documentation is available in the "Oracle Loader for Hadoop" chapter in the Big Data Connectors User's Guide. You can initiate an Oracle Loader for Hadoop job by right-clicking on a database table icon in an Oracle Database connection. Note that the connection must be a service name connection when using direct path load. A SID connection will raise errors. Using SQL Developer to run an Oracle Loader for Hadoop job requires an SSH connection to a Hadoop client, a Hadoop edge node, or a node in the Hadoop cluster. The file #!/bin/bash export HIVE_HOME=/usr/lib/hive export HIVE_CONF_DIR=${HIVE_HOME}/conf export OLH_HOME=/u01/connectors/olh export HADOOP_CLASSPATH=${OLH_HOME}/jlib/*:${HIVE_HOME}/lib/*:${HIVE_CONF_DIR} export WALLET_LOCATION=/home/oracle/oracle_wallet export TNS_ADMIN=${WALLET_LOCATION} export OLH_LIB_JARS=${HIVE_HOME}/lib/hive-exec.jar,${HIVE_HOME}/lib/libfb303-0.9.2.jar,${HIVE_HOME}/lib/hive-metastore.jar export CLUSTER_WALLET_LOCATION=${WALLET_LOCATION} export CLUSTER_TNS_ADMIN=${TNS_ADMIN} 1.18.3 Oracle SQL Connector for HDFS: Overview and RequirementsOracle SQL Connector for HDFS can be used to:
Oracle SQL Connector for HDFS supports
Oracle SQL Connector for HDFS creates an external table over delimited text files or a Hive table over delimited text files. This external table can be used to load or query in-place. (For more information, see the Big Data Connectors User's Guide.) You can start Oracle SQL Connector for HDFS by right-clicking the Tables icon of an Oracle Database connection. Using SQL Developer with Oracle SQL Connector for HDFS requires an SSH connection to a Hadoop client, a Hadoop edge node, or a node in the Hadoop cluster. The file #!/bin/bash export HIVE_HOME=/usr/lib/hive export HIVE_CONF_DIR=${HIVE_HOME}/conf export OSCH_HOME=/u01/connectors/osch export HADOOP_CLASSPATH=${OSCH_HOME}/jlib/*:${HIVE_HOME}/lib/*:${HIVE_CONF_DIR} export WALLET_LOCATION=/home/oracle/oracle_wallet export TNS_ADMIN=${WALLET_LOCATION} export CLUSTER_WALLET_LOCATION=${WALLET_LOCATION} export CLUSTER_TNS_ADMIN=${TNS_ADMIN} 1.19 Support for Oracle SQL Access to KafkaSQL Developer enables you to work with the Oracle SQL Access to Kafka (OSAK) technology. This section contains the following topics:
1.19.1 OverviewOracle SQL Access to Kafka (OSAK) enables Oracle external tables to connect to Kafka brokers and read Kafka records.You can query Kafka data and join it with data in Oracle Database tables. Note that Kafka data does not have to persist in Oracle Database to be queried. Oracle SQL Access to Kafka also enables you to load Kafka data into tables stored in Oracle Database. 1.19.2 PrerequisitesFor installing and configuring Oracle SQL Access to Kafka, you need the following:
1.19.3 Installation and ConfigurationInstall and Configure Oracle SQL Access to Kafka Unzip the Oracle SQL Access to Kafka kit and refer to
Working with Apache Kafka If you have secured your Apache Kafka cluster, the following properties file must be updated to set the appropriate properties: For more information, refer to the Apache Kafka Security information. Working with Oracle Cloud Infrastructure Streaming Service In order to work with Oracle Cloud Infrastructure Streaming Service, the following properties must be uncommented and set in the cluster configuration properties file:
For more information on Kafka compatibility with Oracle Cloud Infrastructure Streaming Service, refer to the Streaming Service documentation. 1.19.4 Get StartedAs part of the installation and configuration process, a PL/SQL package,
1.19.4.1 Register a Kafka ClusterBefore data can be read from a Kafka topic, register the cluster and assign a unique name. The following example registers the cluster
1.19.4.2 Supported Kafka Record TypesOracle SQL Access to Kafka supports the following Kafka record types:
1.19.4.3 Create Views to Access CSV Data in a Kafka TopicTo query data from a Kafka topic, the next step is to create OSAK views that map to the Kafka topic. When the Kafka record format is CSV or
delimited text, before creating the views, you must create a reference table whose shape is mapped to records in the Kafka topic. Note, if the Kafka record format is The following example creates a reference table
After the reference table is created, create the OSAK views. The following example creates one view per partition in the topic
The above example causes the following view to be created:
where 1.19.4.4 Create Views to Access JSON Data in a Kafka TopicTo query data from a Kafka topic containing JSON data, OSAK views must be created specifying When the Kafka record format is The following example creates one view for the single partition in the topic
The above example causes the following view to be created:
where
1.19.4.5 Query Kafka Data as Continuous StreamsAfter creating the OSAK views, you can query the views using standard SQL. The view name consists of a generated application_id (which is the concatenation of
cluster name, Kafka group name, topic name) concatenated with a view_id. The sensor topic described above has just one partition, and therefore one view; the view name would be OSAK views can be accessed continuously, reading from an initial offset or timestamp to the end of the stream. In this case, the typical usage is querying the most recent Kafka records. The following example sets the starting offset to 100 records below the Kafka partition high water mark using the
It is also possible to set the point from which to start reading to a particular timestamp. Note: Multiple applications reading the same set of OSAK views can result in duplicate Kafka records being processed or Kafka records being skipped, because each application will attempt to manage the offset. When multiple applications read the same topic, create a set of views for each application by using application-specific Kafka group names. Then each application can use their own offset to determine where to read. One application can call ORA_KAFKA.INIT_OFFSET with 100 and use one set of views, another application can call ORA_KAFKA.INIT_OFFSET with 550 and use another set of views, and so on.
1.19.4.6 Explore Kafka Data from a Specific OffsetOracle SQL Access to Kafka enables you to read a specified number of records from a specific offset. This type of access is restricted to applications that create one view per Kafka topic/partition. The following example creates a view for a new application, name
1.19.4.7 Explore Kafka Data from a Specific TimestampIn addition to reading records from a specific offset, Oracle SQL Access to Kafka allows you to position an OSAK view to start at a particular timestamp and read messages in a
user defined window of time. As compared to 1.19.4.8 Customize Oracle SQL Access to Kafka ViewsThe By default, the By
default, a Kafka topic record format that is specified as 'CSV' is considered to have fields delimited by comma and records terminated by new line. The
The The
The supported keys are:
Examples:
1.19.4.9 Reconfigure Existing Kafka Views When New Partitions are Added to an Existing TopicIt is possible for additional Kafka partitions to be added to a topic that
is being processed by OSAK views. Oracle SQL Access to Kafka provides a procedure similar to
1.19.4.10 Load Kafka Data into Temporary TablesOSAK views are Kafka applications that are not transactional within Oracle. Each scan of a view will likely yield new results since the view typically scans Kafka records from an offset to a topic's high water mark (latest record available) which is continually advancing. This becomes problematic if one wants consistency across several SQL queries of the same data set retrieved from an OSAK view. It also becomes problematic if one is executing complicated joins with OSAK views without careful hinting in a SQL query that ensures the OSAK view is the outermost table in a join. Oracle SQL Access to Kafka provides procedures ( Two types of temporary tables are useful: private temporary tables and global temporary tables, which are created by calling the 1.19.4.11 Load Kafka Data into Tables Stored in Oracle DatabaseThe
1.19.5 Reference InformationFor complete reference information for the 1.20 SQL Developer ReportsSQL Developer provides many reports about the database and its objects. You can also create your own user-defined reports. To display reports, click the Reports tab on the left side of the window. If this tab is not visible, select View and then Reports. (As an alternative to the SQL Developer graphical interface, you can use the command-line interface for SQL Developer to generate reports.) Individual reports are displayed in tabbed panes on the right side of the window; and for each report, you can select (in a drop-down control) the database connection for which to display the report. For reports about objects, the objects shown are only those visible to the database user associated with the selected database connection, and the rows are usually ordered by Owner. The detail display pane for a report includes the following icons at the top:
The time required to display specific reports will vary, and may be affected by the number and complexity of objects involved, and by the speed of the network connection to the database. For most reports that contain names of database objects, you can double-click the object name in the report display pane (or right-click the object name and select Go To) to display that object in a detail view pane, just as if you had selected that object using the Connections navigator. To export a report into an XML file that can be imported later, right-click the report name in the Reports navigator display and select Export. To import a report that had previously been exported, select the name of the report folder name (such as a user-defined folder) in which to store the imported report, right-click, and select Import. You can create a shared report from an exported report by clicking Tools, then Preferences, and using the Database: User Defined Extensions pane to add a row with Type as REPORT and Location specifying the exported XML file. The next time you restart SQL Developer, the Reports navigator will have a Shared Reports folder containing that report. Reports are grouped in the following categories: About Your Database reports list release information about the database associated with the connection. All Objects reports list information about all objects accessible to the user associated with the specified database connection, not just objects owned by the user. Application Express reports list information about Oracle Application Express 3.0.1 (or later) applications, pages, schemas, UI defaults, and workspaces. ASH and AWR reports list information provided by the Active Session History (ASH) and Automated Workload Repository (AWR) features. Database Administration reports list usage information about system resources. Data Dictionary reports list information about the data dictionary views that are accessible in the database. Examples of data dictionary views are ALL_OBJECTS and USER_TABLES. PL/SQL reports list information about your PL/SQL objects and allow you to search the source of those objects. Scheduler reports list information about jobs running on the database. Security reports list privilege-related information about the database. Streams reports list information about stream rules. Table reports list information about tables owned by the user associated with the specified connection. These reports can help you to better understand the metadata and data. The table reports include Quality Assurance reports that indicate possible logical design flaws and sources of runtime performance problems. XML reports list information about XML objects. Data Modeler reports list information about design objects that have been exported to the Data Modeler reporting repository. TimesTen reports list information about Oracle TimesTen In-Memory database objects. User Defined reports are any customized reports that you have created. 1.20.1 Bind Variables for ReportsFor some reports, you are prompted for bind variables before the report is generated. These bind variables enable you to further restrict the output. The default value for all bind variables is null, which implies no further restrictions. To specify a bind variable, select the variable name and type an entry in the Value field. Any bind variable values that you enter are case insensitive, all matches are returned where the value string appears anywhere in the name of the relevant object type. 1.20.2 About Your Database reportsThe About Your Database reports list release information about the database associated with the selected connection. The reports include Version Banner (database settings) and National Language Support Parameters (NLS_xxx parameter values for globalization support). 1.20.3 All Objects reportsAll Objects reports list information about objects visible to the user associated with the database connection. All Objects: For each object, lists the owner, name, type (table, view, index, and so on), status (valid or invalid), the date it was created, and the date when the last data definition language (DDL) operation was performed on it. The Last DDL date can help you to find if any changes to the object definitions have been made on or after a specific time. Collection Types: Lists information about for each collection type. The information includes the type owner, element type name and owner, and type-dependent specific information. Dependencies: For each object with references to it, lists information about references to (uses of) that object. Invalid Objects: Lists all objects that have a status of invalid. Object Count by Type: For each type of object associated with a specific owner, lists the number of objects. This report might help you to identify users that have created an especially large number of objects, particularly objects of a specific type. Public Database Links: Lists all public database links. Public Synonyms: Lists all public synonyms. 1.20.4 Application Express reportsIf you select a connection for a schema that owns any Oracle Application Express 3.0.1 (or later) applications, the Application Express reports list information about applications, pages, schemas, UI defaults, and workspaces. For information about Oracle Application Express, see the documentation for that product. 1.20.5 ASH and AWR reportsThe ASH and AWR reports list information provided by the Active Session History (ASH) and Automated Workload Repository (AWR) features, which require special licensing. 1.20.6 Charts reportsCharts reports include a chart showing the distribution of objects of various object types (number of tables, indexes, and so on). 1.20.7 Database Administration reportsDatabase Administration reports list usage information about system resources. This information can help you to manage storage, user accounts, and sessions efficiently. (The user for the database connection must have the DBA role to see most Database Administration reports.) All Tables: Contains the reports that are also grouped under Table reports, including Quality Assurance reports. Cursors: Provide information about cursors, including cursors by session (including open cursors and cursor details. Database Parameters: Provide information about all database parameters or only those parameters that are not set to their default values. Locks: Provide information about locks, including the user associated with each. Sessions: Provide information about sessions, selected and ordered by various criteria. Storage: Provide usage and allocation information for tablespaces and data files. Top SQL: Provide information about SQL statements, selected and ordered by various criteria. This information might help you to identify SQL statements that are being executed more often than expected or that are taking more time than expected. Users: Provide information about database users, selected and ordered by various criteria. For example, you can find out which users were created most recently, which user accounts have expired, and which users use object types and how many objects each owns. Waits and Events: Provide information about waits and events, selected by criteria related to time and other factors. For Events in the Last x Minutes, specify the number of minutes in the Enter Bind Values dialog box. 1.20.8 Data Dictionary reportsData Dictionary reports list information about the data dictionary views that are accessible in the database. Examples of data dictionary views are ALL_OBJECTS and USER_TABLES. Dictionary View Columns: For each Oracle data dictionary view, lists information about the columns in the view. Dictionary Views: Lists each Oracle data dictionary view and (in most cases) a comment describing its contents or purpose. 1.20.9 Scheduler reportsJobs reports list information about jobs and other objects related to scheduling jobs using SQL Developer. DBMS Jobs: Lists information about all jobs, DBA jobs (jobs for which a DBA user is associated with the database connection), and your jobs (jobs for which the user associated with the database connection is the log user, privilege user, or schema user). The information for each job includes the start time of its last run, current run, and next scheduled run. Definitions: Lists information about all objects of types associated with job scheduling. Executions: Lists information about the executions of jobs. 1.20.10 PL/SQL reportsPL/SQL reports list information about PL/SQL packages, function, and procedures, and about types defined in them. Program Unit Arguments: For each argument (parameter) in a program unit, lists the program unit name, the argument position (1, 2, 3, and so on), the argument name, and whether the argument is input-only (In), output-only (Out), or both input and output (In/Out). Search Source Code: For each PL/SQL object, lists the source code for each line, and allows the source to be searched for occurrences of the specified variable. Unit Line Counts: For each PL/SQL object, lists the number of source code lines. This information can help you to identify complex objects (for example, to identify code that may need to be simplified or divided into several objects). 1.20.11 Security reportsSecurity reports list information about users that have been granted privileges, and in some cases about the users that granted the privileges. This information can help you (or the database administrator if you are not a DBA) to understand possible security issues and vulnerabilities, and to decide on the appropriate action to take (for example, revoking certain privileges from users that do not need those privileges). Auditing: Lists information about audit policies. Encryption: Lists information about encrypted columns. Grants and Privileges: Includes the following reports:
Policies: Lists information about policies. Public Grants: Lists information about privileges granted to the PUBLIC role. 1.20.12 Streams reportsStreams reports list information about stream rules. All Stream Rules: Lists information about all stream rules. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation. Your Stream Rules: Lists information about each stream rule for which the user associated with the database connection is the rule owner or rule set owner. The information includes stream type and name, rule set owner and name, rule owner and name, rule set type, streams rule type, and subsetting operation. 1.20.13 Table reportsTable reports list information about tables owned by the user associated with the specified connection. This information is not specifically designed to identify problem areas; however, depending on your resources and requirements, some of the information might indicate things that you should monitor or address. For table reports, the owner is the user associated with the database connection. Columns: For each table, lists each column, its data type, and whether it can contain a null value. Also includes Datatype Occurrences: For each table owner, lists each data type and how many times it is used. Comments for tables and columns: For each table and for each column in each table, lists the descriptive comments (if any) associated with it. Also includes a report of tables without comments. If database developers use the COMMENT statement when creating or modifying tables, this report can provide useful information about the purposes of tables and columns Constraints: Includes the following reports related to constraints:
Indexes: Includes information about all indexes, indexes by status, indexes by type, and unused indexes. Organization: Specialized reports list information about partitioned tables, clustered tables, and index-organized tables. Quality Assurance: Reports that identify conditions that are not technically errors, but that usually indicate flaws in the database design. Statistics: For each table, lists statistical information, including when it was last analyzed, the total number of rows, the average row length, and the table type. In addition, specialized reports order the results by most rows and largest average row length. Storage: Lists information about the table count by tablespace and the tables in each tablespace. Triggers: Lists information about all triggers, disabled triggers, and enabled triggers. User Synonyms: Displays information about either all user synonyms or those user synonyms containing the string that you specify in the Enter Bind Variables dialog box (uncheck Null in that box to enter a string). User Tables: Displays information about either all tables or those tables containing the string that you specify in the Enter Bind Variables dialog box (uncheck Null in that box to enter a string). 1.20.13.1 Quality Assurance reportsQuality assurance reports are table reports that identify conditions that are not technically errors, but that usually indicate flaws in the database design. These flaws can result in various problems, such as logic errors and the need for additional application coding to work around the errors, as well as poor performance with queries at runtime. Tables without Primary Keys: Lists tables that do not have a primary key defined. A primary key is a column (or set of columns) that uniquely identifies each row in the table. Although tables are not required to have a primary key, it is strongly recommended that you create or designate a primary key for each table. Primary key columns are indexed, which enhances performance with queries, and they are required to be unique and not null, providing some "automatic" validation of input data. Primary keys can also be used with foreign keys to provide referential integrity. Tables without Indexes: Lists tables that do not have any indexes. If a column in a table has an index defined on it, queries that use the column are usually much faster and more efficient than if there is no index on the column, especially if there are many rows in the table and many different data values in the column. Tables with Unindexed Foreign Keys: Lists any foreign keys that do not have an associated index. A foreign key is a column (or set of columns) that references a primary key: that is, each value in the foreign key must match a value in its associated primary key. Foreign key columns are often joined in queries, and an index usually improves performance significantly for queries that use a column. If an unindexed foreign key is used in queries, you may be able to improve runtime performance by creating an index on that foreign key. 1.20.14 XML reportsXML reports list information about XML objects. XML Schemas: For each user that owns any XML objects, lists information about each object, including the schema URL of the XSD file containing the schema definition. 1.20.15 Data Modeler reportsData Modeler reports list information about objects that have been exported to the Data Modeler reporting repository. (To export to the reporting repository, you must use the full Data Modeler product; you cannot use the Data Modeler that is included in SQL Developer.) To use any Data Modeler reports in SQL Developer, you must have created a database connection to the schema that contains the Data Modeler reporting repository. To view any Data Modeler report, expand the Data Modeler Reports hierarchy and double-click the desired report. If you are prompted for a database connection, select the one containing the reporting repository. If you are prompted for bind variables, select the variable name and type an entry in the Value field. Any bind variable values that you enter are case insensitive, all matches are returned where the value string appears anywhere in the name of the relevant object type. Design Content: Includes reports about the data types and the logical and relational models. Design Rules: Includes reports about potential violations of the Data Modeler design rules. For example, for logical model attributes, you can see attributes without data types and attributes not based on domains; and for relational model tables, you can see tables without columns, primary keys, foreign key links, and unique constraints. 1.20.16 User Defined reportsUser Defined reports are any reports that are created by SQL Developer users. To create a user-defined report, right-click the User Defined node under Reports and select New Report. A dialog box is displayed in which you specify the report name and the SQL query to retrieve information for the report. You can organize user-defined reports in folders, and you can create a hierarchy of folders and subfolders. To create a folder for user-defined reports, right-click the User Defined node or any folder name under that node and select Add Folder. Information about user-defined reports, including any folders for these reports, is stored in a file named UserReports.xml under the directory for user-specific information. For examples of creating user-defined reports, see:
1.20.16.1 User-Defined Report Example: ChartThis example creates a report displayed as a chart. It uses the definition of the EMPLOYEES table from the HR schema, which is a supplied sample schema. Right-click on User Defined Reports and select New Report. In the Create Report dialog box, specify a report name; and for SQL, enter the following: select m.department_id, e.last_name, e.salary from employees m, employees e where e.employee_id = m.employee_id order by 1 The preceding query lists the last name and salary of each employee in each department, grouping the results by department ID (10, 20, 30, ... 110). Note that the expected syntax for the SQL statement for a chart report is: SELECT Bài Viết Liên QuanQuảng CáoCó thể bạn quan tâmToplist được quan tâm#1
Top 2 bài tập mạch chỉnh lưu bán kỳ 20236 tháng trước#2
Top 9 thuốc tẩy quần áo có độc không 20236 tháng trước#3
#4
#6
Top 7 lời bài hát mưa trên cuộc tình tiếng hoa 20236 tháng trước#7
Top 8 toán 8 bài 4 luyện tập trang 16 20236 tháng trước#9
Top 10 trẻ sơ sinh 14 tuần tuổi biết làm gì 20236 tháng trướcQuảng cáoXem NhiềuQuảng cáoChúng tôiTrợ giúpBản quyền © 2021 moiday.com Inc.
|