281⟩ What Operating Systems Are Supported by Oracle SQL Developer?
Oracle SQL Developer is available for three types of operating Systems:
► Windows
► Linux
► Mac OSX
“Learn Oracle database programming with hundreds of Interview Questions and Answers and examples and get a better job as an Oracle DBA with this basic and advance Oracle Database Interview Questions and Answers guide”
Oracle SQL Developer is available for three types of operating Systems:
► Windows
► Linux
► Mac OSX
If you want to download a copy of Oracle SQL Developer, visit http://www.oracle.com/technology/software/products/sql/.
If you are using Windows systems, click the "Oracle SQL Developer for Windows" link. This allows you to download the Windows version in ZIP format with file name of sqldeveloper-v1.0.0.zip in 55,295,721 bytes.
Once you have sqldeveloper-v1.0.0.zip downloaded, extract the zip file into the root directory: .
When the extraction is done, the installation is completed. Your copy of Oracle SQL Developer is installed in directory: sqldeveloper, and ready to run.
To start Oracle SQL Developer, go to sqldeveloper and click sqldeveloper.exe. The Oracle SQL Developer window shows up. Your copy of Oracle SQL Developer is running now. There will nothing under the Connections icon, because you haven't defined any connections yet.
You should visit different menu items to explore the user interface. One nice place to visit is the About item under the Help menu. It provides some very useful information about your copy of Oracle SQL Developer.
Oracel SQL Developer is written in Java. It requires JDK 1.5, which is already included in your download file.
If you have your Oracle 10g XE server running on your local machine, you can connect your Oracle SQL Developer to the server with the following steps:
► Start Oracle SQL Developer
► Right-click on Connections
► Select New Database Connection
► Enter Connection Name as: Local_XE
► Enter Username as: HR
► Enter Password as: globalguideline
► Enter Hostname as: 127.0.0.1
► Enter SID as: XE
► Click Test
► Click Connect, if you see Status changed to Success
You will see the SQL Statement area opened ready to take any SQL statements.
If you have an Oracle server running remotely on a network, and you know all the access information needed, you can following steps to connect your Oracle SQL Developer:
► Start Oracle SQL Developer
► Right-click on Connections
► Select New Database Connection
► Enter Connection Name as: Remote_Server
► Enter Username as: _username_
► Enter Password as: _password_
► Enter Hostname as: _hostname_
► Enter SID as: _sidname_
► Click Test
► Click Connect, if you see Status changed to Success
You will see the SQL Statement area opened ready to take any SQL statements.
Once a connection is established with an Oracle server, you can enter any SQL statements in the SQL Statement area. Try yourself with the following steps:
► Go to the SQL Statement area
► Enter SELECT username, default_tablespace FROM USER_USERS;
► Press F9 or click the Execute Statement icon
You will see the statement results in the Results area.
SQL Developer allows you to export your connection information into an XML file. Here is how to do this:
► Right-click on Connections
► Select Export Connection...
► Enter File Name as: empconnections.xml
► Click OK
Open empconnections.xml, you should see an XML message like this:
<?xml version = '1.0'?><!DOCTYPE connections>
<connections>
<connection>
<JDBC_PORT>1521</JDBC_PORT>
<HOSTNAME>127.0.0.1</HOSTNAME>
<ConnectionType>JDBC</ConnectionType>
<DeployPassword>false</DeployPassword>
<user>HR</user>
<ConnectionName>Local_XE</ConnectionName>
<SID>XE</SID>
<JdbcDriver>oracle.jdbc.driver.OracleDriver
</JdbcDriver>
<PWD>
<![CDATA[0519D91AA309BF15EA9E54BF55F863710496...]]>
</PWD>
<ORACLE_JDBC_TYPE>thin</ORACLE_JDBC_TYPE>
</connection>
</connections>
Most of the time, you only run SQL statements in SQL Worksheet, the SQL statement area. But you can also run some SQL*Plus commands in SQL Worksheet. The example below shows you how to run the DECRIBE command in SQL Developer:
► Go to SQL Worksheet - SQL statement area
► Enter DESCRIBE USER_USERS
► Press F9 to run the command
You will see the following output:
Name Null Type------------------------------ -------- ------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL
DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
The DESCRIBE command returns you the detailed information about the specified table.
You can work with data objects through SQL statements in statement area. If you want to work with data objects interactively, you should use the object browser. The following tutorial steps help you to browse data objects:
► Click the Connections tab.
► Open the Connections icon.
► Open a connection, like Local_XE. You see a list of data object types.
► Open the Connections icon.
► Open Tables. You see list of existing tables.
► Open a table, like EMPLOYEES. You see a list of columns.
► Double-click a table, like EMPLOYEES. You see a list of tabs opened for this table.
Once a table opened, SQL Developer offers you many views on this table, like Columns, Data, Indexes, Constraints, Statistics, Triggers, etc.
You have an existing table and want to get a CREATE statement for that table, you can use the SQL tab in the object view. The following tutorial steps show you how to use SQL Developer to generate a CREATE statement on an existing table:
► Double-click the table name JOBS in the object tree area.
► Click the SQL tab in the object view area.
In a moment, you will get the following CREATE statements:
REM HR JOBS
CREATE TABLE "HR"."JOBS"
( "ID" VARCHAR2(10 BYTE),
"TITLE" VARCHAR2(35 BYTE) CONSTRAINT "TITLE_NN"
NOT NULL ENABLE,
"MIN_SALARY" NUMBER(6,0),
"MAX_SALARY" NUMBER(6,0),
CONSTRAINT "ID_PK" PRIMARY KEY ("ID") ENABLE
) ;
REM HR ID_PK
CREATE UNIQUE INDEX "HR"."ID_PK" ON "HR"."JOBS" ("ID")
;
If you don't want to use SQL statements to create a new table, you use SQL Developer to create one interactively. Follow the steps below to create a new table called: TIP
► Right-click on the Tables in the object tree area.
► Select Create TABLE. Create Table window shows up.
► Enter Name as: TIP
► Enter Column Name as: ID, for column 1.
► Select Type as: INTEGER, for column 1.
► Click Primary as: Checked, for column 1.
► Click Add Column to add column 2.
► Enter Column Name as: SUBJECT, for column 2.
► Select Type as: VARCHAR2, for column 2.
► Select Size as: 80, for column 2.
► Click OK.
If you don't like to use the INSERT statement to enter a new row into a table, you can use the object view to enter it interactively. Follow the steps below to enter new row into table TIP:
► Double-click on the table name TIP.
► Click the Data tab in the object view.
► Click the Insert Row icon, the + sign.
► Enter ID as: 101.
► Enter SUBJECT as: Backup #1.
► Click the Commit Changes icon.
You know the new row is inserted, because the log area shows you:
INSERT INTO "HR"."TIPS" (ID, SUBJECT) VALUES ('101', 'Backup #1')
Commit Successful
The Reports view lets you browse database information that organized by the server as special views. Information can be browsed include:
► Database parameters
► Storage information
► Session information
► Cursors
► Data objects
► User accounts
► Security information
If you don't like to use a SELECT statement to get a list of all tables in the current database, you can use the Reports view to do this as shown in the following tutorial example:
► Click menu View.
► Selects Reports from the menu.
► Open Reports.
► Open Data Dictionary Reports.
► Open Table.
► Double-click User Tables. You see the Enter Bind Values window.
► Click Apply.
You should get a list all current tables in the database.
If you don't like to use a SELECT statement to get a list of all user accounts in the current database, you can use the Reports view to do this as shown in the following tutorial example. You need to connect to the server as SYSTEM to do this:
► Click menu View.
► Selects Reports from the menu.
► Open Reports.
► Open Data Dictionary Reports.
► Open Database Administration.
► Open Users.
► Double-click All Users.
You should get a list all current user accounts in the database.
If you don't like to use a SELECT statement to get a list of all background sessions in the current database, you can use the Reports view to do this as shown in the following tutorial example. You need to connect to the server as SYSTEM to do this:
► Click menu View.
► Selects Reports from the menu.
► Open Reports.
► Open Data Dictionary Reports.
► Open Database Administration.
► Open Sessions.
► Double-click Background Sessions.
You should get a list all current background sessions in the database.
Oracle SQL Developer also lets you create your own reports. See the following steps on how to do this:
► Click menu View.
► Selects Reports from the menu.
► Open Reports.
► Right-click on User Defined Reports.
► Select Add Report.
► Enter Name as: My Team.
► Enter Description as: List of employees in my team.
► Enter SQL as: SELECT * FROM HR.EMPLOYEES WHERE MANAGER_ID=100
► Click Apply.
Your report "My Team" is ready.
Oracle SQL Developer can allow to export table data into files in the following formats:
► TXT - Tab delimited fields file format.
► CSV - Comma Separated Values (CSV) file format.
► LOADER - File format used by SQL*Loader.
► XML - XML file format.
► INSERT - INSERT statements format.