Docstoc

Oracle Architecture components_Ramcosystems

Document Sample
Oracle Architecture components_Ramcosystems Powered By Docstoc
					ORACLE 9i ARCHITECTURE
           Oracle Architecture
                      Oracle



    Oracle Instance                Oracle Database


 SGA                                  Logical Components
 Background Processes                  Physical Components




                      ORACLE 9i Architecture                   2
               Oracle Instance
   Redo Log            Database                      Shared Pool
    Buffer            Buffer cache
                                                  Library cache


                                                 Data Dictionary cache



              Java     Large                       User Global Area
               Pool
                        Pool

DBWR    LGWR          PMON           SMON           CKPT           ARCn
                        ORACLE 9i Architecture                        3
 System Global Area


• Database Buffer Cache
• Shared Pool
   • Library cache
   •Data Dictionary cache
• Redo log Buffer
• Large pool
•Java Pool


                      ORACLE 9i Architecture   4
          Database Buffer Cache


Portion of the SGA that caches the most recently used data blocks
Each buffer in the cache corresponds to and holds the size of one
block
Blocks cache may be of the following segment type
  Table
  Index
  Clusters
  LOB
  LOB indexes
  Rollback segments
  Temporary Segments


                        ORACLE 9i Architecture                      5
            DB Buffer Cache


Contents categorized as
  Dirty buffers
     Contains blocks that have been dirtied and not yet written
      to the database
 Free buffers
     Buffers that have not yet been used
 Pinned buffers
     Buffers that are currently being used (also known as the
      KEEP pool)



                        ORACLE 9i Architecture                   6
             DB buffer Cache


What are dirty buffers ?
  Initially contents of blocks in buffer and disk are same
  Dirty buffers  copy in the buffer cache does not
   match with the copy on the disk
  Buffers are dirtied by the INSERT, UPDATE, DELETE
   statements
Overwriting the buffers
  Oracle server can overwrite a FREE buffer with the
   block that is read from the disk
  Dirty blocks cannot be overwritten

                      ORACLE 9i Architecture                  7
         Database Buffer Cache


Organization
  Write List
  LRU List
     Fee Buffers
     Pinned Buffers
     Dirty Buffers
LRU algorithm
  Old buffers are aged out
  LRU list has the MRU end and the LRU end


                       ORACLE 9i Architecture   8
            Db buffer cache


What is a Cache Hit and a Cache Miss ?
The LRU Algorithm and Full Table Scans
Sizing the buffer cache
  DB_CACHE_SIZE
Buffer cache Advisory
  Feature that estimates the changes in buffer cache if
    the size were increased / decreased
  DB_CACHE_ADIVCE parameter
  V$DB_CACHE_ADVICE dynamic performance view


                    ORACLE 9i Architecture                 9
          Buffer cache advisory

       Output of V$DB_CACHE_ADVICE
Name      Size_for_estimate      estd_physical_reads
DEFAULT              10.1798                      7896
DEFAULT              20.4541                      6614
DEFAULT              30.1231                      6028
DEFAULT              40.7188                      5785
DEFAULT              50.3213                      5342
DEFAULT              61.2834                      4915
DEFAULT              71.3213                      4511




                     ORACLE 9i Architecture              10
            LRU list




 A             A

MRU                                         LRU




  Server           Blocks on disk
  Process
                   A                    C
                            B

               ORACLE 9i Architecture             11
           Redo Log Buffer


Contents Of the redo log buffer
  Recods all the changes made to the database
Purpose
  Contents used for recover




                   ORACLE 9i Architecture        12
                Shared Pool


Shared pool caches recently used SQL and PL/SQL Statements
Managed by the LRU algorithm
Components
  Library Cache
     SQL Area
     PL/SQL area
  Data Dictionary Cache
    Contains information about DB objects
  User Global area
    Present in a shared server configuration
    Contains users session information
                      ORACLE 9i Architecture                 13
               Shared Pool


Statement Caching
  SQL Command is run
  Oracle converts the characters into ASCII
  ASCII value passed through a hashing algorithm to
   produce a single hashed value
  Hash value compared with value existing in the shared
   pool
     If a match exists , cached version of SQL Statement used
     Else Server process begins to parse the statement and
      then execute it

                        ORACLE 9i Architecture                14
              Shared pool


