Update statement interacting with an oracle database

Oracle User Management

Creating a User:-

CREATE USER <username> IDENTIFIED BY <password>;



Change the password of already created user:-

ALTER USER <username> IDENTIFIED BY <password>;



View Created User Information:-

SELECT username, password, created FROM dba_users WHERE username='<username>';


Give user a Default access to particular tablespace during user creation time:-

CREATE USER gaurav identified BY gaurav@1 DEFAULT TABLESPACE temp1;


View the Default Tablespace to a particular user:-

SELECT username, default_tablespace FROM dba_users WHERE username='GAURAV';



Give a default tablespace already created user:-

ALTER USER gaurav DEFAULT TABLESPACE temp1;



Include Access to a Temporary Tablespace during user creation time:-

CREATE USER gaurav IDENTIFIED BY gaurav@1 TEMPORARY TABLESPACE temp;



Include Access to a Temporary Tablespace already  created user:-

ALTER USER gaurav TEMPORARY TABLESPACE temp;

Include Quota On Tablespaces during user creation time:-

CREATE USER gaurav IDENTIFIED by gaurav@1 DEFAULT TABLESPACE user TEMPORARY TABLESPACE temp QUOTA 5M ON users;

 View Access in a particular Tablespace information for a particular user:-

SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas WHERE username='GAURAV';

Give Tablespace Quota to a already created user:-

ALTER USER gaurav QUOTA 3M ON users;

Assign profile to a user during creation time:

CREATE USER gaurav IDENTIFIED BY gaurav@1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M ON users PROFILE DEFAULT;


Provide Profile to a already created user:-

ALTER USER gaurav PROFILE monitoring_profile;



View Assign Profile name of a particular user:-

SELECT username, profile FROM dba_users WHERE username='GAURAV';

Use of EXPIRE keyword:-

CREATE USER gaurav IDENTIFIED BY gaurav@1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M on users PROFILE DEFAULT PASSWORD EXPIRE;

NOTE - PASSWORD EXPIRE: forces the user to reset the password in the first time login.


View Account Status and Expiry date of users:-

SELECT username, expiry_date, account_status FROM dba_users WHERE username='GAURAV';

Drop User:-

DROP USER GAURAV;


Drop User with Objects:-

DROP USER gaurav CASCADE;

Note: Current Connected User Can Not be dropped


LOCK a User Account:

ALTER USER gaurav ACCOUNT lock;



UNLOCK a User Account:

ALTER USER gaurav ACCOUNT unlock;

Database used Size and Free space

select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, 
(select sum(bytes) as p from dba_free_space) free
group by free.p;

RMAN STATUS

SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

Check RMAN backup status complete

set pagesize 200
set linesize 200

select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

Large Table of Oracle Database

col owner for a15
col SEGMENT_NAME for a35
col SizeGB for 999,999,999 heading "Size GB"
set linesize 132
set pagesize 200
set feedback off
break on report
col fn new_value filename
compute sum of SizeGB on report
select '/home/oracle/scripts/out/large_tables_report_'||to_char(sysdate, 'yyyymmdd')||'.log' as fn from dual;
spool &filename
select owner,segment_type,segment_name,bytes/1024/1024/1024 SizeGB
from dba_segments
where segment_type='TABLE' and
bytes/1024/1024/1024 > 1
order by 1,4 desc
/
spool off
set feedback on
exit

Large Index Of Table in Oracle

col owner for a15
col SEGMENT_NAME for a35
col SizeGB for 999,999,999 heading "Size GB"
set linesize 132
set pagesize 200
set feedback off
break on report
col fn new_value filename
compute sum of SizeGB on report
select '/home/oracle/scripts/out/large_tables_report_'||to_char(sysdate, 'yyyymmdd')||'.log' as fn from dual;
spool &filename
select owner,segment_type,segment_name,bytes/1024/1024/1024 SizeGB
from dba_segments
where segment_type='INDEX' and
bytes/1024/1024/1024 > 1
order by 1,4 desc
/
spool off
set feedback on
exit