Shared Pool

Shared Pool contains two very important memory structures :

a) Library Cache

b) Data Dictionary Cache

c) Control Structure

Shared Pool can be sized by by using SHARED_POOL_SIZE initialization parameter but it can not be greater than SGA_MAX_SIZE.

a) Library Cache :

Stores information about the most recently used SQL and PL/SQL statements

The Library Cache is divided into Namespaces (or Libraries). These can be found through v$librarycache

SQL> select namespace from v$librarycache;

NAMESPACE
---------------
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA

11 rows selected.

consists of two structures :

1) Shared SQL Area:

    stores and share the execution plan and parse tree for statement is run, to take the advantage of already prepared execution plans but to take the advantage of already created execution plan in Shared Sql Area the text,schema and bind variables must be exactly the same.

2) Share PL/SQL Area:

 Most recently executed PL/SQL statements.

b) Data Dictionary Cache :

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

c) Control Structures - Common control structure information, for example, lock information

/etc/passwd and /etc/shadow file in UNIX

With shadow passwords, the ``/etc/passwd'' file contains account information, and looks like this:

devenv:x:571:571:verma deven:/home/devenv:/bin/bash

Each field in a passwd entry is separated with ":" colon characters, and are as follows:

* Username, up to 8 characters. Case-sensitive, usually all lowercase

*An "x" in the password field. Passwords are stored in the ``/etc/shadow'' file.

*Numeric user id. This is assigned by the ``adduser'' script. Unix uses this
field, plus the following group field, to identify which files belong to the user.
*Numeric group id. Red Hat uses group id's in a fairly unique manner for enhanced file security. Usually the group id will match the user id.

* Full name of user. I'm not sure what the maximum length for this field is, but try to keep it reasonable (under 30 characters).

* User's home directory. Usually /home/username (eg. /home/smithj). All user's personal files, web pages, mail forwarding, etc. will be stored here.

* User's "shell account". Often set to ``/bin/bash'' to provide access to the bash shell (my personal favorite shell).


The ``/etc/shadow'' file contains password and account expiration information for users, and looks like this:

devenv:Ep6mckrOLChF.:10063:0:99999:7:::

As with the passwd file, each field in the shadow file is also separated with ":" colon characters, and are as follows:

* Username, up to 8 characters. Case-sensitive, usually all lowercase. A direct match to the username in the /etc/passwd file.

* Password, 13 character encrypted. A blank entry (eg. ::) indicates a password is not required to log in (usually a bad idea), and a ``*'' entry (eg. :*:) indicates the account has been disabled.

*The number of days (since January 1, 1970) since the password was last changed.

* The number of days before password may be changed (0 indicates it may be changed at any time)

*The number of days after which password must be changed (99999 indicates user can keep his or her password unchanged for many, many years)

* The number of days to warn user of an expiring password (7 for a full week)

* The number of days after password expires that account is disabled

* The number of days since January 1, 1970 that an account has been disabled

* A reserved field for possible future use

ARCHIVELOG AND NOARCHIVELOG MODE

All transactions are recorded in the online redo log file.
If the database is configured for NORACHIVELOG mode, no redo history is saved to archive log files and recovery operations are limited and a loss of transaction work may occur.

Configure your database in ARCHIVELOG mode ,so that a history of redo information is maintained in archived files.By default the database is in NOARCHIVELOG mode.

Changing the Archiving Mode:

Alter database [archivelog | noarchivelog]

To change the archiving more follow the steps
1) shutdown immediate
2) startup mount
3) alter database archivelog;
4) alter database open;

After database is set in ARCHIVELOG mode you can choose the mode of archiving i.e. automatic or manual archiving . In automatic archiving , the ARCn process is enabled and copy the redo files as they are filled.In manual archiving you have to use SQL *Plus or Oracle Enterprise Manager to copy the files.

You can specify upto 10 ARCn processes by

LOG_ARCHIVE_MAX_PROCESSES parameter

Start or Stop Additional Processes

Alter system set log_archive_max_processes=4;

Enabling Automatic Archiving at Instance Startup

LOG_ARCHIVE_START= [true | false]

Enabling Automatic Archiving After Instance Startup

Alter system archive log start;

