Kenneth: Created page with "== SQLPlus == == SQLPlus == === Program Location === Linux Location: $ORACLE_HOME/bin/sqlplus Windows Location: $ORACLE_HOME/sqlplus.exe === Connect === SQL*Plus: $..."

2015-04-15T16:35:59Z

Created page with "== SQL*Plus == == SQL*Plus == === Program Location === Linux Location: $ORACLE_HOME/bin/sqlplus Windows Location: $ORACLE_HOME/sqlplus.exe === Connect === SQL*Plus: $..."

New page

== SQL*Plus ==

== SQL*Plus ==

=== 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

# prepare environment
$ . oraenv
$ sqlplus scott/tiger

$ 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 [http://penguinman-techtalk.blogspot.com/2009/02/sqlplus-connection-to-remote-oracle.html]
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
<pre>
ken =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.194.121)(PORT = 1521) )
(CONNECT_DATA =
(SID = orcl)
)
)
</pre>

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;

# Limit column widths:
COL table_name FORMAT a30;
COL table_type FORMAT a30;

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;
# for suspend, resume. For shutdown database you will get:
# ORA-01034: ORACLE not available

Change password:
grant connect to <username> identified by <password>;

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

== [http://www.orafaq.com/wiki/SQL*Plus_FAQ 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.

== [http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/ 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.

== [http://www-it.desy.de/systems/services/databases/oracle/sqlplus/sqlplus.html.en 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.

== [http://cs-netlab-01.lynchburg.edu/courses/Oracle/SQLPlus.htm 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;

== Issues ==

=== TNS does not know service requested ===

$ORACLE_HOME\network\admin\tnsnames.ora:
<pre>
ken =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.199.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.119.199.5)
)
)
</pre>

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:
<pre>
ken =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 216.119.199.5)(PORT = 1521) )
(CONNECT_DATA =
(SID = orcl)
)
)
</pre>

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:
* ???

== keywords ==

[[Category:Oracle]]

Oracle/Database/sqlplus - Revision history

Kenneth: Created page with "== SQL*Plus == == SQL*Plus == === Program Location === Linux Location: $ORACLE_HOME/bin/sqlplus Windows Location: $ORACLE_HOME/sqlplus.exe === Connect === SQL*Plus: $..."

Kenneth: Created page with "== SQLPlus == == SQLPlus == === Program Location === Linux Location: $ORACLE_HOME/bin/sqlplus Windows Location: $ORACLE_HOME/sqlplus.exe === Connect === SQL*Plus: $..."