Parsing of SQL statement adds more overhead
Steps in parsing an SQL statement
  Check for syntax
  Object resolution (Names and structures checked
   against the data dictionary)
  Gather statistics on the objects
  Prepare execution plan
  Determine the security of the objects
  Generated the compiled version of statement (P-code)


                   ORACLE 9i Architecture                 15
              Shared Pool


Cache hit
  Finding an SQL statement in shared pool
Cache Miss
  SQL statement does not exist in the Shared pool
Size determined by SHARED_POOL_SIZE




                   ORACLE 9i Architecture            16
             Library Cache


SQL and PL/SQL statements are cached
Contents of shared pool
  Actual text of the statement
  Hashed value associated with the statement
  P-Code for the statement
  Statistics associated with the statement
  Execution plan for the statement




                   ORACLE 9i Architecture       17
                 Library Cache




Shared                          Private
SQL Area                        SQL Area

Parse Tree                Persistent area
Execution Plan            Run time area




                              Only in case of
                              Shared server configuration
                      ORACLE 9i Architecture                18
           Private SQL areas


Contains private information and runtime buffers
Each session has a private SQL area
Parts of Private SQL areas
  Persistent area
     Bind variables
 Runtime area
     Information required during the execution of SQL
      Statements




                       ORACLE 9i Architecture            19
      Dynamic performance views


V$SQL
  Sql text , statistics , memory usage etc
V$SQLAREA
  Similar to V$SQL
  Displays only statements that are parsed and ready for
   execution
V$SQLTEXT
  Sql text and command type
V$SQL_PLAN
  Execution plan for each sql statement
                    ORACLE 9i Architecture              20
         Data dictionary cache


Contains data dictionary information
Data dictionary information is needed for object resolution
Frequently used object resolution information are stored
here
Managed by LRU mechanism
LRU for library cache and Data dictionary cache operate
separately
Data dictionary information is retained longer even if the
corresponding SQL statements are not retained


                    ORACLE 9i Architecture                21
            User global area


Present in the shared pool only if the shared server is
configured
Contains the user session information
In a non shared or dedicated server environment this
information is stored in the PGA of the users session




                     ORACLE 9i Architecture               22
               Large Pool


Session memory for the shared server and the Oracle XA
interface (used where transactions interact with more than
one database)
Memory required for the I/O slave processes of DBWR
Oracle backup and restore operations
Messaging information for parallel query option if
PARALLEL_AUTOMATIC_TUNING is set to TRUE
(otherwise, these buffers are allocated to the shared pool)




                    ORACLE 9i Architecture                23
        Large pool information


Size determined by LARGE_POOL_SIZE
Get information about the large pool
  Select name,bytes from v$sgastat where pool =„large
   pool‟;




                   ORACLE 9i Architecture                24
               Java Pool


Contains the compiled versions of the Java Program units
Optional area of memory




                   ORACLE 9i Architecture              25
         Program Global Area


PGA is a non shared area
Allocated for each server process
Contents of PGA
  Stack space
     Used to hold session variables, arrays etc
 Session information
     Information about the users session ( Private SQL
      area)




                     ORACLE 9i Architecture               26
        Background Processes


DBWR
  When DBWR Writes
LGWR
  When LGWR Writes
SMON
  Performs Instance Recovery
PMON
  Performs Recovery of Failed User Processes



                  ORACLE 9i Architecture        27
        Background Processes


CKPT
  headers of all datafiles are updated to record the details
   of the checkpoint.
  Signals the DBWn to write the dirty blocks to the disk




                     ORACLE 9i Architecture                28
                  DBWR


A database Writer writes modified/Dirty blocks to the disk



DB Buffer
cache                     DBWR


                                             Data Files




                    ORACLE 9i Architecture                   29
               DBWR (Contd)


DBWR Writes to the data files when one of the following events occur
  Checkpoint
  Tablespace is taken Offline
  When the no free blocks are found
  When dirty buffers reach the threshold
  When a tablespace is taken offline
  When a tablespace space is taken to the readonly mode
  When the tablespace is put in the backup mode

      SQL> alter tablespace <name> begin
       backup;

                        ORACLE 9i Architecture                         30
               Log Writer


Copies the contents of the Redo log buffer to online redo
log files


Redo Log
Buffer                    LGWR


                                             Online Redo
                                             log Files




                    ORACLE 9i Architecture                  31
            LGWR (Contd…)


LGWR Writes to the online redo log files when ,
  A commit command is issued
  When log buffer gets one third full
  When the log buffer becomes 1 MB full
  Every time before a DBWR writes to datafile, log writer
   writes to log files




                    ORACLE 9i Architecture                   32
                  Checkpoint
