Oracle Business Intelligence: February 2014

What Is SQL*Plus?

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

Format, perform calculations on, store, and print from query results
Examine table and object definitions
Develop and run batch scripts
Perform database administration

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.

How To Start the Command-Line SQL*Plus?

If you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:\oraclexe\app\oracle\product\10.2.0\server\

>.\bin\sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

SQL>

How To Get Help at the SQL Prompt?

Once SQL*Plus is started, you will get a SQL prompt like this: SQL>. This where you can enter commands for SQL*Plus to run.

To get help information at the SQL prompt, you can use the HELP command as shown in the following tutorial example:

SQL> HELP INDEX

Enter Help [topic] for help.

@ COPY PAUSE SHUTDOWN

@@ DEFINE PRINT SPOOL

/ DEL PROMPT SQLPLUS

ACCEPT DESCRIBE QUIT START

APPEND DISCONNECT RECOVER STARTUP

ARCHIVE LOG EDIT REMARK STORE

ATTRIBUTE EXECUTE REPFOOTER TIMING

BREAK EXIT REPHEADER TTITLE

...

COMPUTE LIST SET XQUERY

CONNECT PASSWORD SHOW

SQL> HELP CONNECT

CONNECT

-------

Connects a given username to the Oracle Database. When you

run a CONNECT command, the site profile, glogin.sql, and

the user profile, login.sql, are processed in that order.

CONNECT does not reprompt for username or password if the

initial connection does not succeed.

CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA}]]

where logon has the following syntax:

username[/password][@connect_identifier]

What Information Is Needed to Connect SQL*Plus an Oracle Server?

If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:

The network hostname, or IP address, of the Oracle server.
The network port number where the Oracle server is listening for incoming connections.
The name of the target database instance managed by the Oracle server.
The name of your user account predefined on in the target database instance.
The password of your user account predefined on in the target database instance.

What Is a Connect Identifier?

A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.

Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

FYI_XE =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = dba.fyicenter.com)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

The above "connect identifier" defines "TNS_XE" with the following connection information:

The network hostname: dba.fyicenter.com.
The network port number: 1521.
The name of the target database instance: XE.

How To Connect a SQL*Plus Session to an Oracle Server?

In order to connect a SQL*Plus session to an Oracle server, you need to:

1. Obtain the connection information from the Oracle server DBA.

2. Define a new "connect identifier" called "FYI_XE" in your tnsnames.org file with the given connection information.

3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:

>cd c:\oraclexe\app\oracle\product\10.2.0\server\

>.\bin\sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

SQL> CONNECT fyi/retneciyf@FYI_XE;

Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE

---------

05-MAR-06

What Happens If You Use a Wrong Connect Identifier?

Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:

SQL> CONNECT fyi/retneciyf@WRONG;

ERROR:

ORA-12154: TNS:could not resolve the connect identifier

specified

Warning: You are no longer connected to ORACLE.

What you need to do in this case:

Check the CONNECT command to make sure that the connect identifier is entered correctly.
Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.

What To Do If DBA Lost the SYSTEM Password?

If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:

(Terminal server to the Oracle server machine)

(Start SQL*Plus)

SQL>CONNECT / AS SYSDBA

Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_iyf;

User altered.

Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.

What Types of Commands Can Be Executed in SQL*Plus?

There are 4 types of commands you can run at the SQL*Plus command line prompt:

1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM fyi_faq;" is a SQL command.

2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to dba.fyicenter.com')" runs a PL/SQL command.

3. SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.

4. OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.

How To Run SQL Commands in SQL*Plus?

If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:

SQL> SELECT 'Welcome!' FROM DUAL;

'WELCOME

--------

Welcome!

SQL> SELECT 'Welcome to FYIcenter.com tutorials!'

2 FROM DUAL

3 ;

'WELCOMETOFYICENTER.COMTUTORIALS!'

-----------------------------------

Welcome to FYIcenter.com tutorials!

How To Run PL/SQL Statements in SQL*Plus?

If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to FYIcenter!')

Welcome to FYIcenter!

PL/SQL procedure successfully completed.

How To Change SQL*Plus System Settings?

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
SET HEADING OFF - Stops displaying the header line of the query output.
SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
SET WRAP OFF - Turns off the wrapping feature when displaying query output.

How To Look at the Current SQL*Plus System Settings?

If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:

SQL> SHOW AUTOCOMMIT

autocommit OFF

SQL> SHOW HEADING

heading ON

SQL> SHOW LINESIZE

linesize 80

SQL> SHOW PAGESIZE

pagesize 14

SQL> SHOW FEEDBACK

