How will you find current and
maximum utilization of process/session details?
Using the below SQL you can find current number of process
and session details
Select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes', 'sessions');
Locking
of users:
===================
set linesize 230
col name for a20
alter session set
nls_date_format='dd-mm-yy hh24:mi:ss';
select
NAME,CTIME,PTIME,EXPTIME,LTIME,lcount from user$ where name ='PRODUACT';
CTIME is the date the user was created.
LTIME is the date the user was
last locked. (Note that it doesn't get NULLed when you unlock the user).
PTIME is the date the password was last changed.
select
file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='&tablespace_name';
==================================================
set linesize 150
column file_name format a50
column tablespace_name format a10
select
file_name,bytes/1024/1024 from dba_data_files where
tablespace_name='&tablespace_name';
SELECT tablespace_name, SUM (bytes_used)/1024/1024
"Used in MB", SUM (bytes_free)/1024/1024 "Free in MB"
FROM V$temp_space_header
GROUP BY tablespace_name;
SELECT
a.tablespace_name, a.file_name, a.bytes/1024/1024 allocated_bytes,b.free_bytes FROM
dba_data_files a,
where
tablespace_name='&tablespace_name';
SELECT
referenced_owner,referenced_name,referenced_type FROM DBA_DEPENDENCIES WHERE
OWNER ='ESRVP' AND NAME ='ES_DATAREC';
The indexes on this table
are:
SQL>
select index_name,index_type,uniqueness from dba_indexes where table_name
='ES_DATAREC';
SELECT
table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY
table_name;
-------------------------------------------------------------------------------------------------------------
Startup time of database:
select host_name, instance_name,
to_char(startup_time,'DD-MON-YYYY-HH24:MI:SS') STARTUP_TIME from v$instance;
SELECT
to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time",instance_name
FROM sys.v_$instance;
---------------------------------------------------------------------------------------------------------
Changing the password : c:\esc\oracle\scripts\password_edit
Change the password and check in dbinfo
-g
\\Isean312\r$
select
file_name,file_id,bytes/1024/1024,tablespace_name,autoextensible,maxblocks from
dba_data_files where tablespace_name='PERFSTATD' order by file_name;
Temp:
- select file_name
, TABLESPACE_NAME from DBA_TEMP_FILES;
2:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
3: ALTER DATABASE TEMPFILE
‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;
or
b) Add temp datafile to temporary tablespace as
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE
‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;
.
alter database register logfile
'R:\BKUP01\Oracle\OraArchive\UNNRKM1P\UNNRKM1P_ARC52747_1_658942285.LOG';
select file_name, tablespace_name, bytes/(1024*1024)
"Size MB"
from dba_temp_files;
========================================================================
SELECT tablespace_name, SUM(bytes_used)/1024/1024/1024,
SUM(bytes_free)/1024/1024
FROM v$temp_space_header
GROUP BY tablespace_name;
========================================================================
List of fragmented segments
select segs.*from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
TEMP
=======================================================
SELECT s.sid "SID",
s.username
"User",
s.program
"Program",
u.tablespace "Tablespace",
u.contents
"Contents",
u.extents
"Extents",
u.blocks * 8 / 1024
"Used Space in MB",
q.sql_text "SQL
TEXT",
a.object
"Object",
k.bytes / 1024 / 1024
"Temp File Size"
FROM v$session s,
v$sort_usage u,
v$access a,
dba_temp_files k,
v$sql q
WHERE s.saddr = u.session_addr
AND s.sql_address =
q.address
AND s.sid = a.sid
AND u.tablespace =
k.tablespace_name;
SELECT TABLESPACE_SIZE/1024/1024 tablespace,
allocated_space/1024/1024 usage, free_space/1024/1024 free FROM
dba_temp_free_space;
query to find temp usage:
==========================
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
select tablespace_name,file_id,bytes_used,bytes_free from
v$temp_space_header
1. Identify temp segment usages per
session
–
– Temp segment usage per session.
–
SQL> SELECT S.sid,S.serial# sid_serial,
S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS,
v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
2. Identify temp segment usages per
statement
–
– Temp segment usage per statement.
–
SQL> SELECT S.sid || ‘,’ || S.serial#
sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces
TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus
on problematic session/statement.
----------------------------------------------------------------------------------------------------------------
SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool='LARGE POOL'
GROUP BY ROLLUP(name);
select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
from gv$sort_segment;
Ora-1652
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
select sum(free_blocks) from
gv$sort_segment where tablespace_name =
'<TABLESPACE NAME >
select sum(free_blocks) from
gv$sort_segment where tablespace_name =’TEMP’;
select
sid,serial#,USERNAME,LOGON_TIME,PROGRAM,sql_address from v$session where
status='ACTIVE' order by LOGON_TIME
SELECT segment_name, segment_type, tablespace_name, extents FROM user_segments; SET LINESIZE 100
COLUMN spid FORMAT
A10
COLUMN username
FORMAT A10
COLUMN program
FORMAT A45
SELECT
s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr
AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
Check
for Deadlock
SELECT
holding_session bsession_id, waiting_session wsession_id, b.username busername,
a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1,
lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE
c.holding_session=b.sid and c.waiting_session=a.sid;
Consuming more cpu resource by oracle process query:
SELECT
v.sid,
v.serial#, v.username, v.program, v.machine, p.spid, s.first_load_time,
s.sql_text
FROM
v$session v,
v$sql s, v$process p
WHERE
v.sql_hash_value
= s.hash_value and v.sql_address = s.address
and v.paddr = p.addr and v.status = 'ACTIVE';
select a.job,a.sid,b.serial#,b.machine,b.username from dba_jobs_running a,v$session b where a.sid=b.sid;
Blocking locks:
select 'Sid '||l1.sid||' is blocking sid '||l2.sid
"Blocking Status" from v$lock l1,v$lock l2
where l1.block=1 and l2.request>0 and l1.id1=l2.id1 and
l1.id2=l2.id2;
select s1.username || '@' || s1.machine ||
' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' (
SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1 join v$lock l2 on (l1.id1 =
l2.id1 and l2.id2 = l2.id2)
JOIN
v$session s1 ON (s1.sid = l1.sid)
JOIN v$session s2 ON (s2.sid = l2.sid)
WHERE l1.BLOCK=1 and l2.request > 0;
SELECT * FROM V$SESSION
WHERE USERNAME IS NOT NULL AND LAST_CALL_ET > (600) AND STATUS =
'ACTIVE';
SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL;
select(select username from v$session where sid=a.sid)
blocker,a.sid,' is blocking ',(select username from v$session where sid=b.sid)
blockee,b.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2;
alter system kill session '800,13269' immediate
select * from v$lock where block > 0;
select sid,serial#,program,status,osuser,machine,terminal
from v$session where sid=5316;
SQL> select sid, serial#,
inst_id from gv$session where sid='1018';
SID SERIAL# INST_ID
---------- ---------- ----------
1018
64305 2
SQL> alter system disconnect
session '1018,64305,@2' immediate;
select s.sid || ', '
|| s.serial# sid_serial, p.spid, s.username || '/' || s.osuser username, s.status,
to_char(s.logon_time, 'DD-MON-YY HH24:MI:SS')
logon_time, s.last_call_et/60 last_call_et,
w.event || ' / ' || w.p1 || ' / ' || w.p2 || ' / ' ||
w.p3 waiting_event, p.program
from v$process
p, v$session s,
v$session_wait w where
s.paddr = p.addr and s.sid=&Oracle_SID
and w.sid = s.sid;
Find out
Blocking session:-
select blocking_session,sid,serial#,wait_class,seconds_in_wait
from v$session where blocking_session is not NULL order by blocking_session;
Jobs Running in
the database.
SELECT
R.SID,J.JOB,J.WHAT,R.LAST_DATE,J.THIS_DATE,J.NEXT_DATE FROM DBA_JOBS
J,DBA_JOBS_RUNNING R WHERE R.JOB=J.JOB;
select job,what,log_user,this_date,this_sec from
dba_jobs where job=2020;
select sid,username,status,event,state,row_wait_obj#,row_wait_row#
from v$session where sid in (139,133) order by sid;
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
v$session s
select d.job_name,d.job_action
from dba_scheduler_jobs d, sys.scheduler$_job s where
d.job_action=program_action and s.obj# =&myjobnum;
if job scheduled through
scheduler
select obj# ,
object_name from sys.scheduler$_job a, dba_objects b where a.obj# = b.object_id
and a.obj#='&NUM';
Sessions:
select
sid,serial#,username,osuser,program,machine,status,logon_time,floor(last_call_et/60)
"AIM", type,sql_address,sql_hash_value from v$session where username
is not null order by sid;
select
sid,serial#,username,osuser,program,type,sql_address, from v$session where
username is not null order by sid;
SELECT
SID,Serial#,UserName,Status,SchemaName,Logon_Time FROM V$Session WHERE
Status='ACTIVE'AND username is not null;
SELECT v.SID,
SUBSTR(s.NAME,1,30) "Statistic", v.VALUE
FROM V$STATNAME s,
V$SESSTAT v
WHERE s.NAME = 'CPU
used by this session'
AND v.STATISTIC# =
s.STATISTIC#
AND v.VALUE > 0
ORDER BY 3;
select
username,status,sid,serial# from v$session;
alter system kill
session 'SID,SERIAL#' where status = 'INACTIVE';
alter system kill
session '151,39' immediate;
To find the
maximum number of sessions used in the database.
select * from
v$resource_limit;
Kill a session
of a particular machine
SELECT 'ALTER SYSTEM
KILL SESSION '||''''||SID||','||SERIAL#||''''||' IMMEDIATE;' "Sessions to
be Killed" FROM V$SESSION WHERE machine='ASA\TXFER_TX24' and
last_call_et/60>60
To find the
sessions available in the database ordered by their sid.
--order by sid
select
sid,serial#,username,osuser,program,machine,status,logon_time,floor(last_call_et/60)
"Act in MIn",
type,sql_address,sql_hash_value
from v$session where username is not null order by sid;
-- order by last_call_et(descending)
select
sid,serial#,username,osuser,program,machine,status,logon_time,floor(last_call_et/60)
"Act in MIn",
type,sql_address,sql_hash_value
from v$session where username is not null order by LAST_CALL_ET DESC;
-- order by login time name
select
sid,serial#,username,osuser,program,machine,status,logon_time,floor(last_call_et/60)
"Act in MIn",
type,sql_address,sql_hash_value
from v$session where username is not null order by logon_time DESC;
-- order by machine name
select
sid,serial#,username,osuser,program,machine,status,logon_time,floor(last_call_et/60)
"Act in MIn",
type,sql_address,sql_hash_value
from v$session where username is not null order by MACHINE DESC;
To clear Old
ARchive log files
Set oracle_sid=tlax
Rman target
sys/itpwyxdbafk
Delete archivelog
until time 'sysdate-7';
To find the
redo log switch time in minutes.
SELECT ROUND(AVG(1440 * (b.first_time - a.first_time)), 0)
"Log switch time in minutes"
FROM v$loghist a, v$log b WHERE b.sequence# = a.sequence# +
1 AND a.sequence# = (SELECT MAX(sequence#) FROM v$loghist) ORDER BY a.sequence#;
Long running
sql:
select * from
v$session_longops where sofar!=totalwork;
Check weather
long process or running or not:-
Select username,target,(sofar*100/total
work) "Percent work",opname,elapsed_seconds,time_remaining from
v$session_longops where totalwork<>sofar and totalwork>0;
if it is not
long running process it shows the (No rows selected)
To find the
percentage of completion for the running sql statements.
select SID, SERIAL#, OPNAME,
SOFAR, TOTALWORK, Decode(totalwork,0,0,round (100*sofar/totalwork,2)) | |'%'"Percentage
Completed", time_remaining "Time in Seconds" from
v$session_longops where sofar!=totalwork order by 6 desc;
select
sid,serial#,opname,to_char(start_time,'HH24:MI:SS') "START", TARGET, TIME_REMAINING,
(SOFAR/TOTALWORK)*100 "PERCENT_COMPLETED"
FROM V$SESSION_LONGOPS
where sofar!=totalwork;
Sessions
connected to the database.
SELECT SID, SERIAL#, USERNAME,
OSUSER, PROGRAM, MACHINE, STATUS, LOGON_TIME, FLOOR(LAST_CALL_ET/60)
"AIM" FROM V$SESSION WHERE USERNAME IS NOT NULL ORDER BY MACHINE
DESC;
Select
execuations,gets,row_processed in v$sqlarea
we need to get execuations. gets
and row_processed in v$session and
v$sqlarea
only in v$sqlarea
SESSION COUNT:-
select count(*) from
v$session;
select count(*) from
v$session where status='ACTIVE';
select count(*) from
v$session where status='INACTIVE';
select
status,count(*) from v$session group by status;
SESSION TRACE:-
exec
dbms_system.set_sql_trace_in_session(202,7102,true);
BROKEN JOBS RECOVER:-
EXEC
DBMS_JOB.BROKEN(JOB=>JOB NUMBER,BROKEN=FALSE);
exec
dbms_job.broken(66143,FALSE)
EXEC
DBMS_JOB.RUN(JOBNO);
SQL> select job,
what from dba_jobs;
JOB WHAT
----------
-----------------------------------------------------------------------
22 compute_statistics;
42
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
SQL> execute dbms_job.remove(22);
USER LEVEL:-
exp "fastrack_user/PVNET"
file="F:\Backup\backup\exp_PVNET_05-JAN-10.dmp"
log="F:\Backup\backup\exp_PVNET_05-JAN-10.log" rows=y grants=y
indexes=y statistics=none buffer=120000
TABLE LEVEL:-
exp
FASTRACK_USER/PVSC@PVSC Tables = (s_fti_acknowledgement_ref)
FILE=
F:\Backup\EXP_PVSC_06_JAN_10.dmp LOG = F:\Backup\EXP_PVSC_06_JAN_10.log rows =
Y
exp userid = username/password@dbtns
BUFFER0000 FILE= D:\file_name.dmp LOG = D:\file_name.log
rows = Yes Indexes =
No STATISTICS=NONE FEEDBACK0000 GRANTS=N Tables = <Table Name>
exp
userid=FASTRACK_USER/PVSC@PVSC Tables = (s_fti_acknowledgement_ref)
BUFFER=10000
FILE= D:\file_name.dmp
LOG = D:\file_name.log rows = Yes Indexes = No STATISTICS=NONE
FEEDBACK0000 GRANTS=N
DBLINKS INFO:-
select sysdate from
dual@CMPLINK
select * from
dba_db_links;
Select * from
ALL_DB_LINKS
Select * from
ALL_DB_LINKS
select * from
user_db_links
select sysdate from
dual
select * from
all_users@CMPLINK
/
TK PROOF
execute
dbms_system.set_sql_trace_in_session(192,887,true);
SQL> set serveroutput
on;
SQL> spool
D:\devasa34_output.txt
SQL> execute
dbms_system.set_sql_trace_in_session(192,887,true);
PL/SQL procedure
successfully completed.
SQL> execute
dbms_system.set_sql_trace_in_session(192,887,false);
PL/SQL procedure successfully
completed.
SQL> set
serveroutput off;
SQL> spool off
SQL> tkproof
SQL> tkprof
D:\ORADBA\TRANSFER\DEVASA34\UDUMP\devasa34_ora_2312.trc
D:\TKPROOF\devasa34_output.txt sys=NO explain=fastrack_user/pvtx@devasa34
IMPORTANT CMD
LOCAL DATABSE SERVER
NAME=EMSANGAPPS
FIND OUT THE BROKEN JOBS:-
SELECT R.SID,J.JOB,J.WHAT,R.LAST_DATE,J.THIS_DATE,J.NEXT_DATE FROM DBA_JOBS J,DBA_JOBS_RUNNING R WHERE R.JOB=J.JOB;
Daily Checkups Transfer
Invalid Objects:
select * from
dba_objects where status='invalid' and owner in ('TRANSFER_USER','ARCHIVE_USER');
select * from
user_objects where status='INVALID';
Scheduler
SELECT * FROM
S_TR_SCHEDULER;
SBF Request Status
SELECT * FROM
S_TRI_REQUEST ORDER BY 1 DESC;
Jobs Running in the database.
SELECT R.SID,J.JOB,J.WHAT,R.LAST_DATE,J.THIS_DATE,J.NEXT_DATE
FROM DBA_JOBS J,DBA_JOBS_RUNNING R WHERE R.JOB=J.JOB;
Sessions connected to the database.
SELECT SID,SERIAL#,
SERNAME, SUSER,PROGRAM, ACHINE, TATUS, OGON_TIME, FLOOR(LAST_CALL_ET/60)
"AIM" FROM V$SESSION WHERE USERNAME IS NOT NULL ORDER BY MACHINE
DESC;
To place a job offline
exec
dbms_job.broken(318,TRUE);
To kill the session.
Alter system kill
session 'sid,serial#' immediate;
sacl
mail
confirmation check
1.select * from v$session;
2. check for any blocks,
3. long runing sql
4.jobs running
5.jobs broken
Broke the job if any one is running
SET LINES 500 SELECT 'ALTER INDEX ' ||OWNER|| '.' ||INDEX_NAME || '
REBUILD ONLINE;' FROM DBA_INDEXES WHERE
OWNER IN ('TRANSFER_USER','ARCHIVE_USER') and index_name not like '%TMP%';
6.gather schema statistics
7.check the any row chaining
If, then move
tablespaces then again rebuil the indexes.
8.job run
9.check the invalid objects
mail sent
FIND OUT THE BROKEN JOBS USING THIS
CMD:-
SELECT JOB, LOG_USER, PRIV_USER, SCHEMA_USER, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, NEXT_DATE, NEXT_SEC, TOTAL_TIME, BROKEN FROM DBA_JOBS;
DBA INVALID OBJECTS:-
select * from
dba_objects where status='INVALID' AND OWNER IN
('TRANSFER_USER','ARCHIVE_USER')
USER
INVALID OBJECTS:
Select object_name from user_objets where statua=’INVALID’;
RECOMPILE THE INVALID OBJECTS:-
F:\ORADBA\TRANSFER\DBNAME\SCRIPTS\CONTROL\RECOMPILE.SQL
Session executing more
memory in dbs:
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
Session who login in machine
SELECT distinct a.sid
,a.serial#,a.status ,NVL(a.username,'{Background Task}') ,a.osuser ,a.machine ,
a.program ,a.logon_time ,Trunc(b.value/1024)
FROM v$session a,
v$sesstat b,
v$statname c,
v$sqltext d
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND a.sql_hash_value = d.hash_value
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY a.logon_time
ASC;
Tablespace size
scripts
SELECT /* + RULE */
df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes
- SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM
dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE
fs.tablespace_name (+) =
df.tablespace_name
GROUP BY
df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM
dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE
fs.tablespace_name (+) = df.tablespace_name
GROUP BY
df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4
DESC;
set linesize 150
column file_name
format a50
column
tablespace_name format a10
column free_space
format 9999.9999
select ddf.file_name
,ddf.tablespace_name
,sum(dfs.bytes)/1024/1024
free_space
from dba_data_files
ddf, dba_free_space dfs
where ddf.file_id =
dfs.file_id
and
ddf.tablespace_name like 'USERS'
group by
ddf.file_name,ddf.tablespace_name
/
|
FILE_ID |
FILE_NAME |
SMALLEST |
CURRSIZE |
SAVINGS |
SELECT FILE_NAME,
CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL(
BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES
DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY
FILE_ID ) DBAFS
WHERE DBADF.FILE_ID =
DBAFS.FILE_ID(+)
/
Datafile shrink
set linesize 150
column file_name format a50
column tablespace_name format a10
column free_space format 9999.9999
select ddf.file_name,ddf.tablespace_name,
sum(dfs.bytes)/1024/1024 free_space from dba_data_files ddf, dba_free_space dfs
where ddf.file_id = dfs.file_id and ddf.tablespace_name like 'APPS_TS_TX_IDX'
group by ddf.file_name,ddf.tablespace_name
/
TABLESPACE USED AND FREE PERCENTAGE
SELECT /* + RULE */ df.tablespace_name
"Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024)
"Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
TABLESPACE USAGE
column tablespace_name
heading 'Tablespace Name' format a10
COLUMN created_size
HEADING 'Create (MB)' FORMAT
9999,999.99;
COLUMN max_free HEADING 'Max Free (MB)' FORMAT 9999,999.99;
COLUMN tot_free HEADING 'Tot Free (MB)' FORMAT 9999,999.99;
COLUMN pct_free HEADING 'Free %' FORMAT 999.999;
SELECT
SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",
tot_create_blk /1024/1024 created_size,
ROUND(max_free_blk / 1024/1024,2)
max_free,
ROUND(tot_free_blk / 1024/1024,2)
tot_free,
ROUND((tot_free_blk/tot_create_blk*100),3) pct_free
FROM ( SELECT tablespace_name,
SUM(bytes) tot_create_blk
FROM sys.dba_data_files
GROUP BY tablespace_name ) data_files,
( SELECT tablespace_name,
MAX(bytes) max_free_blk,
SUM(bytes) tot_free_blk
FROM sys.dba_free_space
GROUP BY tablespace_name ) free_space
WHERE data_files.tablespace_name =
free_space.tablespace_name
and data_files.tablespace_name =
'&tablespace'
ORDER BY
data_files.tablespace_name;
DATAFILE LIST :
================
set pages 200
set lines 200
col tablespace_name
for a30
col file_name for a70
select
tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024
MAXSIZE_MB from dba_data_files where tablespace_name='&TABLESPACENAME';
select tablespace_name,file_name,bytes/1024/1024
Size_MB from dba_data_files where tablespace_name='&TABLESPACENAME';
==================================================================================
***************************************************************************************
select job_name,
session_id, running_instance, elapsed_time, cpu_used
from
dba_scheduler_running_jobs;
JOB_NAME SESSION_ID
RUNNING_INSTANCE
-------------------
---------- ----------------
JOB_DEL_PROJECTS 67 4
Now you can stop the
job using
exec
DBMS_SCHEDULER.stop_JOB (job_name => 'JOB_DEL_PROJECTS');
or you can kill the
SID