Frequency of checkpoint can be increased to influence the time taken
for recovery
FAST_START_MTTR_TARGET parameter
A Checkpoint Occurs During
  Log Switch
  When the system is shutdown using the options,
      Normal
      Transactional
     immediate
  When the DBA Forces a checkpoint by the command
     SQL> Alter system checkpoint;
  When a tablespace is taken offline or to the read only mode
  Depending upon the value that is set by the
   FAST_START_MTTR_TARGET parameter
                        ORACLE 9i Architecture                         33
                SMON

System Monitor is responsible for performing Instance
Recovery
Recovery is performed in 3 steps
  Roll forward
  Open the database
  Rollback
SMON is also responsible for cleaning up temporary
segments that are no longer in use
coalescing contiguous free extents within dictionary-
managed tablespaces.
SMON wakes up regularly to check whether it is needed.
                  ORACLE 9i Architecture                 34
                 SMON


Other processes can call SMON if they detect a need for
SMON to wake up.




                    ORACLE 9i Architecture                35
                 PMON


performs process recovery when a user process fails
PMON periodically checks the status of dispatcher and
server processes, and restarts any that have died
PMON also registers information about the instance and
dispatcher processes with the network listener.
Like SMON, PMON wakes up regularly to check whether it
is needed and can be called if another process detects the
need for it.




                    ORACLE 9i Architecture               36
         Other Processes


               Processes



                                   Background Process
User Process


                Server Process




                  ORACLE 9i Architecture                37
               User Processes


    When a user runs an application program (such as a
    pro*c program) or an oracle tool (such as oracle
    enterprise manager or SQL*plus), oracle creates a user
    process to run the user's application
    User Process communicates with server process with
    the help of a dispatcher


User Process          Dispatcher              Server Process




                     ORACLE 9i Architecture                    38
              Server Process


Oracle creates server processes to handle the requests of user
processes connected to the instance.
In some situations when the application and Oracle operate on
the same machine, it is possible to combine the user process
and corresponding server process into a single process to
reduce system overhead.
However, when the application and Oracle operate on different
machines, a user process always communicates with Oracle
through a separate server process.
Server processes (or the server portion of combined user/server
processes) created on behalf of each user's application can
perform one or more of the following:
                     ORACLE 9i Architecture                   39
             Server Process


Parse and execute SQL statements issued through the
application
Read necessary data blocks from datafiles on disk into the
shared database buffers of the SGA, if the blocks are not
already present in the SGA
Return results in such a way that the application can
process the information




                    ORACLE 9i Architecture               40
       An Example of How Oracle Works


1. Client runs the application in the user process
2. Client attempts connection to server using Oracle Net
   Services
3. Server creates a dedicated server process for client
4. User process executes the SQL




                       ORACLE 9i Architecture              41
        An Example of How Oracle Works


5.   Server executes the SQL
6.   Updates the SGA
7.   DBWn writes the data when appropriate
8.   LGWn writes the modifications to the online redo log file
9.   If transaction is a success, Success message is sent to
     client
       1. Else error message is sent




                         ORACLE 9i Architecture                  42
       An Example of How Oracle Works


10. Other background processes run, watching for conditions
    that require intervention.
11. Database server prevents contention between transactions
    that use the same data




                       ORACLE 9i Architecture              43
    Logical Structure of Oracle DB


Database
  Tablespace
    Segments
         Extents
               Data Blocks




                      ORACLE 9i Architecture   44
             Physical Structure

                 Physical Files



                                        Control Files
    Data Files


                     Online Redo log files




Parameter File     Password File                Archived Logfile
                       ORACLE 9i Architecture                      45
               Control Files

Control File Creation
Control File ReCreation
Contents of Control file
Multiplexing of Control Files
Obtaining Control File Information
  V$controlfile
  V$controlfile_record_section
  Show parmeter control_files




                     ORACLE 9i Architecture   46
         Online Redo Log Files


Purpose
Contents
Log groups and members
Multiplexing log files
Adding members and groups
Archiving log files
Obtaining log file information
  V$log
  V$logfile

                    ORACLE 9i Architecture   47
      Tablespaces and Datafiles


Types of tablespaces
  System
  Non System
Others
  Undo Tablespace
  Temporary Tablespace
  User data tablespace
Tablespace creation



                 ORACLE 9i Architecture   48
              Parameters