FEEDBACK ON for 6 or more rows

SQL> SHOW TIMING

timing OFF

SQL> SHOW NULL

null ""

SQL> SHOW ALL

appinfo is OFF and set to "SQL*Plus"

arraysize 15

autocommit OFF

autoprint OFF

autorecovery OFF

autotrace OFF

blockterminator "." (hex 2e)

cmdsep OFF

colsep " "

compatibility version NATIVE

concat "." (hex 2e)

copycommit 0

COPYTYPECHECK is ON

define "&" (hex 26)

describe DEPTH 1 LINENUM OFF INDENT ON

echo OFF

...

What Are SQL*Plus Environment Variables?

Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables:

ORACLE_HOME - The home directory where your Oracle client application is installed.
PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOME\bin.
SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOME\sqlplus\mesg
TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.

How To Generate Query Output in HTML Format?

If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example:

SQL> connect HR/retneciyf

SQL> SET MARKUP HTML ON

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%';

FIRST_NAME	LAST_NAME	HIRE_DATE
John	Seo	12-FEB-98
John	Russell	01-OCT-96

What Is Output Spooling in SQL*Plus?

The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.

Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:

SPOOL fileName - Turning on output spooling with the specified file.
SPOOL OFF - Turning off output spooling and close the spool file.

How To Save Query Output to a Local File?

Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:

SQL> connect HR/retneciyf

SQL> SET HEADING OFF

SQL> SET FEEDBACK OFF

SQL> SET LINESIZE 1000

SQL> SPOOL \temp\employees.lst

SQL> SELECT * FROM EMPLOYEES;

......

SQL> SPOOL OFF

You should get all records in employees.lst with fixed length fields.

What Is Input Buffer in SQL*Plus?

Input buffer is a nice feature of the command-line SQL*Plus tool. It allows you to revise a multiple-line command and re-run it with a couple of simple commands. By default, input buffer is always turned on in SQL*Plus. The last SQL statement is always stored in the buffer. All you need is to remember to following commonly used commands:

LIST - Displays the SQL statement (the last executed SQL statement) in the buffer.
RUN - Runs the SQL statement in the buffer again. ";" is a quick command equivalent to RUN.
CLEAR BUFFER - Removes the SQL statement in the buffer.
INPUT line - Adds a new line into the buffer.
APPEND text - Appends more text to the last line in the buffer.
DEL - Deletes one line from the buffer.
CHANGE /old/new - Replaces 'old' text with 'new' text in the buffer.

How To Revise and Re-Run the Last SQL Command?

If executed a long SQL statement, found a mistake in the statement, and you don't want enter that long statement again, you can use the input buffer commands to the correct last statement and re-run it. The following tutorial exercise gives you a good example:

SQL> connect HR/retneciyf

SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%';

FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'

ERROR at line 2:

ORA-00942: table or view does not exist

SQL> LIST

1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> CHANGE /EMPLOYEE/EMPLOYEES/

2* FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%'

SQL> RUN

(Query output)

SQL> INPUT ORDER BY FIRE_DATE

SQL> LIST

1 SELECT FIRST_NAME, LAST_NAME, HIRE_DATE

2 FROM EMPLOYEE WHERE FIRST_NAME LIKE 'Joh%'

3* ORDER BY HIRE_DATE

SQL> RUN

(Query output)

SQL> CLEAR BUFFER

buffer cleared

SQL> LIST

SP2-0223: No lines in SQL buffer.

How Run SQL*Plus Commands That Are Stored in a Local File?

If you have a group of commands that you need to run them repeatedly every day, you can save those commands in a file (called SQL script file), and using the "@fileName" command to run them in SQL*Plus. If you want to try this, create a file called \temp\input.sql with:

SELECT 'Welcome to' FROM DUAL;

SELECT 'FYIcenter.com!' FROM DUAL;

Then run the "@" command in SQL*Plus as:

SQL> connect HR/retneciyf

SQL> @\temp\input.sql

'WELCOMETO

----------

Welcome to

'FYICENTER.COM

--------------

FYIcenter.com!

How To Use SQL*Plus Built-in Timers?

If you don't have a stopwatch/timer and want to measure elapsed periods of time, you can SQL*Plus Built-in Timers with the following commands:

TIMING - Displays number of timers.
TIMING START [name] - Starts a new timer with or without a name.
TIMING SHOW [name] - Shows the current time of the named or not-named timer.
TIMING STOP [name] - Stops the named or not-named timer.

The following tutorial exercise shows you a good example of using SQL*Plus built-in timers:

SQL> TIMING START timer_1

(some seconds later)