Disabling Automatic Archiving

Alter system archive log stop

Manually Archiving Online Redo Log Files

Alter system archive log current;

Archive Log Destination

Use LOG_ARCHIVE_DEST_n to specify up to ten archival destinations.

Additional Options

Log_archive_dest_1=”LOCATION=/archive/ MANDATORY REOPEN”
Log_archive_dest_2=”SERVICE=standby_mydb MANDATORY REOPEN=600”
Log_archive_dest_3=”LOCATION =/archive001/ OPTIONAL”

MANDATORY implies that archiving to this destination must complete successfully before an online redo log file can be overwritten.

OPTIONAL implies that an online redo log file can be reused even if it has not been successfully archived to this destination. This is default.

REOPEN defines whether archiving to a destination must be reattempted inc ase of failure.The default is 300 seconds.If REOPEN is not specified ,errors at optional destination are recorded and ignored.

Log_archive_dest_state_1= DEFER

To stop arching to a mandatory location temporarily when an error has occurred ,the state of that destination can be set to DEFER.
To enable it

Log_archive_dest_state_1= ENABLE

Creating New User in Oracle 9i

example:

CREATE USER user1
IDENTIFIED BY pass
DEFAULT TABLESPACE mytab
TEMPORARY TABLESPACE temp1
QUOTA 10M ON mytab
QUOTA 5M ON temp1
PASSWORD EXPIRE;

where:

user1: name of the user
BY pass:pass is set as password
DEFAULT TABLESPACE:set the tablespace where segment that are created by the user are stored
QUOTA: Defines the max space allowed for objects owned by the user in the tablespace.
PASSWORD EXPIRE : Forces the user to reset the password when the user logs on to the database.

Changing User Quota

ALTER USER user1
QUOTA 0 ON mytab;

Dropping a User

DROP USER user1

Use the CASCADE clause to drop all objects in the chema if the schema contains objects.

DROP USER user1 CASCADE

User Information

to obtain user information query following views

A) DBA_USERS
B) DBA_TS_QUOTAS

Creating Views

View
A view is a logical table based on a table or another view.A view contains no data of its own but is like a window through which data from a tables can be viewed or changed.The tables on which a view is based are called base tables.


Advantages of Views
1) To restrict data access
2) To make complex queries easy
3) To provide data independence
4) To present different views of the same data

Creating a View
create [or replace][FORCE|NOFORCE] VIEW view
[(alias[, alias....)]
As subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE re-create the view if it already exits
FORCE creates the view regardless of whether or not the base table exist
NOFORCE creates the view only if the base tables exist(This is default)
view is the name of the view
WITH CHECK OPTION specifies that only rows accessible to the view can be inserted or updated
WITH READ ONLY ensures that no DML operation can be performed on this view

creating a view
create view myview
as select emp_id,emp_name,emp_sal
from employee
where dept_id=10

Retrieving Data from a View

select * from myview

Data Access Using Views

1)It retrieves the view definition from the data dictionary table USER_VIEWS.
2)It checks access privileges for the view base table.

Rules for Performing DML Operations on a View
1) You can perform DML operations on simple views.

2) You can not remove a row f the view contains the following:
a) GROUP fucntions
b) A GROUP BY clause
c) The DISTINCT keyword
d) The pseudo column ROWNUM keyword

3) You can not modify data in a view if it contains:
a) GROUP functions
b) A GROUP BY clause
c) The DISTINCT keyword
d) The pseudocolumn ROWNUM keyword
e) Columns defined by expressions

4) You can not add data through a view if the view includes:
a) Group functions
b) A GROUP By clause
c) The DISTINCT keyword
d) The pseudocolumn ROWNUM keyword
e) Columns defined by expressions
f) NOT NULL columns in the base tables that are not selected by the view.

5) WITH CHECK Option

The WITH CHECK OPTION clause specifies that INSERTS and UPDATES performed through the view can not create rows which the view can not select and therefore it allows integrity constraints and data validation check to be enforced on data being inserted or updated.

6)Denying DML Operations
You can ensure that no DML operations occur on your view by creating it WITH READ ONLY option

6)Removing a View

DROP VIEW view;

DROP View myview;