Oracle/Database/sqlplus

Program Location
Linux Location: $ORACLE_HOME/bin/sqlplus

Windows Location: $ORACLE_HOME/sqlplus.exe

Connect
SQL*Plus: $ sqlplus Enter user-name: / as sysdba

$ sqplus Enter user-name: sysman Enter password: [configured during install]

$ sqlplus [USER]/[PASS]@[DATABASE] $ sqlplus scott/tiger

$ . oraenv $ sqlplus scott/tiger
 * 1) prepare environment

$ export ORACLE_SID=[orcl] $ sqlplus scott/tiger

$ sqlplus "/ as sysdba"

Can specify SID on command line: set ORACLE_SID=orcl

Start SQL*Plus without connecting to the database: SQLPLUS /NOLOG connect / as sysdba connect username/password connect username/password@SID

Connect to Oracle as SYSDBA: CONNECT username/password AS SYSDBA

Remote Connect
Remote connection sqlplus sysman/test12@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[remoteServer])(PORT=1521)))(CONNECT_DATA=(SID=orcl)))' sqlplus sysman/test12@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[remoteServer])(PORT=1521)))(CONNECT_DATA=(SID=orcl)))"

$ORACLE_HOME/network/admin/tnsnames.ora (may need to be created) sqlplus sysman/test12@ken ken = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.194.121)(PORT = 1521) )  (CONNECT_DATA = (SID = orcl) ) )

Show User: SQL> show user USER is "SYS"

SQL> SELECT user FROM dual;

Commands
Show Release: SQL> show release release 1101000600

Show current user: show user;

Show database name: select ora_database_name from dual; select name from v$database; select global_name from global_name;

Show Tables: SQL> SELECT * FROM cat;

COL table_name FORMAT a30; COL table_type FORMAT a30;
 * 1) Limit column widths:

SELECT * FROM all_tables; <- all tables you have access to SELECT * FROM user_tables; <- all tables owned by currently logged in user SELECT * FROM dba_tables; <- all tables in database SELECT table_name FROM user_tables;

Describe Table: (show columns) SQL> DESCRIBE [table];

Show parameters: show parameters; SELECT value FROM v$system_parameter

Show current selected database instance: show parameter instance_name; SELECT value FROM v$system_parameter WHERE name='instance_name';

From command line: sqlplus scott/tiger @[SQL_FILE] | grep "[TEXT]"

User: CREATE USER [USER] IDENTIFIED BY [PASSWORD]; create user newuser identified by newuser default tablespace users temporary tablespace temp; GRANT create session TO [USER]; # allow login GRANT connect to [USER];        # alternate allow login GRANT all on [TABLE] TO [USER]; # allow edit of table GRANT RESOURCE to [USER];           # Allow to grow a table? GRANT UNLIMITED_TABLESPAC to [USER]; # Allow to grow a table? CONNECT [USER]/[PASSWORD] alter user ben quota unlimited on users; ALTER USER [USER] ACCOUNT [LOCK/UNLOCK];    # lock out an account ALTER USER [USER] IDENTIFIED BY [PASSWORD]; # change user password REVOKE create session TO [USER]; REVOKE ALL PRIVILEGES FROM [USER]; DROP USER username [CASCADE]

Roles and Privileges; select * from DBA_ROLES; select * from DBA_ROLE_PRIVS;

Starting an Instance, and Mounting and Opening a Database STARTUP

Starting an Instance Without Mounting a Database STARTUP NOMOUNT

Forcing an Instance to Start STARTUP FORCE

Shutting Down a Database: SHUTDOWN

Shutting Down with the IMMEDIATE Option: SHUTDOWN IMMEDIATE

Abort shutdown: SHUTDOWN ABORT

Suspend Database: ALTER SYSTEM SUSPEND;

Resume Database: ALTER SYSTEM RESUME;

Show database status: SELECT DATABASE_STATUS FROM V$INSTANCE;
 * 1) for suspend, resume.  For shutdown database you will get:
 * 2) ORA-01034: ORACLE not available

Change password: grant connect to identified by ;

Execute script in file: @test.sql start test.sql

Dump last SQL query to file: select ... save my-select.sql desc ... save my-desc.sql

Log output to file: spool [file] ... spool off

References:
 * Thomas Eibner - sqlplus - http://thomas.eibner.dk/oracle/sqlplus/
 * sqlplus commands - http://ss64.com/ora/syntax-sqlplus.html
 * Oracle Syntax - http://ss64.com/ora/syntax.html
 * Starting Up and Shutting Down - http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/start.htm