Types of parameter Files
  Spfile
  pfile
Parameters List




                   ORACLE 9i Architecture   49
Managing the Oracle Instance
                Objectives


Starting up the instance
  Normal mode
  Restricted mode
Parameter files
  Pfile
  Spfile
List of important Oracle Initialization parameters
Shutting down the instance
Database consistency

                     ORACLE 9i Architecture          51
    Starting up the Oracle Instance


Every database is associated with an Oracle Instance
Oracle Instance Consists of
  SGA
  Background process
After starting the instance , it is associated with a
particular database
  Also known as mounting the database
Only the DBA can startup and Shutdown the instance



                    ORACLE 9i Architecture              52
       Starting up the instance


When an instance is started
  Parameter file is processed
  SGA is allocated
  Background process are started
Modes of instance startup
  Nomount
  Mount
  open



                   ORACLE 9i Architecture   53
     Staring up the oracle instance


Nomount state
  SGA is allocated
  Background processes are started
Mount State
  Control file is processed
Open state
  Data files and Online redo log files are open and can be
   accessed
  Open in the Read only mode
  Open in the Read Write mode
                    ORACLE 9i Architecture                54
    Starting up oracle instances


Identify the mode of the instance
  SQL > SELECT STATUS FROM V$INSTANCE




                 ORACLE 9i Architecture   55
Startup the instance in the restricted mode


 Startup the instance in the restricted mode
   SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION
   Prevents the users from logging in
   Only DBA and other users with the “RESTRICTED
    SESSION” privilege can connect
 Switching back to normal mode
   SQL> ALTER SYSTEM DISABLE RESTRICTED
    SESSION;



                    ORACLE 9i Architecture      56
Starting up the instance in restricted mode


 Whether the session is in restricted mode ?
  SQL > SELECT LOGINS FROM V$INSTANCE;
      “ALLOWED” is displayed if the database is in the normal
       mode
      “RESTRICTED” is displayed if the database is in the
       restricted mode




                      ORACLE 9i Architecture                 57
              Parameter files


Contains the initialization parameters
Processed during instance startup
Types of parameter files
  Pfile
     Text file
     Can be directly altered by the administrator
 Spfile
     Binary file
     Cannot be directly altered by the administrator
     Administrator uses the “ALTER SYSTEM …” command to
      change the values of the parameters stored in the spfile

                     ORACLE 9i Architecture                      58
            Parameter files
Altering the value of parameters in the spfile
  ALTER SYSTEM SET <PARAMETER>=<VALUE>
   SCOPE= MEMORY / SPFILE / BOTH SID = „SID‟
Scope values
  MEMORY
    Parameter value changes remain in effect only till the
     instance is restarted
 SPFILE
    Parameter value changes take effect only after the next
     instance startup
    Changes remain persistent across subsequent startup
     and shutdown

                     ORACLE 9i Architecture                    59
             Parameter files


Scope values
  BOTH
     Parameter value changes take effect immediately
     Are persistent across startup and shutdown
Click here




                    ORACLE 9i Architecture              60
         Creating an SPFILE


Following command creates an SPFILE from PFILE
  CREATE SPFILE FROM PFILE;
Parameter files are searched as follows
  spfileSID.ora
  spfile.ora
  initSID.ora
  init.ora




                  ORACLE 9i Architecture         61
    Shutting down Oracle Instance


After a shutdown
  Database is close
  Database in unmounted
  Background process are stopped
  SGA is deallocated
Modes of shutdown
  NORMAL
  IMMEDIATE
  TRANSACTIONAL
  ABORT

                  ORACLE 9i Architecture   62
         Database consistency


NORMAL
  Users cannot log in
  Oracle waits till all the users log out
  A checkpoint is raised
  Data files are synchronized
  During the next startup database is in a consistent state
  Next startup does not requires instance recovery




                    ORACLE 9i Architecture                63
         Database consistency


IMMEDIATE
  Users cannot log in
  Oracle waits for all pending transactions to complete
  A checkpoint is raised
  Data files are synchronized
  During the next startup database is in a consistent state
  Next startup does not requires instance recovery




                    ORACLE 9i Architecture                64
         Database consistency


TRANSACTIONAL
  Users cannot log in
  Oracle rollback the pending transactions
  A checkpoint is raised
  Data files are synchronized
  During the next startup database is in a consistent state
  Next startup does not requires instance recovery




                    ORACLE 9i Architecture                65
        Database Consistency


