Oracle DB useful queries

To Kill Session:

SELECT ‘alter system kill session ‘||””||B.SID||’,’||
b.serial#||””||’;’,
b.status,
b.osuser,
b.machine from v$session b where B.OSUSER in (‘AK851270’);

To Check Lock on Tables:

select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from

(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,

(select OBJECT_ID, OWNER, OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS) B

where a.OBJECT_ID=B.OBJECT_ID;

To check tablespace datafiles/tempfiles:

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN tablespace FORMAT a18 HEADING ‘Tablespace Name’
COLUMN filename FORMAT a75 HEADING ‘Filename’
COLUMN filesize_in_MB FORMAT 9,999,999,999,999 HEADING ‘File Size’
COLUMN used_in_MB FORMAT 9,999,999,999,999 HEADING ‘Used (in bytes)’
COLUMN pct_used FORMAT 999 HEADING ‘Pct. Used’

BREAK ON report

COMPUTE sum OF filesize ON report
COMPUTE sum OF used ON report
COMPUTE avg OF pct_used ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes/1024/1024 filesize_in_MB
, NVL((d.bytes – s.bytes), d.bytes)/1024/1024 used_in_MB
, TRUNC(((NVL((d.bytes – s.bytes) , d.bytes)) / d.bytes) * 100) pct_used
FROM
sys.dba_data_files d
, v$datafile v
, ( select file_id, SUM(bytes) bytes
from sys.dba_free_space
GROUP BY file_id) s
WHERE
(s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes/1024/1024 filesize
, NVL(t.bytes_cached, 0)/1024/1024 used
, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d
, v$temp_extent_pool t
, v$tempfile v
WHERE
(t.file_id (+)= d.file_id)
AND (d.file_id = v.file#)
/

To create schema:
create tablespace GST_01 datafile ‘path/GST_01.dbf’ size 10G;

create user GST identified by “password” default tablespace GST_01;

grant connect,resource to gst;

To Impersonate in OBIEE:

http://IPAddress:9704/analytics/saw.dll?BIEEHome&startPage=1&NQUser=weblogic&NQPassword=weblogic&Impersonate=abc@sample.com

*IPAddress or Hostname of obiee.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s