Oracle/Database/sqlplus

From Omnia
Jump to navigation Jump to search

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 [1] 

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;

# 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:

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;

Issues

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:

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:

  • ???

keywords

Retrieved from "https://aznot.com/index.php?title=Oracle/Database/sqlplus&oldid=1986"