ABORT
  Users cannot log in
  UnCommitted transactions are not rolled back
  Checkpoint is not raised
  Data files are out-of-sync
  Database is in an inconsistent state
  Next startup requires instance recovery




                   ORACLE 9i Architecture         66
Creating Database
              Objectives


Creating the database
  Manually
  Database Configuration Assistant
Using Oracle Managed Files




                   ORACLE 9i Architecture   68
           Database Creation


Manual Database Creation
Step 1: Create the Database SID
c:\> oradim –new –SID Ramco
Step 2: create the Folder structures as follows
admin
    <SID>
           Bdump
           Cdump
           Udump
           create
           pfile


                    ORACLE 9i Architecture        69
            Creating database


Ora90
Oradata
    <SID>
Step 3: Create the parameter file and save the same as
“init.ora” inside pfile, inside admin folder
Step 4: Create a file “iniSID.ora” and place the same in the
folder ora90\database
  Contents
     ifile=„‟\oracle\admin\<SID>\pfile\init.ora”



                       ORACLE 9i Architecture              70
            Database Creation


Step 5: Create the password file as follows
c:\>orapwd file=\oracle\ora90\database\pwdSID.ora
    password=change
Step 6: Create a .sql file containing the code to create the
database Click here
Step 7: set the oracle_sid environment variable
c:\> set oracle_sid=<SID>
c:\>sqlplus sys/change as sysdba
Step 8: Run the script to create the database
SQL> @create.sql

                     ORACLE 9i Architecture                    71
           Database creation


Step 9: Run the following scripts located in
\oracle\ora90\rdbms\admin
  Catalog.sql
  Catproc.sql
  Catexp.sql




                    ORACLE 9i Architecture     72
Working with Control
       Files
               Objectives


Control file overview
Contents of the control file
Specifying names and locations of the control file
Size of the control file
Multiplexing the control files
  When using spfile
  When using pfile
View the contents of the control file



                    ORACLE 9i Architecture           74
          Control File overview


A Small binary file that is required for the normal operation
of the database
Control file is continuously updated during database use
Contains information required by the instance to access
the database
Control file information is updated by Oracle Server
DBA cannot edit the contents of the control file




                     ORACLE 9i Architecture                 75
              Control file contents


1. The database name
2. The timestamp of database creation
3. The names and locations of associated datafiles and online
   redo log files
4. Tablespace information
5. Datafile information
6. The log history
7. Archived log information
8. Backup set and backup piece information


                       ORACLE 9i Architecture              76
                Control file contents


9.    Backup datafile and redo log information
10.   Datafile copy information
11.   The current log sequence number
12.   Checkpoint information




                          ORACLE 9i Architecture   77
   Name and location of control file

Control_files parameter
  Specifies the names and locations of the control files
  Control files are created during the database creation
  Oracle refers to the parameter to identify the number of
   control files to be created, their names and locations
Size of the control file
  The Initial size of the control file is determined by
     MAXLOGFILES
     MAXLOGMEMBERS
     MAXLOGHISTORY
     MAXDATAFILES
     MAXINSTANCES

                    ORACLE 9i Architecture                78
       Multiplexing control files


Increase the number of control files
Spfile
  SQL> alter system set control_files=
   („e:\oracle\oradata\ramco\control01.ctl‟,
   „e:\oracle\oradata\ramco\control02.ctl‟,
   „e:\oracle\oradata\ramco\control03.ctl‟)
   scope=spfile;
  Shutdown the instance
  Use OS command create additional copies of control
   file
  Startup the instance

                   ORACLE 9i Architecture               79
       Multiplexing control files


When using pfile
 Shutdown the instance
 Make changes to the control_files parameter in the pfile
 Use OS utilities to created additional copies of the
  control file
 Startup the instance




                    ORACLE 9i Architecture               80
  View the contents of the control file


CONTROLF event get the dump of the control file
Creates a tracefile user_dump_dest directory
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME
CONTROLF LEVEL 1';
  This level will dump only the controlfile's file header.
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME
CONTROLF LEVEL 10';
  CONTROLF with level 10 you will get trace that shows
   the entire structure of the controlfile with the relative
   contents.

                    ORACLE 9i Architecture                 81
     Obtain control file information


