http://www.dbasupport.com/oracle/ora10g/session_wait.shtml
select distinct sql_id from v$active_session_history where SESSION_ID=
select distinct sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where SESSION_ID=
-------------------------------------------------------------------------------------------------------DB Objects
--http://www.techonthenet.com/oracle/sys_tables/
all_views
ALL_CATALOG
-------------------------------------------------------------------------------------------------------get Session Id
Select Sid From V$SESSION Where audsid = SYS_CONTEXT('userenv','sessionid');
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL;
-------------------------------------------------------------------------------------------------------get User name
Select '*** User = '||USER||' ***' From dual
-------------------------------------------------------------------------------------------------------get db name
Select Name, CREATED, LOG_MODE
From V$DATABASE;
-------------------------------------------------------------------------------------------------------get DB links
Select db_link From user_db_links;
Select A.OWNER, A.HOST, A.DB_LINK, A.USERNAME, A.CREATED,
DECODE (B.FLAG, 0, 'NO', 1, 'YES') "Dec", B.AUTHUSR, C.STATUS
From DBA_DB_LINKS A, SYS.USER$ U, SYS.LINK$ B, DBA_OBJECTS C
Where A.DB_LINK = B.Name And
A.OWNER = U.Name And
B.OWNER# = U.USER# And
A.DB_LINK = C.OBJECT_NAME And
A.OWNER = C.OWNER And
C.OBJECT_TYPE = 'DATABASE LINK'
Order By 1, 2, 3;
------------------------------------------------------------------------------------------------------SYS_CONTEXT: http://www.psoug.org/reference/sys_context.html
Select SYS_CONTEXT('userenv','DB_NAME') From dual
DB_NAME = Database
SERVER_HOST = Name Of server Where DB resides
HOST = Name Of client
SESSION_ID = Session Id
OS_USER = O.S user
SERVICE_NAME = Name Of service conneted To A Session (db Name)
SESSION_USER = Name Of user login Id
SESSION_USERID = dentifier Of The Database user Name By which The Current user Is Authenticated.
Sid =
-------------------------------------------------------------------------------------------------------who is running what
Select s.Program, s.schemaname, s.Sid, s.status, s.process, s.osuser, A.sql_text, P.Program
From v$session s, v$sqlarea A, v$process P
Where s.sql_hash_value=A.hash_value And s.sql_address=A.address And s.paddr=P.addr
--and s.schemaname='&1'
And s.status='ACTIVE'
-------------------------------------------------------------------------------------Oracle Parameters (init.ora)
Select Name,
DECODE (Type, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') Type,
VALUE, DESCRIPTION, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISDEFAULT,
ISMODIFIED, ISADJUSTED
From V$PARAMETER
Order By Name;
--RBS
Select
r.segment_name segment_name,
r.owner owner,
r.tablespace_name tablespace_name,
r.status status,
ROUND(r.initial_extent/1024/1024) initial_extent,
ROUND(r.next_extent/1024/1024) next_extent,
s.Extents,0 Extents,
ROUND(s.rssize/1024/1024) rssize,
s.xacts active_trans
From
dba_rollback_segs r,
v$rollname n,
v$rollstat s
Where r.segment_name = n.Name
And n.usn = s.usn;
Select 'Database Version = '||banner From v$version Where Rownum = 1;
Select 'Database Block Size = '||value From v$parameter Where Name = 'db_block_size';
----------------------------------------------------------------------------------------------------Date Time difference
Sql> Create Or REPLACE Function datediff( p_what In VARCHAR2,
2 p_d1 In DATE,
3 p_d2 In DATE ) Return NUMBER
4 As
5 l_result NUMBER;
6 Begin
7 Select (p_d2-p_d1) *
8 DECODE( UPPER(p_what),
9 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, Null )
10 Into l_result From dual;
11
11 Return l_result;
12 End;
13 /
Function created
---------------------------------------------------------------------------------------------------who is using rbs?
Select SUBSTR(A.os_user_name,1,8) "OS User"
, SUBSTR(b.object_name,1,30) "Object Name"
, SUBSTR(b.object_type,1,8) "Type"
, SUBSTR(c.segment_name,1,10) "RBS"
, E.process "PROCESS"
, SUBSTR(d.used_urec,1,8) "# Of Records"
, d.used_ublk
From v$locked_object A
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session E
Where A.object_id = b.object_id
And A.xidusn = c.segment_id
And A.xidusn = d.xidusn
And A.xidslot = d.xidslot
And d.addr = E.taddr
----------------------------------------------------------------------------------------------------Re-Connect
Begin sys.dbms_application_info.set_module('T.O.A.D.', Null); End;
-----------------------------------------------------------------------------------------------------Client Info
SET serveroutput On
Declare
XXX VARCHAR(20);
Begin
DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('TOAD1+1');
DBMS_APPLICATION_INFO.READ_CLIENT_INFO (XXX );
DBMS_OUTPUT.PUT_LINE(XXX);
End;
Select P.spid,
substr(s.osuser,1,10) osuser,
substr(s.username,1,8) username,
substr(s.Program,1,24) Program,
substr(s.client_info,1,60) ClientInfo
From v$session s, v$process P
Where s.paddr=P.addr
And s.osuser Is Not Null
--and spid=4767916
Order By s.osuser
-------------------------------------------------------------------------------------------------SET_SESSION_LONGOPS
Select * From V$SESSION_LONGOPS Where sql_address='070000004185C7E0'
Select *-- sql_id
From v$session
Where Sid =24
Select operation
, options
, object_name
, object_alias
, object_type
Select *
From v$sql_plan --WHERE object_name='XXXX'
Where address = --'070000004185C7E0'
( Select sql_address
From v$session
Where Sid =24)
---------------------------------------------------------------------------------------------------------CBO
Select *--index_name,table_name, blevel, avg_data_blocks_per_key, avg_leaf_blocks_per_key, clustering_factor
From user_indexes Where table_name In ('XXXX','YYYY')
Select *--table_name, blocks
From user_tables Where table_name In (''XXXX','YYYY')
EXEC DBMS_STATS.gather_table_stats('orauser', ''XXXX');
EXEC DBMS_STATS.gather_table_stats('orauser', ''XXXX', estimate_percent => 15);
EXEC DBMS_STATS.gather_index_stats('orauser', ''XXXX');
EXEC DBMS_STATS.gather_index_stats('orauser', ''XXXX', estimate_percent => 15);
--more on Statistics
http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php
Select table_name, num_rows From dba_tables Where owner='orauser'
Order By 2 desc
Select * From user_tab_cols
col c1 heading 'Average Waits|forFull| Scan Read I/O' format 9999.999
col c2 heading 'Average Waits|for Index|Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
Select
A.average_wait ,
b.average_wait ,
A.total_waits /(A.total_waits + b.total_waits) ,
b.total_waits /(A.total_waits + b.total_waits) ,
b.average_wait , A.average_wait
From
v$system_event A,
v$system_event b
Where
A.event = 'db file scattered read'
And
b.event = 'db file sequential read'
Select * From v$system_event
-----------------------------------------------------------------------------------------------------File Status
Select SEGMENT_NAME, STATUS From dba_rollback_segs
Select * From dba_data_files Order By file_id
Select FILE#,status,Error From v$datafile_header
Select Name , status From v$datafile ;
4
/u04/oradata/f752x539/o1_mf_psindex_2pxzbghr_.dbf
-----------------------------------------------------------------------------------------------------AutoNumber in trigger
Create Or REPLACE Trigger TableName_ID_assign
Before Insert On TableName For Each Row
Begin
Select TableName_IDs.Nextval Into :New.Id From dual;
End;
/
Create Procedure TableName_C( p_ID Out TableName.Id%Type ...) Is
v_NewID TableName.Id%Type;
Begin
Select TableName_IDs.Nextval Into v_NewID From dual;
Insert Into TableName (Id ...) Values (v_NewID ...);
p_ID = v_NewID;
End TableName_C;
/
---------------------------------------------------------------------------------------------------Tables/Index in a TableSpace
Select A.Name,c.Name Tablespace,b.analyzetime analyzed,b.rowcnt ,d.bytes, d.bytes/POWER(2,20) MB,d.bytes/POWER(2,30) GB
From sys.obj$ A, sys.tab$ b, sys.ts$ C,DBA_SEGMENTS d
Where A.Name Like 'XXXX%'
And A.obj#=b.obj#
And b.ts#=c.ts#
And A.Name=d.segment_name
And d.segment_type='TABLE'
And d.tablespace_name=c.Name
Select A.Name,c.Name Tablespace,b.analyzetime analyzed,b.rowcnt ,d.bytes, d.bytes/POWER(2,20) MB,d.bytes/POWER(2,30) GB
From sys.obj$ A, sys.ind$ b, sys.ts$ C,DBA_SEGMENTS d
Where A.obj#=b.obj#
And b.ts#=c.ts#
And A.Name=d.segment_name
And d.segment_type='INDEX'
And d.tablespace_name=c.Name
And A.Name Like ''XXXX%'
Order By Name
Select segment_name,segment_type, bytes/POWER(2,20) MB,bytes/POWER(2,30) GB
From DBA_SEGMENTS Where segment_name Like 'XXXX%'
Drop Table XXXX
Select * From sys.tab$
Select * From DBA_SEGMENTS Where segment_name Like 'XXXX%'
-- total bytes
Select SUM(BYTES) From USER_SEGMENTS Where SEGMENT_TYPE='TABLE' And segment_name=UPPER(''XXXX')
--how many MB -GB
Select 1073741824/POWER(2,20) MB, 1073741824/POWER(2,30) GB From dual
--move to a different tablespace
Alter Table 'XXXX Move Tablespace new_table_space
----------------------------------------------------------------------------------------------bind variable VALUES IN 10g
http://halisway.blogspot.com/search/Label/oracle
First have A look In v$sql To find The Sql Query you are looking For,Join The sql_id To v$sql_bind_capture And To View The bind variable Values For that Query.
v$session.sql_hash_value = Current Cursor Sql, v$session.prev_hash_value = prev Sql. Both can Use To look up Sql text (HASH_VALUE(
Select sql_id,sql_text From v$sql Where users_executing > 0
Select Name, position, DATATYPE_STRING,WAS_CAPTURED,LAST_CAPTURED,VALUE_STRING
From v$sql_bind_capture Where sql_id='5chvypbvbxtau'
Select sql_text From v$sqlarea Where users_executing > 0
--from SID
Select SQL_TEXT,sql_id From V$SQLTEXT_WITH_NEWLINES Where HASH_VALUE=TO_NUMBER((Select sql_hash_value From v$session Where Sid =459 And Type <> 'BACKGROUND')) Order By PIECE
---------------------------------------------------------------------------------------------------Oracle select Index
-->> USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.
Select * From USER_TAB_COLUMNS Where table_name='XXXXX' And column_name='BUSINESS_UNIT'
--USER_TABLES.NUM_ROWS - IF NUM_DISTINCT = NUM_ROWS THEN USING an INDEX would be preferable TO doing a FULL TABLE SCAN.
--AS the NUM_DISTINCT decreases, the cost OF USING an INDEX increase thereby making the INDEX LESS desirable.
Select * From USER_TABLES Where table_name='XXXXX'
Select Clustering_Factor From USER_INDEXES Where index_name='XXXXX'
# USER_INDEXES.Clustering_Factor - This defines how Ordered The Rows are In The Index. If Clustering_Factor approaches The NUMBER Of Blocks In The Table, The Rows are Ordered. If it approaches The NUMBER Of Rows In The Table, The Rows are randomly Ordered. In such A Case, it Is unlikely that Index entries In The same leaf Block will Point To Rows In The same Data Blocks.
# Decrease The INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make The Cost Of A Full Table Scan cheaper.
----------------------------------------------------------------------------------------------- Descending index
Select owner, index_name, uniqueness, status
,INDEX_TYPE, Temporary, PARTITIONED, Logging, Degree, funcidx_status, join_index
From sys.ALL_INDEXES
Where table_owner = 'orauser'
And table_name = 'XXXXX'
Order By index_name
Select * From sys.ALL_IND_COLUMNS Where index_owner = 'orauser' And index_name = 'XXXXXX' Order By column_position
Select column_expression From sys.ALL_IND_EXPRESSIONS Where index_owner = 'orauser'And index_name = 'XXXXX'And column_position =3
Select
INDEX_NAME "Index Name"
,INDEX_TYPE "Index Type"
,UNIQUENESS "Uniqueness"
,STATUS "Status"
,TABLE_OWNER || '.' || TABLE_NAME "Table"
,TABLE_TYPE "Table Type"
,TABLESPACE_NAME "Tablespace"
,Buffer_Pool "Buffer Pool"
,initcap(partitioned) "Partitioned"
,decode(Temporary, 'N', 'No', 'Yes') "Temporary"
,INI_TRANS "Initial Transactions"
,MAX_TRANS "Max Transactions"
,INITIAL_EXTENT "Initial Extent Size"
,NEXT_EXTENT "Next Extent Size"
,MIN_EXTENTS "Minimum Extents"
,MAX_EXTENTS "Maximum Extents"
,PCT_INCREASE "Percent Increase"
,PCT_FREE "Percent Free"
,Freelists "Freelists"
,FREELIST_GROUPS "Freelist Groups"
,Degree "Degree"
,Instances "Instances"
,LAST_ANALYZED "Last Analyzed"
,BLEVEL "BLevel"
,LEAF_BLOCKS "Leaf Blocks"
,DISTINCT_KEYS "Distinct Keys"
,AVG_LEAF_BLOCKS_PER_KEY "Avg Leaf Blocks Per Key"
,AVG_DATA_BLOCKS_PER_KEY "Avg Data Blocks Per Key"
,Clustering_Factor "Clustering Factor"
,NUM_ROWS "Num Rows"
,SAMPLE_SIZE "Sample Size"
,Generated "Generated"
,decode(JOIN_INDEX, 'NO', 'No', 'Yes') "Join Index"
From SYS.USER_INDEXES
Where INDEX_NAME Like 'XXXXX'
--------------------------------------------------------------------------------------------Session Info
--::::::::::Sql statements still In The Cache that have done A great deal Of Disk I/O And Buffer gets. (Buffer gets show approximately The amount Of CPU Resource used.)
Select * From V$SQLAREA --WHERE module LIKE 'T.O.A.D%'
--::::::::: Shows active (in progress) transactions
Select Sid, serial#,s.status,username, terminal, osuser,
T.start_time, r.Name, (T.used_ublk*8192)/1024 USED_kb, T.used_ublk "ROLLB BLKS",
DECODE(T.Space, 'YES', 'SPACE TX',
DECODE(T.recursive, 'YES', 'RECURSIVE TX',
DECODE(T.noundo, 'YES', 'NO UNDO TX', T.status)
)) status
From sys.v_$transaction T, sys.v_$rollname r, sys.v_$session s
Where T.xidusn = r.usn
And T.ses_addr = s.saddr
Select
substr(s.username,1,18) username,
substr(s.Program,1,15) Program,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
From
v$session s,
v$process P,
v$transaction T,
v$rollstat r,
v$rollname n
Where s.paddr = P.addr
And s.taddr = T.addr (+)
And T.xidusn = r.usn (+)
And r.usn = n.usn (+)
Order By 1
;
----------------------------------------------------------------------------------------------------- reset PW
Select username, Password
From DBA_USERS Where username='XXXX'
Alter USER XXXX Identified By XXXXX
------------------------------------------------------------------------------------------------- how many users in DB, CPU usage
Select rpad(c.Name||':',11)||chr(9)||chr(9)|| rpad(' Current logons='||(to_number(b.sessions_current)-1),20)||chr(10)|| 'cumulative logons='||rpad(substr(A.value,1,12),12)||chr(9)|| 'highwater mark='||b.sessions_highwater||chr(9)
From v$sysstat A, v$license b, v$database c
Where A.Name = 'logons cumulative'
Select substr(sn.Name,1,30) parameter, ss.username ||' ('|| se.Sid ||') ' user_process, se.value, ss.osuser,ss.process,ss.Program,ss.logon_time,ss.client_info,ss.last_call_et
From v$session ss, v$sesstat se, v$statname sn
Where se. statistic# = sn.statistic#
And sn.Name Like '%CPU used by this session%'
And se.Sid=ss.Sid
Order By sn.Name, se.value DESC
---------------------------------------------------------------------------------------------- Installed Packages
Select *--owner, object_type
From dba_objects Where object_name = 'UTL_FILE'
Select * From dba_objects Where OBJECT_TYPE='PACKAGE' Order By 2
Select *--owner, object_type
From dba_objects Where owner='PUBLIC' And OBJECT_TYPE='SYNONYM'
And OBJECT_name In (Select OBJECT_name From dba_objects Where owner='SYS' And OBJECT_TYPE='PACKAGE')
---------------------------------------------------------------------------------------------- Random 1/10 records select
select a.* from table1 a
where dbms_utility.get_hash_value(dump(key_field),0,100)<10
---- create 10000 random strings
select dbms_random.string('x', 10),
case when level <= 5000 then 1 else 0 end,
level
from dual
connect by level <= 10000
---------------------------------------------------------------------------------------------- Displaying multiple column values per row
http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm
---------------------------------------------------------------------------------------------- parse a string
(orig: http://www.dbforums.com/oracle/998642-pl-sql-splitting-string-into-array.html)
modified form:
SELECT substr(main_string, position_from + 1, decode(position_to, 0, length(main_string),position_to - position_from - 1))
FROM (SELECT main_string,
decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
instr(main_string, ',', 1, rownum) position_to
FROM (SELECT '111,123,aaabbb,555,AAA' main_string
FROM dual)
CONNECT BY LEVEL <= length(main_string))
WHERE position_to > 0 or position_from > 0
-------------------------------------------------------Random # & strings
SELECT dbms_random.value(1,2000), dbms_random.string('U', 10),dbms_random.string('L', 10),dbms_random.string('A', 20) ,
dbms_random.string('X', 20),dbms_random.string('P', 20)