SQL> TIMING START timer_2

(some seconds later)

SQL> TIMING START timer_3

(some seconds later)

SQL> TIMING SHOW timer_1

timing for: timer_2

Elapsed: 00:00:19.43

(some seconds later)

SQL> TIMING STOP timer_2

timing for: timer_2

Elapsed: 00:00:36.32

SQL> TIMING

2 timing elements in use

What Is Oracle Server Autotrace?

Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning:

Statement execution path - Shows you the execution loop logic of a DML statement.
Statement execution statistics - Shows you various execution statistics of a DML statement.

To turn on the autotrace feature, the Oracle server DBA need to:

Create a special table called PLAN_TABLE.
Create a special security role called PLUSTRACE.
Grant PLUSTRACE role your user account

How To Set Up Autotrace for a User Account?

· If an Oracle user wants to use the autotrace feature, you can use the tutorial as an example to create the required table PLAN_TABLE, the required security role PLUSTRACE, and grant the role to that user:

· SQL> CONNECT HR/retneciyf

· SQL> @\oraclexe\app\oracle\product\10.2.0\server

· \RDBMS\ADMIN\UTLXPLAN.SQL

· Table (HR.PLAN_TABLE) created.

· SQL> CONNECT / AS SYSDBA

· SQL> @C:\oraclexe\app\oracle\product\10.2.0\server

· \SQLPLUS\ADMIN\PLUSTRCE.SQL

· SQL> drop role plustrace;

· Role (PLUSTRACE) dropped.

· SQL> create role plustrace;

· Role (PLUSTRACE) created.

· SQL> grant plustrace to dba with admin option;

· Grant succeeded.

· SQL> GRANT PLUSTRACE TO HR;

· Grant succeeded.

· Remember that PLAN_TABLE table must be created under the user schema HR.

How To Get Execution Path Reports on Query Statements?

If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial exercise bellow shows you a good example:

· SQL> CONNECT HR/retneciyf

· SQL> SET AUTOTRACE ON EXPLAIN

· SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE

· 2 FROM EMPLOYEES E, JOBS J

· 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

· LAST_NAME SALARY JOB_TITLE

· ----------------- ---------- -----------------------------

· King 24000 President

· Kochhar 17000 Administration Vice President

· De Haan 17000 Administration Vice President

· Russell 14000 Sales Manager

· Partners 13500 Sales Manager

· Hartstein 13000 Marketing Manager

· 6 rows selected.

· Execution Plan

· -----------------------------------------------------------

· Plan hash value: 3851899397

· -----------------------------------------------------------

· | | | | |/%CPU|

· -----------------------------------------------------------

· 0|SELECT STATEMENT | | 59| 2832| 4/0|00:00:01

· 1| NESTED LOOPS | | 59| 2832| 4/0|00:00:01

· *2| TABLE ACCESS FULL |EMPLOYEES| 59| 1239| 3/0|00:00:01

· 3| TABLE ACCESS |JOBS | 1| 27| 1/0|00:00:01

· | BY INDEX ROWID | | | | |

· *4| INDEX UNIQUE SCAN|JOB_ID_PK| 1| | 0/0|00:00:01

· -----------------------------------------------------------

· Predicate Information (identified by operation id):

· ---------------------------------------------------

· 2 - filter("E"."SALARY">12000)

· 4 - access("E"."JOB_ID"="J"."JOB_ID")

How To Get Execution Statistics Reports on Query Statements?

· If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example:

· SQL> CONNECT HR/retneciyf

· SQL> SET AUTOTRACE ON STATISTICS

· SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE

· 2 FROM EMPLOYEES E, JOBS J

· 3 WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

· LAST_NAME SALARY JOB_TITLE

· ----------------- ---------- -----------------------------

· King 24000 President

· Kochhar 17000 Administration Vice President

· De Haan 17000 Administration Vice President

· Russell 14000 Sales Manager

· Partners 13500 Sales Manager

· Hartstein 13000 Marketing Manager

· 6 rows selected.

· Statistics

· ----------------------------------------------------------

· 0 recursive calls

· 0 db block gets

· 16 consistent gets

· 0 physical reads

· 0 redo size

· 720 bytes sent via SQL*Net to client

· 384 bytes received via SQL*Net from client

· 2 SQL*Net roundtrips to/from client

· 0 sorts (memory)

· 0 sorts (disk)

· 6 rows processed

Oracle Business Intelligence

Friday, February 21, 2014

Oracle DAC

Oracle Business Intelligence Data Warehouse Administration Console

Introduction to Command-Line SQL*PLUS Client Tool