Lesson 46 :What is Recycle Bin ?

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:

• When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.
• When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.
• When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

Lesson 45 : Subquery

Subquery 

Subquery  is a query whithin  in a query. It is also called as Inner query or Nested query. A subquery is usually added in the WHERE clause of the sql statement.  Usually, a subquery is used when we know how to search for a value using a SELECT statement, but do not know the exact value.

Here are few points to remember,

· Must be enclosed in parentheses

· Can be the object of an IN or NOT IN clause

· Can be the object of EXISTS or NOT EXISTS clause

· Support quantifiers ALL, ANY, SOME

· Support LIKE or NOT LIKE used with a quantifier

· Can specify more than one column to match

· Generate a DISTINCT list of values

Example :

To list out employees who are working in Boston.

SQL> SELECT * from emp WHERE deptno IN (SELECT deptno from dept where location='BOSTON');


Example :

To list out the department managers whose salaries are less than 35,000 and whose budget amounts are greater than 7500000.

SQL> SELECT last_name,first_name FROM employee WHERE salary_amount<35000 AND (SELECT manager_employee_number FROM department WHERE budget_amount >7500000 ) ;

Lesson 44 : SELECT with ANY

SELECT with ANY operator

We can use the ANY operator in a WHERE clause to compare a value with any of the values in a list.

We have to use any one of these operations =, <>, <, >, <=, or >= before ANY.


Example :

SQL> create table emp(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>

SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 750000, 'Toronto',  'Programmer')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 250000, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 450000, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 650000, 'Vancouver','Manager')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 720000, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 360000,'New York',  'Tester')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 760000,'New York',  'Manager')
  3  /
