USER MANAGEMENT

                                 USER MANAGEMENT
we have 3 types of users.
>end users
>developers
>DBA's
End users are the real users who access the business.
developers develop the project.
DBA's ae administrative the entire database.
SQL>select username from dba_users;
how many users are there in the database will display
when we create a database oracle automatically create default users inside a database.
outof all users sys user is sysdba and remaining users are developers.
syntax to create non default users;

SQL>create user username identified by password;

SQL>select username,password from dba_users;

NOTE:oracle always store the password in encrypted format.

SQL>selectuser_id,account_status,default_tablespace,temporary_tablespace from dba_users where  username='username';

even though user exist in the database he require CREATE SESSION privilege to connect to the database.

SQL>grnat create session to username;

when user account locked the ora -28000 error will came that time you can unlock the account
SQL>alter user username account unlock;
we can also lock the account 
SQL>alter user username account lock;

Particular user to assign  a tablespace
SQL>alter user username default tablesapce tablesapcename;
this is for creating any tables,indexes any other objects will create in the tablespace only.
SQL>alter user username temporary tablespace tablespacename;

SQL>grant create table to username;
this previlige to create table to user
particular user want to some quota in the tablespace.
SQL>alter user username quota 5m on tablespacename;

SQL>  select username,tablespace_name,bytes/(1024*1024)"MB",max_bytes/(1024*1024)"maxbytes" from dba_ts_quotas where username =username;


                        QUOTA MANAGEMENT
                              ---------------------
Using this quotas concept we can resrict a user upto some maximum memory inside a tablespace.
SQL>alter user username quota 10m on tablespacename;
SQL>alter user username quota unlimited on tablespace;
Single user can have quotas on multiple tablespaces


                       PROFILE MANAGEMENT
                                                  
Every user inside a database will have it's own profile.
when we create a database oracle will autimatically create a profile is DEFAULT.
This profile is assigned to every user by default if there is no profile explicitly assign.
SQL>select username,profile from dba_users;
SQL>select distinct(profile) from dba_proifiles;

PROFILE
------------------------------
MONITORING_PROFILE
DEFAULT

syntax to create user defined profile
SQL>create profile profilename
limit
sessions_per_user 3;

Assign profile to user
SQL>alter user username profile profile name;
altering the profile also
SQL>alter profile profile name 
limit
failed_login_attempts;
SQL>select  * from dba_profiles where profile='P1';
we can drop profile also 
SQL>drop profile profile name cascade;
when we drop the assigned profile than automatically assign the default profile.

                         PRIVILEGE MANAGEMENT
privilege is the permission to do task inside the database;
privileges are defined by oracle we cannot create or destroy the privilege.
minimum require to connect the database CREATE SESSION privilege.
By default  SYS user had all privileges provided by oracle
privileges are two types
1>system level privilege
2>object level privilege

>system level privilege;
using this privilege we can impact the entire system database.
All DDL operations comes under this privilege i.e create,aleter,rename,drop,truncate
these are again two types
system privileges:
sysdba,sysoper are very powerful system privileges
when we grnat sysdba privilege to any user that user becomes as sys user .he can do any activity in database without anyother privilege.
To grant this sysdba previlege database requires password file.
sysoper previlege is same as sysdba but user who is having sysoper privilege cannot perform destructive tasks like drop database/tbs/role etc.
object level previleges:
we cannot operate at object level privileges.

                               OBJECT LEVEL PRIVILEGES;
All DML,DRL operations come under this privilege
SQL>select  * from session_privs;

                                    ROLE MANAGEMENT
to make privilege management easy we use roles
role is collection of one or more privileges.
when we  create database time oracle create few roles these are default roles.
default and non default roles 
default roles are connect,resource,export_full_database,recovery_catalog_owner etc
Nondefault roles:
these roles are created by user sys
syntax;
SQL>create role rolename;
particular user having roles

SQL>select username,default_role,granted_role from user_role_privs;

Basic startup stages are:
1> startup nomount
2> startup mount
3> startup open

A part from the basic stages we also have some other option (or)  staging stages like
startup quiet
startup pfile
startup upgrade
startup downgrade
startup migrate
startup restrict
startup readonly
startup mount exclusive restrict.
------------------------------
STARTUP NOMOUNT:
------------------------------
To move to startup nomount if require parameter file.
In default location  $ORACLE_HOME/dbs
This stage is used to create a instance.
If oracle doesn't find parameter file in default location it is 

throughs an ORA-109 ERROR.
------------------------------
STARTUP MOUNT:
------------------------------
To move startup mount state we require 2 files 
parameter file and controlfile.
this stage is used to mount the database.
mounting means identifying the location of datafiles and 
Redologfiles and associating those files to the corresponding the  instance.
If don't find controlfile in default location oracle throughs an
ORA-205 ERROR.
This stage is used to perform maintain tasks.
like placing database in archivelog mode.
placing database in flashback mode
The default location of the controlfile will be decided the 
parameter DB_CREATE_FILE_DEST
then we loss controlfile to recover the controlfile we need to 
standing nomount state and repairing the controlfile also.

------------------------------
STARTUP OPEN:
------------------------------
When we say startup open oracle opens the  datafiles and 
Redologfiles.As  per the specified on controlfile.
When we loss the datafiles,Redologfiles we can perform restore
and recovery we standing in startup mount stage.
In startup open state database should be completely open for 
end user access purpose.

                                   SHUTDOWN STAGES
In different ways we can shutdown database. they are
1>shut normal
2>shut transactional
3>shut immediate
4>shut abort

------------------------
SHUT NORMAL:
--------------------------
When we issue shutnormal command oracle never down the 
database  untill and unless all users disconnected by themself.
Oracle never forces in any user to disconnect  in this case.
Before closing a database oracle performs a proper checkpoint.
once you issue shut normal command no new user is allowed 
in database.After performing the checkpoint oracle closes 
datafiles,redologfiles,controlfiles serially.

--------------------------------
SHUT TRANSACTIONAL:
---------------------------------------
When we issue shut transactional to shutdown the database
oracle immediately disconnects idle users  and still it waits for 
the active users either commit or rollback the transaction.
once they transaction is over oracle immediately disconnects 
these users as well.
like shutnormal before shutdown the database oracle performs 
a proper checkpoint.
Once we issue transactional no new end user is allowed into the
database.oracle closes all datafiles,Redologfiles,controlfiles serially

---------------------------------
SHUT IMMEDIATE:
------------------------------
This command used to shut down the database immediately
by disconnecting the idle users and rollback active user transactions.
In this case oracle forces all the connected users to disconnected  immediately.
once we shut immediate issue command no new end user allow 
to enter.
Before closing a database oracle performs proper checkpoint at 
end it updates same checkpoint scn number all the datafiles  
header and controlfiles.
--------------------------------
SHUT ABORT:
------------------------
When above 3 methods doesn't work a down database then we
need to use shut abort 
This is not recommended way to down the database.
when we issue this command oracle immediately clears the 
instance without performing a proper checkpoint and downs
the database without closing any or the database files.
Not performing a proper checkpoint prior to shutdown the 
database in the next startup oracle requires instance recovery.
Instance reovery will automatically done by the SMON process.
while opening a database next time smon background process 
compares START_SCN value with STOP_SCN value stored in 
controlfile 
They are not same the smon understands that previous shutdown 
was not a normal shutdown (or) abnormal shut down
It inciates instance recovery using current Redologfiles.