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;

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 Basic Column Definition
column_definition -> field_name data_type_definition,

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
CURRVALMost recent sequence number
NEXTVALNext available sequence number
SYSDATECurrent system date and time from DUAL table
USERCurrent 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 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
ABSAbsolute value
CEILCeiling
FLOORFloor
SIGNSign of a number
SQRTSquare Root

Aggregate numeric functions
AVGAverage value of field
COUNTNumber of records returned
MAXMaximum value in field in returned records
MINMaximum value in field in returned records
SUMSum 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
LIKEWildcard match verb
_single character wildcard
%multiple character wildcard
NULLmatches null values

Other Commands
Show

Password

Help Index

Exit




Top of this page   Top of page      Home page   Home page