1 row created.
SQL> insert into emp(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from emp
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   James      Kumar      25-JUL-96 25-JUL-06    750000 Toronto    Programmer
02   Aravind    Ramanan    21-MAR-76 21-FEB-86    250000 New Delhi  Tester
03   James      Sudarsan   12-DEC-78 15-MAR-90    450000 New Delhi  Tester
04   Prakash    Shankar    24-OCT-82 21-APR-99    650000 New Delhi  Manager
05   Robert     White      15-JAN-84 08-AUG-98    720000 New Delhi  Tester
06   Balaji   Kozhi     30-JUL-87 04-JAN-96    360000 Boston     Tester
07   Kamesh     Srikharan  31-DEC-90 12-FEB-98    760000 Boston     Manager
08   James      Roy        17-SEP-96 15-APR-02    1232.78 New Delhi  Tester
8 rows selected.
SQL>


SQL>
SQL> SELECT *
  2  FROM emp
  3  WHERE salary > ANY (2000, 3000, 4000);
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
02   Aravind    Ramanan    21-MAR-76 21-FEB-86    250000 New Delhi  Tester
03   James      Sudarsan   12-DEC-78 15-MAR-90    450000 New Delhi  Tester
04   Prakash    Shankar    24-OCT-82 21-APR-99    650000 New Delhi  Manager
05   Robert     White      15-JAN-84 08-AUG-98    720000 New Delhi  Tester
06   Balaji     Kozhi      30-JUL-87 04-JAN-96    360000 Boston     Tester
07   Kamesh     Srikharan  31-DEC-90 12-FEB-98    760000 Boston     Manager
6 rows selected.
SQL>
SQL>

Lesson 43 : TEMPORARY TABLE

GLOBAL TEMPORARY TABLE Vs TEMPORARY TABLE

In Oracle, there is no difference between temporary table and global temporary tables . When we create a temporary table in an Oracle database, it is automatically global, and you are required to include the "Global" key word.

The SQL standard, which defines how the term "GLOBAL TEMPORARY TABLE" is interpreted, allows for either a LOCAL or GLOBAL scope. This would allow for either a user specific table (LOCAL) or everyone (GLOBAL). Oracle implements only the GLOBAL version.

The data we put into an Oracle Temporary table is specific to our session only. That is, only you can see your data even if there are 500 users all using the same table, and your data is deleted from the table when you disconnect (or when you commit the current transaction) depending upon table settings.


To know about Global temporary table http://quickoraclelearning.blogspot.in/2012/11/lesson-33-global-temporary-tables-gtt.html

Lesson 38 - ROLEs

ROLE

A role is a set or group of privileges that can be granted to users or another role. 

Creating a Role

To create a role, you must have CREATE ROLE system privileges.

Syntax :

Role without password

CREATE ROLE rolename [ NOT IDENTIFIED ;

Role with password

CREATE ROLE rolename  IDENTIFIED  {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

Where ,

 Rolenaame  -  Any user defined name that  will refer to the grouping of privileges or Role.

NOT IDENTIFIED    - No password is required to enable the role.

IDENTIFIED   -  a user must be authorized by a specified method before the role is enabled.

 BY password   -   password to enable the role.

 USING package  -  phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

EXTERNALLY    -   user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

GLOBALLY   -  user must be authorized by the enterprise directory service to enable the role.

The following are few privileges that can be granted to any roles,

Select   -  To query the table or view using a select statement.

Insert  -  To add new rows

Update  -  To update  existing rows in the table using the update statement.

Delete  - To delete rows from the table using the delete statement.

References   - To create a constraint that refers to the table.

Alter   -  To change the table definition using the alter table statement.

Index    - To create an index on the table using the create index statement

Execute   -  To execute the  function/procedure

Examples :

SQL> create role ReadonlyUsers;

Role created.

SQL> grant select on emp to ReadonlyUsers;

Grant succeeded.

SQL> grant ReadonlyUsers to Sam;

Grant succeeded.

SQL> revoke ReadonlyUsers from  Sam;

Revoke succeeded.

-         To remove the role from database

SQL> drop role ReadonlyUsers;

Role dropped.

Index

Post	Topics
1	Introduction Database
2	SQL*PLUS
3	How to execute sqlcommands
4	SQL* Plus commands
5	SQL command categories
6	Objects and schema in Oracle
7	Data Types in Oracle
8	Operators in Oracle
9	Database Tables
10	SELECT with WHERE clause
11	SELECT with ORDER BY Clause
12	Aggregate functions or Group by functions
13	SELECT with GROUP BY clause
14	SELECT with HAVING clause
15	UPDATE Statement
16	DELETE Statement
17	TRUNCATE TABLE  Statement
18	Built-in Functions - Arithmetic
19	Built-in Functions - Character types
20	Constraints
21	Sequences
22	SYNONYMS and PUBLIC SYNONYMS
23	VIEW
24	Union, Union all, Intersect and Minus
25	Date Functions
26	SAVEPOINT
27	Rollback
28	Indexes
29	ROLLUP option in SELECT
30	Pseudocolumn - ROWID
31	CUBE in SELECT
32	Virtual Columns
33	Global temporary tables (GTT)
34	GRANT and REVOKE
35	COMMENT Statement
36	Pseudocolumn - ORA_ROWSCN
37	Pseudocolumn - ROWNUM
38
Topics to come….
	Bind Variables
	Create Directory Statement
	Cursors
	Data Dictionary
	Data Pump
	Database Links
	Special functions
	DBMS JOBS
	Decode
	Exceptions
	Explain Plan
	External Table
	Flashback
	FOR loop
	HINT
	IF statement
	Index Organised tables
	Interview Questions on PL/SQL
	Interview Questions on SQL
	JAVA in Oracle
	Joins
	Materialised Views
	Merge
	Nested Tables
	OOPS in Oracle
	Packages
	PL/SQL
	Pseudocolumns
	Roles
	Set Transactions
	SQL Loader
	SQL queires using functions
	SQL Trace
	SQL Tuning
	Stored function
	Stored procedure
	Subquries
	Temporary tables
	Triggers
	User defined Data types
	and more……

Lesson 42 : LOCK Table

LOCK TABLE

LOCK TABLE statement is to lock one or more tables in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation.

Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table.

A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table.

A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

Syntax :

LOCK TABLE tables IN lock_mode MODE [NOWAIT];

Lock_mode is one of the following,

ROW SHARE  -  permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

ROW EXCLUSIVE -  is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

SHARE UPDATE - same as ROW SHARE.

SHARE - permits concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE -  is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

EXCLUSIVE - permits queries on the locked table but prohibits any other activity on it.

NOWAIT

 Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.

If you omit this clause, then the database waits until the table is available, locks it, and returns control to you.

Lesson 41 Oracle Reserved Words

Oracle Reserved Wordsand Key words

The following are Oracle Reserved words

ACCESS

ADD

ALL

ALTER

AND

ANY

AS

ASC

AUDIT

BETWEEN

BY

CHAR

CHECK

CLUSTER

COLUMN

COMMENT

COMPRESS

CONNECT

CREATE

CURRENT

DATE

DECIMAL

DEFAULT

DELETE

DESC

DISTINCT

DROP

ELSE

EXCLUSIVE

EXISTS

FILE

FLOAT

FOR

FROM

GRANT

GROUP

HAVING

IDENTIFIED

IMMEDIATE

IN

INCREMENT

INDEX

INITIAL

INSERT

INTEGER

INTERSECT

INTO

IS

LEVEL

LIKE

LOCK

LONG

MAXEXTENTS

MINUS

MLSLABEL

MODE

MODIFY

NOAUDIT

NOCOMPRESS

NOT

NOWAIT

NULL

NUMBER

OF

OFFLINE

ON

ONLINE

OPTION

OR

ORDER

PCTFREE

PRIOR

PRIVILEGES

PUBLIC

RAW

RENAME

RESOURCE

REVOKE

ROW

ROWID

ROWNUM

ROWS

SELECT

SESSION

SET

SHARE

SIZE

SMALLINT

START

SUCCESSFUL

SYNONYM

SYSDATE

TABLE

THEN

TO

TRIGGER

UID

UNION

UNIQUE

UPDATE

USER

VALIDATE

VALUES

VARCHAR

VARCHAR2

VIEW

WHENEVER

WHERE

WITH

Note : We cannot create any objects using these names,

Example :

SQL> create table with (dd date);

create table with (dd date)

*
ERROR at line 1:

ORA-00903: invalid table name

SQL> create table access (names char(30));

create table access (names char(30))

*
ERROR at line 1:

ORA-00903: invalid table name

Note :The V$RESERVED_WORDS data dictionary view provides the list of all keywords, including whether the keyword is always reserved or is reserved only for particular uses. But we need DBA privilege to access this table - V$RESERVED_WORDS