SQL*Plus FAQ - Oracle FAQ
What is SQL*Plus and where does it come from?

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database.

ORACLE SQL*Plus: An Introduction and Tutorial
VERY DETAILS DOCUMENT

The Oracle Relational Database Management System (RDBMS) is an industry leading database system designed for mission critical data storage and retrieval. The RDBMS is responsible for accurately storing data and efficiently retrieving that data in response to user queries.

The Oracle Corporation also supplies interface tools to access data stored in an Oracle database. Two of these tools are known as SQL*Plus, a command line interface, and Developer/2000 (now called simply Developer), a collection of forms, reports and graphics interfaces. This technical working paper introduces the features of the SQL*Plus tool and provides a tutorial that demonstrates its salient features.

This tutorial is intended for students and database practitioners who require an introduction to SQL, an introduction to working with the Oracle SQL*Plus tool, or both.

Basic Introduction to SQL*PLUS
The SQL*PLUS (pronounced "sequel plus") program allows you to store and retrieve data in the relational database management system ORACLE. Databases consists of tables which can be manipulated by structured query language (SQL) commands. A table is made up of columns (vertical) and rows (horizontal). A row is made up of fields which contain a data value at the intersection of a row and a column. Be aware that SQL*PLUS is a program and not a standard query language.

Our SQL*Plus Reference
SQL*Plus Managing Users CREATE USER username IDENTIFIED BY password;

ALTER USER username IDENTIFIED BY new_password;

DROP USER username [CASCADE] Specify CASCADE to drop all objects in the user's schema.

List all usernames SELECT NAME FROM SYS.USER$

GRANT privilege1, privilege2, ... [ON object_name] TO user1, user2, ...;

REVOKE privilege1, privilege2, ... [ON object_name] FROM username;

* Privileges: CREATE SESSION * CREATE TABLE * DROP TABLE * UNLIMITED TABLESPACE * CREATE USER * GRANT ANY PRIVILEGE * CREATE ANY TABLE * DROP ANY TABLE

GRANT privilege TO user WITH ADMIN OPTION; (Allows user to grant privilege )

Managing Tablespace - You may/should create tablespace for each user so that they do not need to share the same file space. Example tablespace script. CREATE TABLESPACE lhoward DATAFILE 'C:\oracle\lhoward\lhoward.dbf' SIZE 30M;

CREATE USER lhoward IDENTIFIED BY abcd DEFAULT TABLESPACE lhoward QUOTA UNLIMITED ON lhoward;

GRANT CONNECT, RESOURCE TO lhoward;

REVOKE UNLIMITED TABLESPACE FROM lhoward;

GRANT CREATE SESSION, CREATE TABLE TO lhoward;

Defining Data

* Data Types: CHAR * VARCHAR2 * NCHAR * NUMBER * DATE * BLOB * CLOB * BFILE * NCLOB

Basic Column Definition column_definition -> field_name data_type_definition,

* Contraint Types Primary Key * Foreign Key * Check Condition * Not Null * Unique

Defining Primary Key Constraints. CONTRAINT contraint_name PRIMARY KEY [used within a column declaration]

CONSTRAINT contraint_name PRIMARY KEY (fieldname) [used after column declarations]

Defining Foreign Key Constaints CONSTRAINT foreign_key_id FOREIGN KEY (foreign_key_field) REFERENCES table_name(field_name)

Table Management CREATE TABLE table_name column_definition1, column definition2, ... column_definitionN contraint_definitions;

DESCRIBE table_name;

List all tables and their owners SELECT owner, table_name FROM sys.all_tables;

DROP TABLE tablename [CASCADE CONSTRAINTS];

Add a field to an existing table ALTER TABLE table_name ADD(fieldname_data_declaration constraint_definitions);

Modify existing field definition ALTER TABLE table_name MODIFY(fieldname_data_declaration);

Delete a field from a table ALTER TABLE table_name DROP COLUMN fieldname;

Disable and Reenable Constraint ALTER table_name DISABLE CONSTRAINT constraint_name;

ALTER table_name ENABLE CONSTRAINT constraint_name;

Create a sequence CREATE SEQUENCE sequence_name [INCREMENT BY number] [START WITH start_value] [MAXVALUE max_value] [MINVALUE min_value] [CYCLE] [ORDER]

Drop a Sequence DROP SEQUENCE sequence_name;

Pseudocolumns CURRVAL	Most recent sequence number NEXTVAL	Next available sequence number SYSDATE	Current system date and time from DUAL table USER	Current user from DUAL table

