Comparing two schemas
Hi,
We can compare two schemas with the help of tools like toad,But what if you don't have tool,We can do this with the help of the below queries:
/* Query 1:To compare the two users in terms of tables: */
SELECT TABLE_NAME FROM ALL_TABLES
WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='EMP1')
AND OWNER='EMP2';
/* Query 2:To compare the two users in terms of objects */
SELECT OBJECT_NAME||' '||OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME NOT IN
(SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='EMP1')
AND OWNER='EMP2';
Above queries can be very much helpful,when we transfer data from one schema to other schema and we want to verify them after successful transfer(i.e) after doing datapump or export import activities.Hope it helps.
Best regards,
Rafi.
We can compare two schemas with the help of tools like toad,But what if you don't have tool,We can do this with the help of the below queries:
/* Query 1:To compare the two users in terms of tables: */
SELECT TABLE_NAME FROM ALL_TABLES
WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='EMP1')
AND OWNER='EMP2';
/* Query 2:To compare the two users in terms of objects */
SELECT OBJECT_NAME||' '||OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME NOT IN
(SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER='EMP1')
AND OWNER='EMP2';
Above queries can be very much helpful,when we transfer data from one schema to other schema and we want to verify them after successful transfer(i.e) after doing datapump or export import activities.Hope it helps.
Best regards,
Rafi.
Thursday, August 5, 2010
Useful Tips and Queries for Oracle DBA.
Below are some useful tips regarding 'TEMPORARY TABLESPACE' :
Steps for dropping the Default temporary tablespace::
-----------------------------------------------
Step 1: create one drop1temp tablespace as below make it default.
-------
create tablespace drop1temp tempfile '/star/oradata/STARDEV/droptemp.ora' size 1000m;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE drop1temp;
step2:Drop the original default temporary tablespace now(eg:STA01T):
-----
DROP TABLESPACE STA01T INCLUDING CONTENTS AND DATAFILES;
* SOME THING A BIT TRICKY*
Command for creating New temporary tablespace:
-----------------------------------------------------
CREATE TEMPORARY TABLESPACE STA01T TEMPFILE '/stage/oradata/STARTST/STA01.dbf' SIZE 6000M;
Increasing the size of the temporary tablespace:
-------------------------------------------------------
ORA-01652: unable to extend temp segment by 128 in tablespace STA01T
STEPS FOR ADDING TEMP FILE IN A TEMPORARY TABLESPACE:
---------------------------------------------
Step 1:Add the temporary file.
--------
ALTER TABLESPACE STA01T ADD TEMPFILE '/stage/oradata/STARTST/star01t.dbf' SIZE 5000M;
Step 2:Shut normal and start the Database for changes to take effect.
--------
SQL>Shut immediate;
SQL>startup
Note:Restart the database for changes to take effect...
Step 3:Check the filesystem and space now:
----------------------------------------------
-- The below query can be used for checking datafile size and tempfile sizes.
select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_data_files;
select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_temp_files;
We can see a tempfile of size 5000M in our Database.
Below are some useful Queries for Oracle DBA:
-------------------------------------------------------------
BELOW QUERY YOU CAN USE IN ORACLE 11g ony to know all temporay tablespaces details:
-------------------------------------
select tablespace_name,
tablespace_size/1024/1024 "Total Space",
allocated_space/1024/1024 "Alloc Space",
free_space/1024/1024 "Free Space"
from dba_temp_free_space;
SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;
To know Default temporary tablespace:
---------------------------------------
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
-- The below query can be used to check Default Tablespace and Temporary Tablespace
For Database.
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
To know Default tablespace & temporary tablespace for user:
------------------------------------------------------------
select username,temporary_tablespace,default_tablespace from dba_users where username='STARREP';
To know Tablespace size Query the below:
-----------------------------------------
SQL>set linesize 1000
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
tablespace usage:
=================
SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
Resizing Tablespace without adding datafile:
--------------------
ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;
Checking autoextend on/off for Tablespaces:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
(OR)
SQL> select tablespace_name,AUTOEXTENSIBLE from dba_data_files;
Adding datafile to a tablespace:
alter tablespace star02D add datafile '/work/oradata/STARTST/sta05d.dbf' size 1000M autoextend off;
Increasing Datafile size:
-------------------------
Alter Database datafile '/u01/app/Test1_data_01.dbf' resize 2G;
Important:
----------------
Checking the default tablespace and default temp tablespace for ALL userS:
---------------------------------------------------------------------------
SQL>set linesize 1000
SQL> select default_tablespace,temporary_tablespace,username from dba_users;
undo usage details:
===========
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
---------- ---------- ------------------------------ --------------- -------
260,7 SCOTT sqlplus@localhost.localdomain _SYSSMU4$ 8K
(TNS V1-V3)
who is using a TEMP Segment:
============================
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
PGA USAGE:
==========
select st.sid "SID", sn.name "TYPE",
ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn
where st.statistic# = sn.statistic#
and sid in
(select sid from v$session where username like UPPER('&user'))
and upper(sn.name) like '%PGA%'
order by st.sid, st.value desc
long job:
=========
col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
CPU usage of the USER:
======================
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
Running Jobs:
=============
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS;
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';
How to find the Actual size of a Database?
select sum(bytes)/1024/1024/1024 as GB from dba_data_files;
Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_data_files;
GB
----------
30.9667969
How to find the size occupied by Data in a Database or Database usage details?
select sum(bytes)/1024/1024/1024 as GB from dba_segments;
Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_segments;
GB
----------
10.0961914
So,In the Above example Database size:30 GB,Database usage:10 GB.
How to find the size of the SCHEMA/USER?
We can find the size of the schema/user by query:
select sum(bytes/1024/1024)"size" from dba_segments where
owner='&owner';
Eg:SQL> select sum(bytes/1024/1024)"size" from dba_segments
where owner='TEST_DEV';
size
----------
2.8125
So,In the above example size occupied by user 'TEST_DEV' is 2.8 MB.
How can a USER be granted privilege for executing a particular PACKAGE?
SQL> grant execute on DBMS_CRYPTO to TEST_USER;
Grant succeeded.
SQL> grant execute on DBMS_RANDOM to TEST_USER;
Grant succeeded.
SQL> grant execute on DBMS_UTILITY to TEST_USER;
Grant succeeded.
Here DBMS_CRYPTO,DBMS_RANDOM,DBMS_UTILITY
Note:For security reason DBA should not give execute privileges on all the packages.
How to see the parameters set in our Oracle Database?
SQL> select sid,name,value from v$spparameter where isspecified='TRUE';
Happy Oracle DBA learning...
Best regards,
Rafi.
Steps for dropping the Default temporary tablespace::
-----------------------------------------------
Step 1: create one drop1temp tablespace as below make it default.
-------
create tablespace drop1temp tempfile '/star/oradata/STARDEV/droptemp.ora' size 1000m;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE drop1temp;
step2:Drop the original default temporary tablespace now(eg:STA01T):
-----
DROP TABLESPACE STA01T INCLUDING CONTENTS AND DATAFILES;
* SOME THING A BIT TRICKY*
Command for creating New temporary tablespace:
-----------------------------------------------------
CREATE TEMPORARY TABLESPACE STA01T TEMPFILE '/stage/oradata/STARTST/STA01.dbf' SIZE 6000M;
Increasing the size of the temporary tablespace:
-------------------------------------------------------
ORA-01652: unable to extend temp segment by 128 in tablespace STA01T
STEPS FOR ADDING TEMP FILE IN A TEMPORARY TABLESPACE:
---------------------------------------------
Step 1:Add the temporary file.
--------
ALTER TABLESPACE STA01T ADD TEMPFILE '/stage/oradata/STARTST/star01t.dbf' SIZE 5000M;
Step 2:Shut normal and start the Database for changes to take effect.
--------
SQL>Shut immediate;
SQL>startup
Note:Restart the database for changes to take effect...
Step 3:Check the filesystem and space now:
----------------------------------------------
-- The below query can be used for checking datafile size and tempfile sizes.
select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_data_files;
select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_temp_files;
We can see a tempfile of size 5000M in our Database.
Below are some useful Queries for Oracle DBA:
-------------------------------------------------------------
BELOW QUERY YOU CAN USE IN ORACLE 11g ony to know all temporay tablespaces details:
-------------------------------------
select tablespace_name,
tablespace_size/1024/1024 "Total Space",
allocated_space/1024/1024 "Alloc Space",
free_space/1024/1024 "Free Space"
from dba_temp_free_space;
SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;
To know Default temporary tablespace:
---------------------------------------
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
-- The below query can be used to check Default Tablespace and Temporary Tablespace
For Database.
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
To know Default tablespace & temporary tablespace for user:
------------------------------------------------------------
select username,temporary_tablespace,default_tablespace from dba_users where username='STARREP';
To know Tablespace size Query the below:
-----------------------------------------
SQL>set linesize 1000
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;
tablespace usage:
=================
SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
Resizing Tablespace without adding datafile:
--------------------
ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;
Checking autoextend on/off for Tablespaces:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
(OR)
SQL> select tablespace_name,AUTOEXTENSIBLE from dba_data_files;
Adding datafile to a tablespace:
alter tablespace star02D add datafile '/work/oradata/STARTST/sta05d.dbf' size 1000M autoextend off;
Increasing Datafile size:
-------------------------
Alter Database datafile '/u01/app/Test1_data_01.dbf' resize 2G;
Important:
----------------
Checking the default tablespace and default temp tablespace for ALL userS:
---------------------------------------------------------------------------
SQL>set linesize 1000
SQL> select default_tablespace,temporary_tablespace,username from dba_users;
undo usage details:
===========
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
---------- ---------- ------------------------------ --------------- -------
260,7 SCOTT sqlplus@localhost.localdomain _SYSSMU4$ 8K
(TNS V1-V3)
who is using a TEMP Segment:
============================
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
PGA USAGE:
==========
select st.sid "SID", sn.name "TYPE",
ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn
where st.statistic# = sn.statistic#
and sid in
(select sid from v$session where username like UPPER('&user'))
and upper(sn.name) like '%PGA%'
order by st.sid, st.value desc
long job:
=========
col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
CPU usage of the USER:
======================
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
Running Jobs:
=============
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS;
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';
How to find the Actual size of a Database?
select sum(bytes)/1024/1024/1024 as GB from dba_data_files;
Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_data_files;
GB
----------
30.9667969
How to find the size occupied by Data in a Database or Database usage details?
select sum(bytes)/1024/1024/1024 as GB from dba_segments;
Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_segments;
GB
----------
10.0961914
So,In the Above example Database size:30 GB,Database usage:10 GB.
How to find the size of the SCHEMA/USER?
We can find the size of the schema/user by query:
select sum(bytes/1024/1024)"size" from dba_segments where
owner='&owner';
Eg:SQL> select sum(bytes/1024/1024)"size" from dba_segments
where owner='TEST_DEV';
size
----------
2.8125
So,In the above example size occupied by user 'TEST_DEV' is 2.8 MB.
How can a USER be granted privilege for executing a particular PACKAGE?
SQL> grant execute on DBMS_CRYPTO to TEST_USER;
Grant succeeded.
SQL> grant execute on DBMS_RANDOM to TEST_USER;
Grant succeeded.
SQL> grant execute on DBMS_UTILITY to TEST_USER;
Grant succeeded.
Here DBMS_CRYPTO,DBMS_RANDOM,DBMS_UTILITY
Note:For security reason DBA should not give execute privileges on all the packages.
How to see the parameters set in our Oracle Database?
SQL> select sid,name,value from v$spparameter where isspecified='TRUE';
Happy Oracle DBA learning...
Best regards,
Rafi.
Saturday, July 17, 2010
Sessions and Process related Views of oracle
Sessions and Processes related important Queries of DBA are as follows:
We can know the active sessions in my Database as follows:
There are times that you may need to issue a “SHUTDOWN IMMEDIATE” command to an Oracle Database. It is critical that you should consider checking who are the users that are currently running sessions on that Database. You don’t want to shutdown on your Boss or Important person’s session. Here’s a simple SQL to find all Active sessions in your Oracle Database:
1. SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
2. FROM V$Session
3. WHERE
4. Status=‘ACTIVE’ AND
5. UserName IS NOT NULL;
How to kill all sessions connected to my Database:
Before killing sessions, if possible do
ALTER SYSTEM ENABLE RESTRICTED SESSION;
to stop new sessions from connecting.
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
ALTER SYSTEM QUIESCE RESTRICTED;
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';
KILL ALL SESSION BY GETTING ALTER SYSTEM SCRIPTS:
Step 1:Generate all the sessions to be killed as follows:
--------
SQL> select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session; 2
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
Alter system kill session '1839,3172';
Alter system kill session '1842,295';
Alter system kill session '1844,553';
Alter system kill session '1845,2707';
Alter system kill session '1846,219';
Alter system kill session '1847,1555';
(Or)
Note:Use immediate option with alter system to rollback transactions
Eg: Alter system kill session '1847,1555' immediate;
Killing inactive session as follows:
SQL> SELECT 'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||''';'
2 FROM V$SESSION WHERE STATUS='INACTIVE';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION'1835,1618';
ALTER SYSTEM KILL SESSION'1842,295';
ALTER SYSTEM KILL SESSION'1845,2707';
ALTER SYSTEM KILL SESSION'1850,114';
ALTER SYSTEM KILL SESSION'1855,11910';
KILL ALL SESSIONS OF PARTICULAR USER:
-----------------------------------------------------------------------
begin
for sessions in ( select sid
, serial#
from v$session
where username = 'user_to_kill_sessions')
loop
execute immediate 'alter system kill session '''||sessions.sid||','|sessions.serial#||'''';
end loop;
end;
Some toad work import views:
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';
select *from v$session;
select *from v$process;
select pid,username,program from v$process where username='20C1248';
Check while migration work in Sony <em>which user is using which program and status:------------------------------------------------------------------------------
SELECT s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and machine like '%BHASKAR%';
SELECT s.sid,s.serial#,p.spid,s.osuser,s.program,s.module,s.status,s.action FROM v$process p,v$session s WHERE p.addr = s.paddr
and osuser='20C1248';
SELECT a.username, a.osuser, b.spid
FROM v$session a, v$process b
WHERE a.paddr = b.addr
AND a.username IS NOT null;
select spid,serial# from v$process where spid='29467';
select 'Alter system kill session '''||sid||','||serial#||''';'
from v$session;
TO FIND THE BLOCKING SESSION ALONG WITH SID AND KILLING IN THE
DATABASE:
select process,sid, blocking_session from v$session where blocking_session is not null;
Kill oracle process in Database:
--------------------------------------------------------
select process,sid, blocking_session from v$session where blocking_session is not null;
select SERIAL# from v$session where SID=1568;
conn /as sysdba
alter system kill session '732,11231';
How to see current transaction in Database?
SQL>select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr = b.ses_addr;
How to find the job related details scheduled from Oracle Database?
SQL> SELECT
JOB||' '||SCHEMA_USER||' '||LAST_DATE||' '||NEXT_DATE||' '||WHAT||' '||FAILURES
FROM DBA_JOBS;
How To check server reboot time on windows server:
C:\Documents and Settings\Tc62MYNODE.APAC>net statistics server
Server Statistics for \\MYNODEETD106244
Statistics since 10/19/2011 2:22 PM
Sessions accepted 1
Sessions timed-out 7
Sessions errored-out 12
Kilobytes sent 62885
Kilobytes received 15769
Mean response time (msec) 0
System errors 0
Permission violations 0
Password violations 0
Files accessed 7275
Communication devices accessed 0
Print jobs spooled 0
Times buffers exhausted
Big buffers 0
Request buffers 0
The command completed successfully.
How to know the logon time in minutes(secs) and OS user details login time to Oracle Database?
select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND';
Happy SQL learning...
Best regards,
No comments:
Post a Comment