7. tablespaces ,Users ,, profile

LOGICAL

Under logical part > 1. Oracle data block > oracle is an application which came with own sources,records they generated oracle data block.

2. Extends > combination of multiple oracle data block forms Extends.

3 . Segments> combination of multiple extends forms segment.

4. tablespace> combination of multiple segments forms tablespace.

Inside the tablespace we the datafile

5.Database > multiple tablespace combined together forms a database.

.

PHYSICAL

under physical part > 1. Os data block > which stored data in hard disk . os is responsible for writing into disk.

2.Datafile > this is in tablespace. datafiles are system01.dbf , sysaux01.dbf,undotbs01.dbf,user01.dbf,temp01.dbf.

Datafiles in Tablespace

create a new tablespace and create another datafile on that same tablespace

command is create tablespace dammy datafile '/u01/app/oracle/oradata/PROD/dammy01.dbf' size 30M autoextend on maxsize 200M;

command .[ alter tablespace dammy add datafile '/u01/app/oracle/oradata/PROD/dammy02.dbf' size 20M autoextend on next 10M maxsize 300M; ]

to drop a tablespace command is

drop tablespace dammy including contents and datafiles;

We can create a datafile with autoextend off with a same tablespace

and resize the datafile.

Size of database view

we can offline and online the tablespace status by [ alter tablespace (tablespace name) online/offline ; ]

Which tablespace can be offline or online

system tablespace cannot be offline

sysaux tablespace can be offline and online

undotbs1 tablespace is default cannot be offline

users tablespace can be offline and online

demo tablespace can offline and online

here undotbs3 is not default undo tablespace that is why its offline / online

big tablespace can be offline and online also

.

UNDO , TEMP , BIG tablespaces

UNDO TABLESPACE

default undo tablespace

a new undo tablespace make it default

## . ## TEMP TABLESPACE ### default temp tablespace

create a new temp table space and make it default

make a temp tablespace default command

alter database default temporary tablespace temp04;

Big file tablespace

to check the big file tablespace

create new big file tablespace

drop a datafile ( if in a tablespace have more than one datafiles then datafile can be delete),, ( if in a tablespace have one datafile then cannot be delete then whole tablespace have to delete)

here demo tablespace have 3 datafiles and father tablesapce have 1 datafile . let delete a datafile and see what happend.

.

USERS>

Q. If we want to know in my database who and how many users are there.

view is -> select username from dba_users;

Q. if we want to know which user in which tablespace

select username,default_tablespace from dba_users;

Q. want create a new user.

create user ram identified by oracle ;

Q . tablespace we have

Q . suppose i want to create a user inside a tablespace(cars).

create user sam identified by oracle default tablespace cars;

view it == select username,default_tablespace from dba_users;

Q. i want to login using ram user how to do. give the user permission or privilege for login.

so how to give login permission . only sys user grant login permission to any user . if sys user give admin option to a user. that user only can grant login permission to other users.

> grant create session , create table to ram;

view what privilege have ram

privilege are 2 types = 1. system privilege 2. object privilege.

here [ grant create session , create table ] is system privilege

now create a table

now if we want insert data in t2 table . i don't have privilege to write something for ram user.

to give privilege for write

alter user ram quota 10M on users; ( means i have given 10MB space in users tablespace to ram for keeping his data )

now if we connect as ram we insert value in that table

then commint;

Q. i want remove the session create and table create permissions from ram

revoke create session from ram;

Q. view for all the system privileges.

desc system_privilege_map

Q. i want to get the table of HR user when i login with ram user.

so it give me error . how to get that hr user table.

we give the access of hr user table to ram.

Object privileges are [ select ,update , insert , delete privileges ]

grant object privilege to ram

grant select hr.employee to ram;

login as sys user

Q. if a user sam want to access and insert values to ram user table t2 . how to do.

conn as sys user, grant create session privilege to sam.

grant select insert update these obkect on ram 's table privileges to sam. and view it

Q. want to know what are the object privilege have sam user.

view == desc dba_tab_privs

Q. i want to revoke insert and update object privilege from sam.

Q. drop a user.

drop user sam cascade;

Q. i want to create a user to a specific tablespace with qouta.

create user raja identified by oracle default tablespace cars quota unlimited on cars;

Q. if ram user wants to grant create session to another user.

as a DBA how to do

grant create session to ram with admin option;

Q1. A new user like leon. HR have whatever system privileges and object privileges grant those system privileges and object privileges to that user.

In that @/home/oracle/leon_all.sql file we manually write a script for " HR have whatever system privileges and object privileges grant those system privileges and object privileges to that user. "

this the file locations

this is the script

this is the output

Profile

Profile are limited on kernel and password.

view for profile

desc dba_profiles

select profile,resource_name,resource_type,limit from dba_profiles where profile like 'DEFAULT';

password

1. password_life_time - in bank cases - we need to reset the password after 90 days or 180 days or 120 days.

2. password_reuse_time - you can reuse the password as many time as you want (unlimited) but in some bank app - theres a rule- you can't use your last or previous 5 passwords. if i set password for reuse time 30 days . i can't use same passwords within 30 days.

3 . password_reuse_man - how many times i use the set passswords - let last 5 passwords i can reuse.

if i set password reuse time =30 password reuse max = 5 that means within 30 days last 5 passwords i cannot set now your have to set a new password after 30 days you set a any password

4. password_verify_function - set a password - 'oracle' this is not authenticated way of setting password . there are a rule -

min char - 8 , one char - capital , one number and one special char should be there.

5 . password_lock_time - assume your acct get locked for 1. so now your account remaim lock for specified days after that days your account unlock.

6. password_grace_time - assume 180 dasy set password . but you have not set the password within 180 days . you will have 7 days of grace time - within 7 days reset the password if you will not reset the password -then your acct get locked.

7 . failed_login_attmepts - let it set 3 . if put 3 times wrong password -account get locked.

8. session_per_user - let hr user ,any user - we can open multiple session or tab , every tab open with hr user. if it set -1 user 1 session can create only.

kernel - kernel is the interface between the device OS and hardware(drives). all drives and os are managed by kernel layer.

9 . cpu_per_call and cpu_per_session- whenever you connect to database here / in session some of cpu is been utilized that is set - users are connect so how much cpu limit is allowed per sql call. if more than cpu use the sql will killed.

10 . logical_reads_per_session - here we are set to limiting number of datablock to be read per session.

let 50 users connected, one user do lot of heavy sqls quaries so lot data blocks are come into database buffer cache , other users not get not much of response , server is busy with that user who run heavy quaries.

11. logical_reads_per_call - how many blocks you can read per sql call means execute the sql.

12. idle_time - a user login but do nothing - but you have block some resources

users are full , some users do nothing but they login and idle , one new user try to connect so we can set idle time 2 mints those idle users kill automatically

13. connect_time - set maxtime - 1 . one user can connect 1 hour , after one hour this user disconnect automatically.

create a profile

create profile (profilename)

SQL> create profile prof

2 limit

3 PASSWORD_LIFE_TIME 90

4 PASSWORD_REUSE_TIME 360

5 PASSWORD_REUSE_MAX 5

6 PASSWORD_VERIFY_FUNCTION verify_function_11G

7 PASSWORD_GRACE_TIME 3

8 IDLE_TIME 5

9 SESSIONS_PER_USER 1

10 CONNECT_TIME 60;