How to change archive to no archive-log Mode
shutdown db and follow below steps
>startup mount;
>alter database noarchivelog;
>alter databse open;
select log_mode from v$database;
tablespace growth history
SELECT TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’) days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024*1024),2) ) cur_size_GB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) usedsize_GB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN (‘SYSAUX’,’SYSTEM’)
and sp.begin_interval_time > sysdate -16
and ts.tsname=’PSAPSR3′
GROUP BY TO_CHAR (sp.begin_interval_time,’DD-MM-YYYY’), ts.tsname
ORDER BY ts.tsname, days;
list out Active session in db
SELECT a.sid,a.serial#, s.sql_text,a.process–, b.used_urec, b.used_ublk
FROM gv$session a, gv$sql s
WHERE
a.inst_id = s.inst_id and
a.sql_id=s.sql_id and a.status=’ACTIVE’
and s.sql_text not like ‘SELECT a.sid,a.serial#, s.sql_text%’;
To check lock in database
select
oracle_username
os_user_name,
a.OBJECT_ID,
SESSION_ID,
locked_mode,
object_name,
PROCESS,
object_type from
v$locked_object a,dba_objects b
where a.object_id = b.object_id;