Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Wednesday, June 14, 2017

Data Dictionary views and V$ views(dynamic View)

Data Dictionary views:
Data will not be lost even after instance is shutdowned
Will be accessible only if instance is OPENED
Data dictionary view names are plural

V$ views(dynamic View):
Data will be lost if instance is shutdowned
(some are) Will be accessible even if instance is in mount or nomount stage (STARTED)
V$ view names are singular

SYS, SYSDBA, SYSOPER and SYSTEM

sys and system are "real schemas", there are the default user.
Both automatically created on database creation and granted the DBA role.
SYS super user and have full control of the database and have two default role sysdba and sysoper.
SYS is like root for us. It holds the data dictionary, it is special (it physically works differently from other accounts - no flashback query for it, no read only transactions, no triggers, etc)
SYSTEM is our DBA account, it is just a normal user.
Data dictionary can be changed by sys but not with the system.
sysdba and sysoper are ROLES - they are not users, not schemas.
The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
sysoper is another role, if you connect as sysoper, you'll be in a schema "public" and will only be able to do things granted to public AND start/stop the database.
sysoper is something you should use to startup and shutdown. You'll use sysoper much more often than sysdba.

*Role> means authorization to do something. It is bunch of previledges.

difference between Role & Privilage:
Privileges control the ability to run SQL statements. A role is a group of privileges. Granting a role to a user gives them the privileges contained in the role.

A privilege is a right to execute an SQL statement or to access another user's object. In Oracle, there are two types of privileges: system privileges and object privileges.
A privileges can be assigned to a user or a role

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.
The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters.

If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE
MEMORY_TARGET & MEMORY_MAX_TARGET

SGA and PGA can manage together rather than managing them separately.

If SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET is set to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.(But sum of SGA_TARGET and PGA_AGGREGATE_TARGET should be less than or equal to MEMORY_TARGET).
SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).