Managing Data Add a new record to a table (references all columns) INSERT INTO table_name VALUES(col1_value, col2_value, ...);

Alternate syntax for adding new record (puts data only into names columns) INSERT INTO table_name (col-x_name, col-y_name, ...) VALUES(col-x_value, col-y_value, ...);

Insert a record into a table utilyzing a sequence INSERT INTO table_name VALUES(sequence_name.NEXTVAL, col1_value, col2_value, ...);

Change a field's value in one or more records UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE record_retrieval_conditions;

Delete one or more records from a table DELETE FROM table_name WHERE record_retrieval_conditions;

Remove all records from a table without saving rollback information TRUNCATE TABLE table_name;

Retrieving Data

* Special Tables Objects * Tables * Indexes * Views * Sequences * Users * Constraints * Cons_Columns * Ind_Columns * Tab_Columns * DUAL

Basic Retrieval from single table SELECT [DISTINCT] display_fields FROM tables WHERE field_conditions ORDER BY field_name [ASC | DESC];

Basic Join SELECT display_fields FROM table1, table2 WHERE table1_foreign_key=table2_primary_key AND other_field_conditions;

Basic Group Retrieval from single table SELECT group_field, group_functions FROM tables WHERE field_conditions ORDER BY field_name [ASC | DESC] GROUP BY group_field HAVING condition_on_group;

Numeric Functions Convert a date string into an internal date where date_format specifies format of string date. TO_DATE(string_date, date_format) Return the mod of number in the indicated base MOD(number, base) Raise number to the indicated exponent POWER(number, exponent) Round number to the indicated precision (number of decimal places) ROUND(number precision) Truncate number to the indicated precision (number of decimal places) TRUNC(number, precision)

More numeric functions ABS	Absolute value CEIL	Ceiling FLOOR	Floor SIGN	Sign of a number SQRT	Square Root

Aggregate numeric functions AVG	Average value of field COUNT	Number of records returned MAX	Maximum value in field in returned records MIN	Maximum value in field in returned records SUM	Sum of values in field

String Functions CONCAT(string1, string2) LPAD(string, num_chars, pad_char) RPAD(string, num_chars, pad_char) LTRIM(string, search_string) RTRIM(string, search_string) REPLACE(string, search_string, replacement_string) SUBSTR(string, start_posn, length)

More string functions INITCAP(string)	Capitalize first character LENGTH(string)	Length of string UPPER(string)	Convert all chars to uppercase LOWER(string)	Convert all chars to lowercase

Date Functions ADD_Months(date, num_months_to_add) LAST_DAY(date) - Last day of month as date MONTHS_BETWEEN(date1, date2) - returns decimal difference in months

Retrieving special values from DUAL SELECT sequence_name.CURRVAL, sequence_name.NEXTVAL, SYSDATE, USER FROM DUAL;

Special Search Keywords and Symbols LIKE	Wildcard match verb _	single character wildcard %	multiple character wildcard NULL	matches null values

Other Commands Show

Password

Help Index

Exit

Test Connection
First: Check if Enterprise Manager is working. Login. If successful this is a good first step.

Check TNS: $ tnsping [server_ip]  # tnsping 216.119.199.5 $ tnsping [sid]        # tnsping orcle OK (0 msec)

SQL*Plus using local IPC: $ sqlplus user: sysman pass: test12

Create table, populate and check: create table ken (rec int); insert into ken (rec) values (1); insert into ken (rec) values (2); insert into ken (rec) values (3); select * from ken;

TNS does not know service requested
$ORACLE_HOME\network\admin\tnsnames.ora: ken = (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.199.5)(PORT = 1521))    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.119.199.5) ) )

SQL*Plus on connection: C:\>sqlplus sysman/test12@ken C:\>sqlplus sysman/test12@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=216.119.199.5)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.119.199.5)))"

Error: ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:
 * ORA-12514: TNS:listener does not currently know of service... - http://ora-12514.ora-code.com/

On server check listening services: lsnrctl services

Check listener log: "C:\app\Administrator\diag\tnslsnr\WIN-GRR4TSQIG3O\listener\trace\listener.log"

???

TNS does not know of SID
$ORACLE_HOME\network\admin\tnsnames.ora: ken = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.199.5)(PORT = 1521) )  (CONNECT_DATA = (SID = orcl) ) )

SQL*Plus on connection: C:\>sqlplus sysman/test12@ken C:\Users\Administrator>sqlplus sysman/test12@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (PROTOCOL=TCP)(HOST=216.119.199.5)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))'

Error: ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Solution: