Oracle Database Basic Command

Oracle Database Basic Command

** Credit Web

Alter
Application:	Altering a users password, making changes to a table or column.
Change Password:	ALTER user username IDENTIFIED BY newpassword
Example	ALTER USER tony IDENTIFIED BY cereal
Adding a column:	ALTER TABLE tablename ADD column name datatype
Example:	ALTER TABLE project ADD names varchar2(25)
Commit
Application:	Commit data to a database table.
Save inserted data	Commit
Constraints
Application:	Allows for the creation of column constraints such as Primary and Foreign keys.
Create Primary Key	CONSTRAINT constraint name_pk PRIMARY KEY
Example	CONSTRAINT cd_cd_id_pk PRIMARY KEY (cd_id)
	{NOTE: this is the format when using at the end of you column declarations in a CREATE Table statement)
Create a Foreign Key	CONSTRAINT constraint name_fk REFERENCEStablename(primary key)
Example	CONSTRAINT cd_cd_id_fk REFERENCES cd(cd_id)
	{NOTE: this is the format when using at the end of you column declarations in a CREATE Table statement)
ADD a PRIMARY Key Constraint (after table created)	ALTER TABLE tablename
	ADD CONSTRAINT constraint name PRIMARY KEY (column name)
Example	ALTER TABLE cd
	ADD CONSTRAINT cd_cd_id_pk PRIMARY KEY(cd_id);
Add a FOREIGN KEY Constraint (after table created)	ALTER TABLE tablename
	ADD CONSTRAINT constraint name REFERENCEStable(column name)
Example	ALTER TABLE cd
	ADD CONSTRAINT cd_cd_id_fk REFERENCES cd(cd_id);
Create
Application:	Creates a table,view, index, sequence, user etc. in the database.
Create Table	CREATE TABLE tablename (data definition)
Example:	CREATE TABLE project ( name varchar2(25), activity varchar2(30))
Create View	CREATE VIEW viewname (column names)AS SELECT select statement
Example:	CREATE VIEW report (Names, Addresses) AS SELECT Select name, address FROM book
Delete
Application:	Delete a record from a table.
Delete record	DELETE FROM tablename WHERE search condition
Example:	DELETE FROM project WHERE projid = 21
Drop
Application:	Drop a table or view from a database.
Drop Table	DROP TABLE tablename
Example	DROP TABLE projects
Drop View	DROP VIEW view name
Example	DROP VIEW lowscores
Insert
Application:	Insert records into a table.
Insert record	INSERT INTO table (columns1, column2....) VALUES (value1, value2...)
Example	INSERT INTO project (name, activity) VALUES (building, construction)
Modify
Application:	Modify a column in a table.
Modify a column in a table	ALTER TABLE tablename MODIFY column name new data definition
Example:	ALTER project MODIFY (name varchar2(50))
Rename
Application:	Renames a table.
Rename table	RENAME oldtablename TO new tablename
Example:	RENAME projects99 TO projects2000
Various Select Statements
SELECT Statement	SELECT column1, column2... FROM table
Example	SELECT project_name FROM project
Column Alias	Creates an alias for the database column.
	SELECT column name "alias name" FROM table
Example	SELECT name "Our Team" FROM project
Order By	SELECT column FROM table ORDER BY column name
Example	SELECT name FROM project ORDER BY project_num
Order By (Ascending order)	SELECT name FROM project ORDER BY project_num ASC
Order By (Descending order)	SELECT name FROM project ORDER BY project_num DESC
COUNT	Returns an integer representing the number of counted rows.
Example	SELECT count(*) FROM projects
AVG	Returns the average value of a numberic column's returned values.
Example	SELECT avg(project_cost) FROM project
SUM	Sums a numeric column;'s returned values.
Example	SELECT sum(project_count) FROM project
MIN	Returns the minimum value of a numeric column's returned values.
Example	SELECT min(project_cost) FROM project
MAX	Returns the maximum value of a numeric column's return values.
Example	SELECT max(project_cost) FROM project
Distinct	Supresses duplicate values.
Example	SELECT DISTINCT project_date FROM project
Math Operators (+,-,*,/)	Performs basic calculations in a select statement
Example	SELECT total+5 FROM project
Logical Operators (AND, OR, NOT)	Allows you to apply multiple search criteria.
Example	SELECT project_name FROM projects WHERE project_date > '02-FEB-00' AND project_cost < 150.00
LIKE or =	These are applied to your search condition in the WHERE clause.
Example LIKE	Use when applying wildcards on VARCHAR2 datatypes.
	SELECT name FROM project WHERE name LIKE 'JONES%'
Example =	Use when knowing the exact search parameter in your WHERE cluase.
	SELECT cost FROM project WHERE cost = 150.00
SQL Plus
Commands	Most of the commands listed here also require a FULL path name to carry out the desired task.
Save FILENAME	Saves a file.
Get FILENAME	Opens a file.
Start FILENAME	Executes a file.
@FILENAME	Executes a file.
Edit FILENAME	Opens a file for editing in Notepad.
Spool FILENAME	Spools out to a file.
Exit	Exits out of SQL Plus
Disc	Disconnects from the Database.
Connect	Connects to the database: CONNECT username/password@instance
Formatting Columns	Apply these techniques to alter the way your data is displayed in a columnar format.
Character and Date Columns	An, sets the display width of n
Examples
Create column headings	COLUMN name HEADING ‘Customer|Name’ FORMAT A70
	COLUMN bact_balance JUSTIFY LEFT FORMAT $999,999,990.00
	COLUMN inv_date FORMAT A9 NULL ‘No Invoice’
Display the current settings	COLUMN name
Clear Settings	COLUMN name CLEAR
Describe	Diplays table column names and datatypes.
	DESCR tablename
Example	DESCR project

Basic Knowledge on Oracle Database

Today I wanted to publish the overview of an Oracle database.  By the end of this post, You will came to know the following information.

·         Definition of an Oracle, Oracle Server and Oracle Database.

        ·         Physical and Logical structure of the database.

Oracle is the name of database management system and is developed by Oracle Corporation.

Oracle Server manages the data in the database. Users will access the oracle server using the SQL commands. So Oracle server receives SQL commands from users and executes them on the database.

An Oracle database is a logical collection of inter related data or is a collection of data, treated as a single unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers.

The database has Logical structures and the Physical Structures.

Oracle Database Physical Structure:

The Physical structure of the database is placed in Physical level. It is physically a set of three operating system files.

o   Data Files : It contains the data of the database.  Every table that is stored in the database is a part of these files. Only Oracle Server can interpret these datafiles.

o   Redo log files : Every database has a set of two or more Redo Log files. The set of redo log files is known as databases redo log. Redo Log files are used in the failure recovery. All the changes made to the database are written to redo log files.

o   Control files : This can be treated as a heart of a database. Contain information required to verify the integrity of the database.   The control file will record the following information

·         Database name

·         Name and location of data files and redo log files and ect.  

I will publish the detailed information on these Physical structure of the database in the further posts.

Oracle Database Logical Structure: 

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest. 

• Tablespace
• Segments
• Extents
• Data blocks

Tablespace:  Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace. 

Segment :  Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

o   Data segment  (stores user data within the database)

o   Index Segment (store Indexes)

o   Temporary Segment ( stores rollback segment)

o   Rollback/Undo segment (created when sql statement needs a temporary work area)

Extent : An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file. 

Data Blocks : Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device.