Use the v$controlfile view
  SQL> select name from v$controlfile;
   NAME
   --------------------------------------------------
   F:\ORACLE\ORADATA\RAMCO\CONTROL01.CTL
   F:\ORACLE\ORADATA\RAMCO\CONTROL02.CTL
   F:\ORACLE\ORADATA\RAMCO\CONTROL03.CTL




                    ORACLE 9i Architecture              82
Working with table spaces
               Objectives


Tablespace
Types of tablespaces
  System
  Undo
  Temporary
  User data
Non standard block sizes for tablespaces
Online and Offline tablespaces



                   ORACLE 9i Architecture   84
              Tablespace


Database divided into logical units called tablespace
Database divided into logical units known as segments
Types of segments in a tablespace
  Table
  Index
  Index organized table
  Partitioned table
  Partitioned index
  Cluster table

                   ORACLE 9i Architecture               85
            Tablespaces


Types of segments
  Rollback segment
  Temporary segment
  LOB segment
  Bootstrap segment




                 ORACLE 9i Architecture   86
         Types of tablespaces


SYSTEM tablespace
  Create during database creation
  Contains the data dictionary
  Standard block size used
  Cannot be taken offline
  Cannot be made read only




                   ORACLE 9i Architecture   87
         Types of tablespaces


Non SYSTEM tablespace
  Undo , Temporary, User data
  Undo and temporary tablespace can be created during
   database creation
  User data tablespace is created




                   ORACLE 9i Architecture                88
            Undo tablespace


Used for storing undo information
Undo tablespace can contain only undo segments
A new transaction is assigned to an undo segment
If instance does not have an undo tablespace, it is bound
to the system undo segments




                    ORACLE 9i Architecture                  89
    Assignment of undo tablespace


Assigned in 2 ways
  At the time of database creation
  Alter the parameter UNDO_TABLESPACE
Get information about the undo blocks generated
  select to_char(begin_time,'dd:mi:ss') begin_time,
   to_char(end_time,'dd:mi:ss') end_time,
   undoblks "Total Blocks“ from v$undostat;




                    ORACLE 9i Architecture             90
         Temporary tablespace


Used for sorting data
DEFAULT TEMPORARY TABLESPACE clause of created
database
Size of the temporary tablespace is to be a multiple of
SORT_AREA_SIZE




                   ORACLE 9i Architecture                 91
          Creating tablespace
Create [ undo ] tablespace <Name>
datafile „<FileName>‟ autoextend on |off next <int> K |M
maxsize <int> K | M | unlimited
Minimum extent <int> K|M
blocksize <int> [ K ]
Logging | Nologging
Online | Offline
Permanent | temporary
default Storage ( INITIAL <int> K | M NEXT <INT> K | M
MINEXTENTS INT MAXEXTENTS INT | UNLIMITED
PCTINCREASE INT
BUFFER_POOL KEEP | RECYCLE | DEFAULT )
extent management dictionary | local
[ autoallocate uniform size int k | M ]
segment space management manual | auto
                    ORACLE 9i Architecture                 92
        Non standard block sizes


If Blocksize clause is not specified, block size is defined by
DB_BLOCK_SIZE parameter
Non standard block sizes can range from 2K to 32 K (in
multiples of 2)
If tablespace created with non default block size,
   Configure the DB_CACHE_SIZE
   Configure atleat one the DB_nK_CACHE_SIZE
     parameter, for every



                     ORACLE 9i Architecture                 93
     Alter tablespace availability


Take the tablespace offline
  Temporarily unavailable
  Use the ALTER TABLESPACE command
ALTER TABLESPACE <NAME> OFFLINE;
ALTER TABLESPACE <NAME> ONLINE;




                  ORACLE 9i Architecture   94
         Dropping tablespace


Drop tablespace <name>;
Drop tablespace <name> including contents;
Drop tablespace <name> including contents and datafiles;




                   ORACLE 9i Architecture              95
Users and Privileges
               Objectives


Creating users
Assigning roles, privileges
Allocating a tablespace to the user
Managing quota on tablespace
Revoking privileges from the user




                    ORACLE 9i Architecture   97
             Creating users


Create user <userName>
identified by password
default tablespace <Name>
quota <int> k|m | unlimited on <tbs>;




                    ORACLE 9i Architecture   98
           privileges to users


Grant <role> / privilege to user
Revoke <role> / <privilege> to user




                    ORACLE 9i Architecture   99
            Altering the user


Alter user <user>
default tablespace <name>
quota int K|M | unlimited on <tbs>;




                    ORACLE 9i Architecture   100

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:68
posted:4/17/2010
language:English
pages:100