Anuj Singh Oracle DBA: 09/01/2011

Thursday, 29 September 2011

ORA-01555, snapshot too old errors on transactions

undo tablespace info

select distinct
s.sid,
s.osuser,
s.process,
s.sql_id,
round((u.undoblks*32768) / (1024*1024*1024)) GB,
u.tuned_undoretention
from v$undostat u, v$session s
where u.maxqueryid in s.sql_id
group by
s.sid,
s.osuser,
s.process,
s.sql_id,
u.undoblks,
u.tuned_undoretention
order by s.sid

select distinct
u.maxqueryid,
u.maxquerylen,
u.tuned_undoretention
from v$undostat u, v$session s
where u.tuned_undoretention > 20000 -- <<<-- second retention
and u.maxqueryid not in s.sql_id
group by u.maxqueryid, u.maxquerylen, u.tuned_undoretention;

find the SQL_ID retention period and not in active

select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';

SQL> select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';

SQL_TEXT
--------------------------------------------------------------------------------
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'

select s.sid from v$undostat u, v$session s
where u.maxqueryid='0rc4km05kgzb9'
and u.maxqueryid = s.prev_sql_id;

to reset the undo stat

begin
reset_undo_stat();
end;

SYS AS SYSDBA>select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
5015

SYS AS SYSDBA>show parameter undo

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1

if MAX(MAXQUERYLEN)> undo_retention then you will get error

undo_retention =maxquerylen + 300 sec

================
some sql to monitor

set pages 200
col user0 form a15
col comm0 form a15
col name0 form a30
col extents0 form 999 Heading "Extents"
col shrinks0 form 999 Heading "Shrinks"
col waits form 9999 heading "Wraps"

select
rn.name name0,
s.username user0,
r.rssize ,
r.waits,
r.extents extents0,
r.shrinks shrinks0,
r.optsize,
decode (s.command,1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
10,'DROP INDEX',
12,'DROP INDEX',
26,'LOCK TABLE',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
48,'SET TRANSACTION',
NULL, NULL,
'look it up '||to_char(s.command)) comm0,
sql_id
from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.taddr (+) = t.addr
and t.xidusn (+) = r.usn
and rn.usn = r.usn
order by rn.name
/

SELECT rn.name name0
, p.pid
,p.spid
, NVL (p.username, 'NO TRANSACTION') user0
, p.terminal
FROM v$lock l, v$process p, v$rollname rn
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = rn.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY rn.name;

PROMPT
PROMPT Time since last WRAP
PROMPT WRAPS The number of times a rollback segment entry has wrapped from one extent to another.

select n.name, round( 24*((sysdate-startup_time) - trunc(sysdate-startup_time)) / (s.writes/s.rssize),1) "Hours"
from v$instance ,v$rollname n,v$rollstat s
where n.usn = s.usn
and s.status = 'ONLINE'
/

Monday, 26 September 2011

Oracle Undo statistics


Oracle Undo statistics  ..

-- single instance 
set lines 1000 pages 200
column inst_id head inst#         format 99
column hour    head "hour|(dd-mm-yyyy-hh24)"                         justify centre
column "activesize"          head "active|size(mb)"    format 99,999 justify right
column undosize   head "undo|size(mb)"    format 99,999 justify right
column expiredsize   head "expired|size(mb)"   format 99,999 justify right
column unexpiredsize          head "unexpired|size(mb)"   format 99,999 justify right
column expiredrelsize          head "expired rel|size(mb)"          format 99,999 justify right
column maxconcurrency          head "max |concurrent|txn(#)"          format 99,999 justify right
column txncount   head "total|txn (#)"    format 99,999 justify right
column maxquerylen   head "max query|length(sec)"          format 99,999 justify right
column nospaceerrcnt          head "nospace|errors (#)"   format 99,999 justify right
column ssolderrcnt   head "ora-01555|errors(#)"   format 99,999 justify right
select
  to_char(a.begin_time, 'dd-mm-yyyy-hh24')      "hour"
, round(sum(a.activeblks* 8 )/1024)   "activesize"
, round(sum(a.undoblks * 8 )/1024)   "undosize"
, round(sum(a.expiredblks * 8 )/1024)   "expiredsize"
, round(sum(a.unexpiredblks * 8 )/1024)  "unexpiredsize"
, round(sum(a.expblkrelcnt * 8 )/1024)   "expiredrelsize"
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from v$undostat a
group by to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 1
/



-- for Rac 
set lines 1000  pages 200
column inst_id head inst#                                 format 99
column hour   head "hour|(dd-mm-yyyy-hh24)"                   justify centre
column activesize     head "active|size(mb)"            format 99,999 justify right
column undosize  head "undo|size(mb)"              format 99,999 justify right
column expiredsize head "expired|size(mb)"           format 99,999 justify right
column unexpiredsize  head "unexpired|size(mb)"         format 99,999 justify right
column expiredrelsize  head "expired rel|size(mb)"       format 99,999 justify right
column maxconcurrency  head "max |concurrent|txn(#)"     format 99,999 justify right
column txncount  head "total|txn (#)"              format 99,999 justify right
column maxquerylen  head "max query|length(sec)"      format 99,999 justify right
column nospaceerrcnt  head "nospace|errors (#)"         format 99,999 justify right
column ssolderrcnt  head "ora-01555|errors(#)"        format 99,999 justify right
select
  a.inst_id
, to_char(a.begin_time, 'dd-mm-yyyy-hh24')  hour
, round(sum(a.activeblks* 8 )/1024)   activesize
, round(sum(a.undoblks * 8 )/1024)   undosize
, round(sum(a.expiredblks * 8 )/1024)   expiredsize
, round(sum(a.unexpiredblks * 8 )/1024)  unexpiredsize
, round(sum(a.expblkrelcnt * 8 )/1024)   expiredrelsize
, max(maxconcurrency)     maxconcurrency
, sum(txncount)     txncount
, max(maxquerylen)     maxquerylen
, sum(nospaceerrcnt)     nospaceerrcnt
, sum(ssolderrcnt)     ssolderrcnt
from gv$undostat a
group by a.inst_id, to_char(a.begin_time, 'dd-mm-yyyy-hh24')
order by 2 ;

    hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
(dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
12-09-2016-09      488        2      570         4           0          4     181       1,125          0         0
12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0
12-09-2016-11    1,463        2    1,711         6           0          4     346       1,110          0         0

Rac Output ..

                                                                           max
          hour        active     undo  expired unexpired expired rel concurrent   total   max query    nospace ora-01555
inst# (dd-mm-yyyy-h size(mb) size(mb) size(mb)  size(mb)    size(mb)     txn(#) txn (#) length(sec) errors (#) errors(#)
----- ------------- -------- -------- -------- --------- ----------- ---------- ------- ----------- ---------- ---------
    1 12-09-2016-09      976        2    1,141         7           0          4     232       1,128          0         0
    2 12-09-2016-09       63        2    1,697         3           0          3     171       1,070          0         0
    1 12-09-2016-10    1,463        2    1,717         4           0          4     362       1,121          0         0

Oracle ASM balanced files report

set pagesize 55
set linesize 90

SELECT group_kffxp Group#
, number_kffxp file#
, MAX(count1) MAX
, MIN(count1) MIN
FROM
(SELECT group_kffxp
, number_kffxp
, disk_kffxp
, COUNT(XNUM_KFFXP) count1
FROM x$kffxp
WHERE group_kffxp = &diskgroup_number
ANd disk_kffxp != 65534
GROUP BY group_kffxp, number_kffxp, disk_kffxp
ORDER BY group_kffxp
, number_kffxp
, disk_kffxp
)
GROUP BY group_kffxp, number_kffxp;

Oracle ASM usage by file type

ASM usage by file type

set term on

SET pages 32767
SET lines 255
SET numf 999,999

COLUMN NAME HEAD "DiskGroup" FORMAT A15
COLUMN type HEAD "FileType" FORMAT A20
COLUMN SizeGB HEAD "Size|(GB)"

TTITLE LEFT "ASM SPACE USAGE BY FILETYPE"

BREAK ON REPORT
BREAK ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON NAME
COMPUTE SUM LABEL 'Total' OF SizeGB FORMAT 99,999,999 ON REPORT

select dg.name
, f.type
, ROUND(sum(bytes)/1024/1024/1024) SizeGB
from v$asm_file f
, v$asm_diskgroup dg
where dg.group_number = f.group_number
group by dg.name, f.type
ORDER BY dg.name, f.type ;

TTITLE OFF

Oracle ASM Disk fail group

ASM Disk fail group

set term on
set lines 250
set pages 50

COLUMN DiskGroup FORMAT A10
COLUMN Disk FORMAT A30
COLUMN Partner_Disk FORMAT A30
COLUMN DiskGroup FORMAT A0
COLUMN FAILGROUP FORMAT A20
COLUMN PARTNER_FAILGROUP FORMAT A20
COLUMN path FORMAT A30
COLUMN PARTNER_PATH FORMAT A30

SELECT dg1.name DiskGroup
, d1.NAME Disk
-- , d1.path
, d1.FAILGROUP
, d2.name Partner_Disk
, d2.FAILGROUP PARTNER_FAILGROUP
-- , d2.path PARTNER_PATH
FROM x$kfdpartner p
, v$asm_disk d1
, v$asm_diskgroup dg1
, v$asm_disk d2
WHERE dg1.group_number = d1.group_number
AND p.GRP = dg1.group_number
AND p.disk = d1.DISK_NUMBER
AND p.GRP = d2.group_number (+)
AND p.NUMBER_KFDPARTNER = d2.DISK_NUMBER (+)
AND dg1.name like '%%' --DiskGroup Name
AND d1.name like '%%' --Disk Name
ORDER BY dg1.name , d1.NAME;

Oracle ASM diskgrop report


Oracle ASM diskgrop report ..


set feedback off pagesize 1000 head on  underline _ linesize 170 
var v number ;
begin :v := nvl('&v',95); end;
/
col  total_gb        format 99999.99      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  COMPATIBILITY        for a12                                        
col DATABASE_COMPATIBILITY for a15
col  total_gb        format 99999.99      heading 'total gb '
col host_name for a15
set linesize 400
select    
substr(max (SYS_CONTEXT('USERENV','HOST')),1,15) host_name
,g.group_number
         , g.name
         , g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
,max(allocation_unit_size/1024/1024) allocation_unit_size_MB
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
--ORDER BY "TotalUsed%" desc
order by 2
 ;

set term on pages 200 lines 255 numf 999,999
COLUMN NAME                    HEAD "DiskGroup"    FORMAT A15
COLUMN type                    HEAD "Type"         FORMAT A6
COLUMN compatibility           HEAD "ASM|Compat"   FORMAT A10
COLUMN database_compatibility  HEAD "RDBMS|Compat" FORMAT A10
COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 99999999
COLUMN offline_disks           HEAD "Offline|Disks"
COLUMN TOTAL_GB                HEAD "Total|(GB)"
COLUMN FREE_GB                 HEAD "Free|(GB)"
COLUMN used_GB                 HEAD "Used|(GB)"
COLUMN hot_used_GB             HEAD "Hot|Used|(GB)"
COLUMN cold_used_GB            HEAD "Cold|Used|(GB)"
COLUMN REQUIRED_MIRROR_FREE_GB HEAD "Required|Free|Mirror|(GB)" JUSTIFY RIGHT COLUMN USABLE_GB HEAD "Usable|Free|(GB)"
TTITLE LEFT "ASM DISKGROUP SPACE USAGE REPORT"
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF TOTAL_GB     FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF FREE_GB      FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF USED_GB      FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF HOT_USED_GB  FORMAT 99,999,999 ON REPORT 
COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT
COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT
-- if disk not mounted with instance their values will be 0.
SELECT NAME
, state
, type
, compatibility
, database_compatibility
, allocation_unit_size/1024/1024 allocation_unit_size_MB
, offline_disks
, ROUND(TOTAL_MB/1024) TOTAL_GB
, ROUND(FREE_MB/1024) FREE_GB
, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB
, ROUND(hot_used_mb/1024) HOT_USED_GB
, ROUND(cold_used_mb/1024) COLD_USED_GB
, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB
, ROUND(USABLE_FILE_MB /1024) USABLE_GB
FROM v$asm_diskgroup;

===

var v number ;

begin :v := nvl('&v',90); end;

set term on pages 200 lines 255 numf 999,999

col name head "diskgroup" format a10

col type head "type" format a6

col compatibility head "asm|compat" format a10

col database_compatibility head "rdbms|compat" format a10

col allocation_unit_size_mb head "au|size|(mb)" format 999

col offline_disks head "offline|disks"

col total_gb head "total|(gb)"

col free_gb head "free|(gb)"

col used_gb head "used|(gb)"

col hot_used_gb head "hot|used|(gb)"

col cold_used_gb head "cold|used|(gb)"

col attention for a12

with disk as (select

d.group_number gp_num

, count(d.DISK_NUMBER) Total_Disks

, trunc(max(d.TOTAL_MB/1024),2) disk_size

from v$asm_disk d

group by d.group_number )

select

g.name

, di.total_disks

, di.disk_size

, g.state

, g.type

, g.compatibility

, g.database_compatibility

, g.allocation_unit_size/1024/1024 allocation_unit_size_MB

, g.offline_disks

, round(g.total_mb/1024) total_gb

, round(g.free_mb/1024) free_gb

, round((g.hot_used_mb + cold_used_mb) /1024) used_gb

, round(g.hot_used_mb/1024) hot_used_gb

, round(g.cold_used_mb/1024) cold_used_gb

, round(g.required_mirror_free_mb/1024) required_mirror_free_gb

, round(g.usable_file_mb /1024) usable_gb

, (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"

, case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'

else 'Good'

end as attention

FROM v$asm_diskgroup g, disk di

where 1=1

and g.Name not in ('GRID' )

and g.group_number=di.gp_num ;

=====================

SET LINESIZE 200 PAGESIZE 9999

COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'

COLUMN disk_file_path FORMAT a17 HEAD 'Path'

COLUMN disk_file_name FORMAT a20 HEAD 'File Name'

COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'

COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'

COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'

COLUMN pct_used FORMAT 9999.99 HEAD 'Pct. Used'

break on report on disk_group_name skip 1

compute sum label "" of total_mb used_mb on disk_group_name

compute sum label "Grand Total: " of total_mb used_mb on report

SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

, b.total_mb total_mb

, (b.total_mb - b.free_mb) used_mb

, ROUND((1- (b.free_mb / nullif(b.total_mb,0)))*100, 2) ||'%' pct_used

FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY a.name

Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB) Pct. Used

-------------------- ----------------- -------------------- -------------------- -------------- -------------- -----------------------------------------

DATA ORCL:FLSH_DATA0 FLSH_DATA0 FLSH_DATA0 102,401 44,067 43.03%

ORCL:FLSH_DATA1 FLSH_DATA1 FLSH_DATA1 102,401 44,058 43.02%

ORCL:FLSH_DATA2 FLSH_DATA2 FLSH_DATA2 102,401 44,053 43.02%

ORCL:FLSH_DATA3 FLSH_DATA3 FLSH_DATA3 102,401 44,055 43.02%

******************** -------------- --------------

409,604 176,233

DUMPS ORCL:FLSH_DUMPS0 FLSH_DUMPS0 FLSH_DUMPS0 512,000 501,900 98.03%

******************** -------------- --------------

512,000 501,900

GRID ORCL:FLSH_GRID0 FLSH_GRID0 FLSH_GRID0 40,961 396 .97%

******************** -------------- --------------

40,961 396

LOGS ORCL:FLSH_LOGS0 FLSH_LOGS0 FLSH_LOGS0 102,401 38,704 37.8%

******************** -------------- --------------

102,401 38,704

MGMT ORCL:FLSH_MGMT0 FLSH_MGMT0 FLSH_MGMT0 102,400 55,080 53.79%

******************** -------------- --------------

102,400 55,080

[CANDIDATE] ORCL:FLSH_RECO0 0 0 %

******************** -------------- --------------

0 0

-------------- --------------

Grand Total: 1,167,366 772,313

9 rows selected.

============

COLUMN name NEW_VALUE _instname NOPRINT

select lower(instance_name) name from v$instance

set lines 600

col state format a9

col dgname format a15

col sector format 999990

col block format 999990

col label format a25

col path format a40

col redundancy format a25

col pct_used format 990

col pct_free format 990

col voting format a6

BREAK ON REPORT

COMPUTE SUM OF raw_gb ON REPORT

COMPUTE SUM OF usable_total_gb ON REPORT

COMPUTE SUM OF usable_used_gb ON REPORT

COMPUTE SUM OF usable_free_gb ON REPORT

COMPUTE SUM OF required_mirror_free_gb ON REPORT

COMPUTE SUM OF usable_file_gb ON REPORT

COL name NEW_V _hostname NOPRINT

select lower(host_name) name from v$instance;

select

trim('&_hostname') hostname,

name as dgname,

state,

type,

sector_size sector,

block_size block,

allocation_unit_size au,

round(total_mb/1024,2) raw_gb,

round((DECODE(TYPE, 'HIGH', 0.3333 * total_mb, 'NORMAL', .5 * total_mb, total_mb))/1024,2) usable_total_gb,

round((DECODE(TYPE, 'HIGH', 0.3333 * (total_mb - free_mb), 'NORMAL', .5 * (total_mb - free_mb), (total_mb - free_mb)))/1024,2) usable_used_gb,

round((DECODE(TYPE, 'HIGH', 0.3333 * free_mb, 'NORMAL', .5 * free_mb, free_mb))/1024,2) usable_free_gb,

round((DECODE(TYPE, 'HIGH', 0.3333 * required_mirror_free_mb, 'NORMAL', .5 * required_mirror_free_mb, required_mirror_free_mb))/1024,2) required_mirror_free_gb,

round(usable_file_mb/1024,2) usable_file_gb,

round((total_mb - free_mb)/total_mb,2)*100 as "PCT_USED",

round(free_mb/total_mb,2)*100 as "PCT_FREE",

offline_disks,

voting_files voting

from v$asm_diskgroup

where total_mb != 0

order by 1;

===

set numf 99999.99

col DiskCnt for 9999

col "Group" for 999

SELECT g.group_number "Group" , g.name "Group Name" , g.state "State" , g.type "Type" , g.total_mb/1024 "Total GB" , g.free_mb/1024 "Free GB" , 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"

, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"

, 100*(min(d.free_mb/d.total_mb)) "MinFree"

, 100*(max(d.free_mb/d.total_mb)) "MaxFree"

, count(*) "DiskCnt"

FROM v$asm_disk d, v$asm_diskgroup g

WHERE d.group_number = g.group_number

and d.group_number <> 0

and d.state = 'NORMAL'

and d.mount_status = 'CACHED'

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb

ORDER BY 1;

=====

from web

set linesize 300

sset term on

SSET numf 999,999

DEFINE size_label=GB

DEFINE size_divider="1024/1024/1024"

PROMPT **********************************************************************

PROMPT * A S M D I S K G R O U P S P A C E U S A G E R E P O R T *

PROMPT **********************************************************************

COLUMN NAME HEAD "DiskGroup" FORMAT A16

COLUMN group_number HEAD "G#" FORMAT 99

COLUMN type HEAD "Redundancy|Type" FORMAT A6

COLUMN compatibility HEAD "ASM|Compat" FORMAT A10

COLUMN database_compatibility HEAD "RDBMS|Compat" FORMAT A10

COLUMN allocation_unit_size_MB HEAD "AU|Size|(MB)" FORMAT 999

COLUMN offline_disks HEAD "Offline|Disks"

COLUMN separator HEAD "!|!|!|!|!|!" FORMAT a1

COLUMN mirrored_total_GB HEAD "(E)||Mirrored|Total|(&size_label)"

COLUMN USABLE_TOTAL_GB HEAD "(A-D)|/REDUN||Safely|Usable|Total|(&size_label)"

COLUMN USABLE_USED_GB HEAD "(B-D)|/REDUN||Safely|Usable|Used|(&size_label)"

COLUMN USABLE_FREE_GB HEAD "(C-D)|/REDUN||Safely|Usable|Free|(&size_label)"

COLUMN USABLE_USED_PERCENT HEAD "Safely|Usable|Used|(%)" FORMAT 999.99

COLUMN USABLE_FREE_PERCENT HEAD "Safely|Usable|Free|(%)" FORMAT 999.99

COLUMN mirrored_free_GB HEAD "Mirrored|Free|(&size_label)"

COLUMN REQUIRED_MIRROR_FREE_GB HEAD "(D)|Required|Free|ForMirror|(&size_label)" JUSTIFY RIGHT

COLUMN TOTAL_GB HEAD "(A)||Total|Avail|(&size_label)"

COLUMN used_GB HEAD "(B)||Total|Used|(&size_label)"

COLUMN hot_used_GB HEAD "Hot|Used|(&size_label)"

COLUMN cold_used_GB HEAD "Cold|Used|(&size_label)"

COLUMN FREE_GB HEAD "(C)||Total|Free|(&size_label)"

BREAK ON REPORT

COMPUTE SUM LABEL 'Total' OF TOTAL_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USED_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF HOT_USED_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF COLD_USED_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF FREE_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF REQUIRED_MIRROR_FREE_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_TOTAL_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_USED_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF MIRRORED_FREE_GB FORMAT 99,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_GB FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_TOTAL_GB FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_USED_GB FORMAT 9,999,999 ON REPORT

COMPUTE SUM LABEL 'Total' OF USABLE_FREE_GB FORMAT 9,999,999 ON REPORT

PROMPT

PROMPT . <----------------------- With Mirroring ----------------------> <-------------------- Without Mirroring ---------------->

-- Note:

-- The GROUP_NUMBER, TOTAL_MB, and FREE_MB columns are only

-- meaningful if the disk group is mounted by the instance. Otherwise, their values will be 0.

SELECT NAME

, group_number

, type

, '!' separator

, ROUND(TOTAL_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) mirrored_TOTAL_GB

, ROUND((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)/1024) USABLE_TOTAL_GB

, ROUND((hot_used_mb + cold_used_mb)/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3) /1024) USABLE_USED_GB

, ROUND(USABLE_FILE_MB /1024) USABLE_FREE_GB

, ROUND((USABLE_FILE_MB + ROUND(REQUIRED_MIRROR_FREE_MB/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) ) /1024) mirrored_free_GB

, ROUND( (hot_used_mb + cold_used_mb) / (TOTAL_MB - REQUIRED_MIRROR_FREE_MB ) * 100 , 2) USABLE_USED_PERCENT

, ROUND(USABLE_FILE_MB /((TOTAL_MB - REQUIRED_MIRROR_FREE_MB )/DECODE(type,'EXTERN',1,'NORMAL',2,'HIGH',3)) * 100 ,2) USABLE_FREE_PERCENT

, '!' separator

, ROUND(REQUIRED_MIRROR_FREE_MB/1024) REQUIRED_MIRROR_FREE_GB

, ROUND(TOTAL_MB/1024) TOTAL_GB

, ROUND((hot_used_mb + cold_used_mb) /1024) USED_GB

, ROUND(hot_used_mb/1024) HOT_USED_GB

, ROUND(cold_used_mb/1024) COLD_USED_GB

, ROUND(FREE_MB/1024) FREE_GB

, '!' separator

FROM v$asm_diskgroup_stat;

===================

DiskGroup space info

		 

set feedback off pagesize 1000 head on  underline _ linesize 170 
var v number ;
begin :v := nvl('&v',95); end;
/

set linesize 400
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  COMPATIBILITY        for a12                                        
col DATABASE_COMPATIBILITY for a15
--col  total_gb        format 99999.99      heading 'total gb '
col host_name for a16
col  total_gb        format 999,999,999,999      heading 'total gb '
col STATE for a12
col type for a12
select    
substr(max (SYS_CONTEXT('USERENV','HOST')),1,16) host_name
,g.group_number
         , g.name
         , g.state
,min(compatibility) compatibility, min(database_compatibility) database_compatibility
,max(Type) type
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
--and g.group_number not in (select v1.group_number from v$asm_volume v1)
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
--ORDER BY "TotalUsed%" desc
order by 2
 ;


as grid 

[grid@ ~]$ sqlplus / as sysasm  --- <<<<<<<<<<<<<<  from grid  

var v number ;
begin :v := nvl('&v',95); end;
/

set feedback off pagesize 1000 head off  underline _ linesize 190
ttitle  off
column  today noprint new_value strtoday
select  to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual;
column  database_name noprint new_value strdatabasename
select  'Asm usage Statistics for ON '||HOST_NAME  as database_name from v$instance ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'ASM Usage' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
set  head on
col  total_gb        format 999,999,999      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col  type            format a11           heading 'Redundancy'
col VOTING_FILES for A11
col COMPATIBILITY for a12 
col DATABASE_COMP for a12 
select    g.group_number
         , g.name
         , g.state
		 ,g.type
		 ,g.VOTING_FILES
		 ,max(COMPATIBILITY )     COMPATIBILITY                                                                                   
         ,max(DATABASE_COMPATIBILITY)    DATABASE_COMP
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
-- and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
and g.group_number not in (select v1.group_number from v$asm_volume v1)  --- to avoid acfs !!! run as grid!!
--and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.state,g.type ,g.VOTING_FILES,g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / nullif(g.total_mb, 0)))*100, 2)) 
ORDER BY "TotalUsed%" desc ;





______________________________________________________________________________________________________________________

                                                                           21 apr 2024 09:14:19
                                                                                 ASM Usage
                                                         Asm usage Statistics for ON rac01
______________________________________________________________________________________________________________________

GROUP_NUMBER NAME            STATE       Redundancy  VOTING_FILE COMPATIBILIT DATABASE_COM    total gb     FREE_GB USABLE_FILE_MB TOTAL_DISKS  DISK_SIZE TotalUsed% ATTENTION
____________ _______________ ___________ ___________ ___________ ____________ ____________ ____________ __________ ______________ ___________ __________ __________ ____________________
           0 MGMT            DISMOUNTED              N           0.0.0.0.0    0.0.0.0.0               0          0              0           2          0            Good
           0 RECO            DISMOUNTED              N           0.0.0.0.0    0.0.0.0.0               0          0              0           2          0            Good
           3 LOGS            MOUNTED     EXTERN      N           12.1.0.0.0   10.1.0.0.0            100      97.49          99835           1        100       2.51 Good
           2 GRID            MOUNTED     EXTERN      Y           12.1.0.0.0   10.1.0.0.0             40      39.57          40525           1         40       1.06 Good




var v number ;
begin :v := nvl('&v',93); end;
/

set feedback off pagesize 1000 head off  underline _ linesize 200 
ttitle  off
column  today noprint new_value strtoday
select  to_char( sysdate, 'dd mon yyyy hh24:mi:ss' ) today from dual;
column  database_name noprint new_value strdatabasename
select  'Asm usage Statistics for ON '||HOST_NAME ||'-' || Db_unique_name as database_name from v$database,v$instance ;
TTITLE LEFT     '______________________________________________________________________________________________________________________' -
SKIP 2 CENTER   strToday -
SKIP CENTER     'ASM Usage' -
SKIP CENTER     strDatabaseName -
SKIP LEFT       '______________________________________________________________________________________________________________________' -
SKIP LEFT       ''
set  head on
col  total_gb        format 999,999,999      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
col Redundancy for a15
col free for a8
col disk_size for a10
col TOTAL for a8
col usable_file for a12
col COMPATIBILITY for a10
col DATABASE_COMP for a10
col VOTING_FILES for a12
select    g.group_number
         , g.name
         , g.state
		 ,g.type
		 ,g.VOTING_FILES
		 ,max(COMPATIBILITY )     COMPATIBILITY                                                                                   
         ,max(DATABASE_COMPATIBILITY)    DATABASE_COMP
         , upper(dbms_xplan.format_size2(g.total_mb*1024*1024 ))          total
		 ,max(TYPE) Redundancy
         , dbms_xplan.format_size(g.free_mb*1024*1024)  free
         , dbms_xplan.format_size(usable_file_mb* 1024*1024)          usable_file
         , count(DISK_NUMBER) Total_Disks
         , dbms_xplan.format_size(max(d.TOTAL_MB*1024*1024)) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%" 
		 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1 
and  d.group_number = g.group_number
and   g.Name not in ('GRID','ACFS' )
--and   g.Name  in ('RECO01')
and g.group_number not in (select v1.group_number from v$asm_volume v1)
and g.group_number  in ( select g2.group_number from v$asm_diskgroup g2 where voting_files='N')
group by g.group_number, g.name,g.type	 ,g.VOTING_FILES,g.state, g.total_mb*1024*1024, (g.free_mb*1024*1024), usable_file_mb* 1024*1024 ,(round((1- (g.free_mb / g.total_mb))*100, 2)) 
ORDER BY "TotalUsed%" desc ;

from grid if   proxy asm instance

export ORACLE_HOME=/u01/app/12.1.0/grid 
 export ORACLE_SID=+APX2 

 select * from v$asm_filesystem;

Sunday, 25 September 2011

Oracle default user

some Oracle default user

Where
owner not in
(
'ADAMS',
'ADLDEMO',
'ADMIN',
'APPLSYS',
'APPLYSYSPUB',
'APPS',
'AQ',
'AQDEMO',
'AQJAVA',
'AQUSER',
'AUDIOUSER',
'AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED',
'BC4J',
'BLAKE',
'CATALOG',
'CDEMO82',
'CDEMOCOR',
'CDEMORID',
'CDEMOUCB',
'CENTRA',
'CIDS',
'CIS',
'CISINFO',
'CLARK',
'COMPANY',
'COMPIERE',
'CQSCHEMAUSER',
'CSMIG',
'CTXDEMO',
'CTXSYS',
'DBA',
'DBI',
'DBSNMP',
'DEMO',
'DEMO8',
'DEMO9',
'DES',
'EJSADMIN',
'EMP',
'ESTOREUSER',
'EVENT',
'FINANCE',
'FND',
'FROSTY',
'GPFD',
'GPLD',
'HCPARK',
'HLW',
'HR',
'IMAGEUSER',
'IMEDIA',
'JMUSER',
'JONES',
'LBACSYS',
'LIBRARIAN',
'MASTER',
'MDSYS',
'MFG',
'MIGRATE',
'MILLER',
'MMO2',
'MODTEST',
'MOREAU',
'MTS_USER',
'MTSSYS',
'MXAGENT',
'NAMES',
'OAS_PUBLIC',
'OCITEST',
'ODS',
'ODSCOMMON',
'OE',
'OEMADM',
'OLAPDBA',
'OLAPSVR',
'OLAPSYS',
'OMWB_EMULATION',
'OPENSPIRIT',
'ORACACHE',
'ORAREGSYS',
'ORDPLUGINS',
'ORDSYS',
'ORACLE',
'OSE$HTTP$ADMIN',
'OSP22',
'OUTLN',
'OWA',
'OWA_PUBLIC',
'PANAMA',
'PATROL',
'PERFSTAT',
'PLSQL',
'PM',
'PO',
'PO7',
'PO8',
'PORTAL30',
'PORTAL30_DEMO',
'PORTAL30_PUBLIC',
'PORTAL30_SSO',
'PORTAL30_SSO_PS',
'PORTAL30_SSO_PUBLIC',
'POWERCARTUSER',
'PRIMARY',
'PUBSUB',
'QS',
'QS_ADM',
'QS_CB',
'QS_CBADM',
'QS_CS',
'QS_ES',
'QS_OS',
'QS_WS',
'RE',
'REP_MANAGER',
'REP_OWNER',
'REP_OWNER',
'REPADMIN',
'RMAIL',
'RMAN',
'SAMPLE',
'SAP',
'SCOTT',
'SDOS_ICSAP',
'SECDEMO',
'SH',
'SITEMINDER',
'SLIDE',
'STARTER',
'STRAT_USER',
'SWPRO',
'SWUSER',
'SYMPA',
'SYS',
'SYSADM',
'SYSMAN',
'SYSTEM',
'TAHITI',
'TDOS_ICSAP',
'TESTPILOT',
'TRACESVR',
'TRAVEL',
'TSDEV',
'TSUSER',
'TURBINE',
'ULTIMATE',
'USER',
'USER0',
'USER1',
'USER2',
'USER3',
'USER4',
'USER5',
'USER6',
'USER7',
'USER8',
'USER9',
'UTLBSTATU',
'VIDEOUSER',
'VIF_DEVELOPER',
'VIRUSER',
'VRR1',
'WEBCAL01',
'WEBDB',
'WEBREAD',
'WKSYS',
'WWW',
'WWWUSER',
'XPRT',
)

Saturday, 24 September 2011

Oracle RAC FAQ metalink 220970.1

RAC: Frequently Asked Questions [ID 220970.1]

Friday, 23 September 2011

Oracle Index metadata for given table

Index metadata for given table ....

create index  statement for given table 


ORCL\sys> !cat indx_meta.sql

set heading off 
set feedback off
set verify off
prompt set linesize 200
prompt set long 2000
select 'select dbms_metadata.get_ddl(' || '''TABLE'',' || '''' ||table_name||''',' || '''' || owner||''') from dual ;' 
from dba_tables  where table_name ='&Table_name' ;

set verify on
set heading on
set feedback on

----- out put

ORCL\sys> @indx_meta
set linesize 200
set long 2000
Enter value for table_name: USR_SITES

select dbms_metadata.get_ddl('TABLE','USR_SITES','PROD1') from dual ;






out put of the script 

select dbms_metadata.get_ddl('TABLE','USR_SITES','CPROD1') from dual ;


DBMS_METADATA.GET_DDL('TABLE','USER_SITES','CCCPROD1')
--------------------------------------------------------------------------------

  CREATE TABLE "PROD1"."USR_SITES"
   (    "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
        "SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
        "SITE_DATE_ADDED" DATE NOT NULL ENABLE,
        "TANDC_DATE_ACCEPTED" DATE,
         CONSTRAINT "USR_SITES_USPK1" PRIMARY KEY ("USER_OBJECT_ID", "SITE_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DM_CCCPROD1_DOCBASE"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PROD1"



1 row selected.


===

or

SELECT  DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) FROM    DBA_indexes
WHERE   TABLE_NAME='USR_SITES'    
 /

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "PROD1"."USR_SITES_USPK1" ON "PROD1"."USER_SITES" (
"USER_OBJECT_ID", "SITE_NO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PROD1"



1 row selected.





=========

metadata for other objects !!!!!!!!!!!!!!!!!!




Explanation of the script 



if you want to print ' in sqlplus then

ORCL\sys>; select ' '' ' from dual;

'''
---
 '

why four ' for one '  ???




if you want to print x the wt will be sqlplus

apt-lnxtst-01:ORCL\sys> select 'x' from dual;

'
-
x



replace x with '  



ORCL\sys> select ''' from dual;  


SELECT 'select dbms_metadata.get_ddl('  || ',' ||  ' ) FROM dual ;'  FROM dba_indexes where rownum SELECT 'select dbms_metadata.get_ddl('  || ',' ||  ' ) FROM dual ;'  
FROM dba_indexes where rownum 
SELECT 'select dbms_metadata.get_ddl('  ||  '''index'''||',' ||index_name|| ' ) FROM dual ;'  FROM dba_indexes where rownum 
SELECT 'select dbms_metadata.get_ddl('  ||  '''index'''||',' || ' ) FROM dual ;'  FROM dba_indexes where rownum select ''' ) FROM dual ;' from dual;

     
                                                                                                        
                                                                                                                        
                                                                                                                        
 ==================================================================================================

Oracle Metadata !!!!

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/		


define vowner='SCOTT';  ----<<<<<<<<<< Change owner


col view1 for a100
 select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  view1  from dba_OBJECTS 
where object_type='INDEX' 
and owner='&vowner'
;

for Index

set pagesize 300 linesize 200 long 5000

col view1 for a100

 select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME|| ''',''' || owner|| ''') from dual;'  view1  from dba_indexes
where 1=1
-- object_type='INDEX' 
and TABLE_NAME='xxx'
;


define vowner='REP'
define TNAME='REP'

 select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_NAME|| ''',''' || owner|| ''') from dual;'  view1  from dba_tables
where 1=1
and TABLE_NAME='&TNAME'
;

define vowner='REP'

col TABLE1 for a100
 select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TABLE1  from dba_OBJECTS 
where object_type='TABLE' 
and owner='&vowner'
;



col PROCEDURE1 for a100
 select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' PROCEDURE1  from dba_OBJECTS 
 where object_type='PROCEDURE' 
and owner='&vowner'
;



col FUNCTION1 for a100
 select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' FUNCTION1  from dba_OBJECTS 
where object_type='FUNCTION' 
and owner='&vowner'
;

col TRIGGER1 for a100
 select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TRIGGER1  from dba_OBJECTS 
where object_type='TRIGGER' 
and owner='&vowner'
;



col VIEW1 for a100
 select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' VIEW1   from dba_OBJECTS 
 where object_type='VIEW' 
and owner='&vowner'
;



col CONSTRAINT1 for a100
select 'select dbms_metadata.get_ddl(''CONSTRAINT'',''' || CONSTRAINT_NAME || ''',''' || owner|| ''') from dual;'  CONSTRAINT1 from dba_constraints 
where 1=1
and owner='&vowner'
;

                                                                                                                        
=========================================================================================     


With created date !!

		 

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss'; 
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/			 
			 
select '/*' || created || '*/' || dbms_metadata.get_ddl('INDEX',object_name, owner) 
from dba_objects 
where object_type = 'INDEX' 
and object_name='XXXX'    --- <<< Index name 
order by created, object_name;

=========

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
  END;
/

SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE 1=1
--and owner      = UPPER('&1')
AND    table_name = 'XXXX';

View Metadata

set long 200000 pages 0 lines 150 select dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) from all_views u where 1=1 and owner IN ('SYS') --and owner IN ('ANUJ') and view_name='ALL_IND_STATISTICS' order by owner,view_name ; Or select dbms_metadata.get_ddl('VIEW', 'ALL_IND_STATISTICS') stmt from dual;

                                                                                                                    
                                                                                                                        

======================	
Set pagesize 0  Long 90000
Set Feedback off
Set echo off
-- Spool Schema_ddl.sql
SELECT Dbms_metadata.GET_DDL ('TABLE', u.table_name,u.owner) from Dba_tables u;
SELECT Dbms_metadata.GET_DDL ('VIEW', u.view_name,u.owner) from Dba_views u;
SELECT Dbms_metadata.GET_DDL ('INDEX', u.index_name,u.owner) from Dba_indexes u;
Select Dbms_metadata.get_ddl ('PROCEDURE', U.object_name, U.owner) from dba_objects u where U.object_type = 'PROCEDURE';
Select Dbms_metadata.get_ddl ('FUNCTION', U.object_name, U.owner) from dba_objects u where U.object_type = 'FUNCTION';

Thursday, 22 September 2011

Oracle user bash_profile file

.bash_profile on linux 
bash_profile
.profile on solaris 
profile on solaris



!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

on linux 


oracle@apt-amd-02:~> cat .bash_profile
export ORACLE_BASE=/opt/app/oracle
#export ORACLE_HOME=/opt/app/oracle/product/10.2
export ORACLE_HOME=/opt/app/oracle/product/11.2
export TNS_ADMIN=/opt/app/oracle/product/11.2/network/admin
# export ORACLE_HOME=/opt/app/oracle/product/app10.2
export ORACLE_SID=orcl




export ORACLE_HOME=/opt/oracle/product/10.2
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH:.



export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT
export GGATE=/opt/app/oracle/product/gg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/u01/app/oracle/product/gg

alias alert='tail -f -n100 $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias bdump='cd $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace'
alias cdb='cd $ORACLE_BASE'
alias cdo='cd $ORACLE_HOME'

===

on solaris




-bash-3.00$ cat .bashrc


map! A 

map! B 

map! ^[OD ^[

map! ^[OC ^[ll





alias aaa='sqlplus / as sysdba'          # prompt before overwrite

alias alert='tail -100  /opt/oracle/admin/db/bdump/alert_db.log|more' 
alias rma='rm -i'          # prompt before overwrite
alias cpa='cp -i'
# alias mva='mv -i'
alias arch1='cd  /opt/oracle/admin/db/arch'
alias spfile='cd /opt/oracle/product/10.2/dbs'
# /opt/oracle/admin/db/arch
alias sid='env|grep ORACLE_SID'

alias aalert='vi + $(printf "set heading off pages 0 feedback off select value from v\$parameter where name='\''background_dump_dest'\'';"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'



alias xalert='/usr/bin/vi + $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'






-bash-3.00$ cat .profile
#       This is the default standard profile provided to a user.
#       They are expected to edit it to meet their own needs.

MAIL=/usr/mail/${LOGNAME:?}
ORACLE_BASE=/opt/oracle; export ORACLE_BASE
ORACLE_HOME=${ORACLE_BASE}/product/10.2; export ORACLE_HOME
ORACLE_SID=db; export ORACLE_SID
PATH=/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin:$ORACLE_HOME/jdk:$ORACLE_HOME/perl5:$PATH; export PATH
TMPDIR=/var/tmp
TERM=vt220
ORACLE_TERM=vt220
UMASK=022
EDITOR=vi; export EDITOR
NLS_LANG="ENGLISH_UNITED KINGDOM.UTF8"; export NLS_LANG
# Use the following line for import/export only
#NLS_LANG="ENGLISH_UNITED KINGDOM.AL32UTF8"; export NLS_LANG
LC_ALL=en_US.UTF-8; export LC_ALL
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/ctx/bin:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
. \.brc


==========

 
 export ORACLE_PDB_SID=ORCLPDB  <<<<< change based on requirement ORACLE_SID_PDB - Connect as SYSDBA without password into a PDB directly
 
 sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 25 08:00:17 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        READ WRITE NO


=======================================================================================


 stty erase ^?
 stty erase ^H

 cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=prod

##  
export ORACLE_SID=ORCL
RAC=NO
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle

## based on your requirement !!!!

export EDITOR=vi
PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
SORACLE_SID=`echo $ORACLE_SID | tr '[:upper:]' '[:lower:]'`

if [ $RAC = 'NO' ] ; then
     SDBNAME=$SORACLE_SID
     ORACLE_UNQNAME=$ORACLE_SID
  else
     SDBNAME=`echo $SORACLE_SID | sed s/.$//`
     ORACLE_UNQNAME=`echo $ORACLE_SID | sed s/.$//`
fi

export ORACLE_UNQNAME
alias cdo='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_BASE'
alias bdump='cd $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace'
alias udump='cd $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace'
alias alert='tail -f -n100 $ORACLE_BASE/diag/rdbms/$SDBNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log'

alias sqlme='sqlplus / as sysdba'
alias home='cd $ORACLE_HOME'
alias db='export ORACLE_SID=orcl'
alias alert_db='tail -100f /u01/oracle/11.2.0/diag/rdbms/testdb/testdb/trace/alert_testdb.log'

## End


***************************************************






cat .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export TMOUT=
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
#export ORACLE_SID=

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin

alias DBV='cd /u01/app/dbvisit/dbvisit/standby'


export PATH
cat /etc/oratab




# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export TMOUT=
export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_SID=ibrac1

export GG_HOME=/dumps/gghome_capture
export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME:$PATH:$HOME/.local/bin:$HOME/bin

export PATH


echo " -MGMTDB /u01/app/12.2.0/grid "
echo " +ASM1   /u01/app/12.2.0/grid "
echo " ibrac1  /u01/app/oracle/product/12.2.0/dbhome_1 "
echo " capture /u01/app/oracle/product/12.1.0/dbhome_1 "
echo "  "
echo "  "
echo "  "
echo "  "

EDITOR=vi
SQLPATH=/home/oracle/dba
export PATH EDITOR SQLPATH

export ORACLE_PATH=/home/oracle/dba/






setsid ()
{
unset ORATAB
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
ORACLE_BASE=/u01/app/oracle
ORATAB=/etc/oratab
if tty -s
then
        if [ -f $ORATAB ]
        then
                line_count=`cat $ORATAB | grep -v ^# | sed 's/:.*//' | wc -l`
                # check that the oratab file has some contents
                if [ $line_count -ge 1 ]
                        then
                        sid_selected=0
                        while [ $sid_selected -eq 0 ]
                        do
                                sid_available=0
                                for i in `cat $ORATAB | grep -v ^# | sed 's/:.*//'`
                                        do
                                        sid_available=`expr $sid_available + 1`
                                        sid[$sid_available]=$i
                                        done
                                # get the required SID
                                case ${SETSID_AUTO:-""} in
                                        YES) # Auto set use 1st entry
                                        sid_selected=1 ;;
                                        *)
                                        i=1
                                        while [ $i -le $sid_available ]
                                        do
                                                printf "%2d- %10s\n" $i ${sid[$i]}
                                                i=`expr $i + 1`
                                        done
                                        echo ""
                                        echo "Select the Oracle SID with given number [1]:"
                                        read entry
                                        if [ -n "$entry" ]
                                        then
                                                entry=`echo "$entry" | sed "s/[a-z,A-Z]//g"`
                                                if [ -n "$entry" ]
                                                then
                                                        entry=`expr $entry`
                                                        if [ $entry -ge 1 ] && [ $entry -le $sid_available ]
                                                        then
                                                                sid_selected=$entry
                                                        fi
                                                fi
                                                else
                                                sid_selected=1
                                        fi
                                esac
                        done
                        #
                        # SET ORACLE_SID
                        #
                        export ORACLE_SID=${sid[$sid_selected]}
                        echo "Your profile configured for $ORACLE_SID with information below:"
                        echo ""
                        export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin
                        unset LD_LIBRARY_PATH
                        ORAENV_ASK=NO
                        . oraenv
                        alias oh="cd ${ORACLE_HOME}"
                        ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
                        if [ $ora_version -lt 11 ]
                        then
                        alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
                        alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        fi
                        if [ $ora_version -ge  11 ]
                        then
                        export ORACLE_UNQNAME=$ORACLE_SID
                        alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'
                        alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'
                        fi
                        alias pmon='ps -fu oracle | grep pmon | grep -v grep'
                        alias tns='ps -fu oracle | grep tns | grep -v grep'
                        alias oradiag='cd $ORACLE_BASE/diag/'
                        alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'
                        unset ORAENV_ASK
                        echo
                        #
                        #GIVE MESSAGE
                        #
                        else
                        echo "No entries in $ORATAB. no environment set"
                fi
        fi
fi
}

setsid


showsid()
{
echo ""
echo "ORACLE_SID=$ORACLE_SID"
echo "ORACLE_BASE=$ORACLE_BASE"
echo "ORACLE_HOME=$ORACLE_HOME"
echo "alias oh=cd ${ORACLE_HOME}"
echo "alias pmon='ps -fu oracle | grep pmon | grep -v grep'"
echo "alias tns='ps -fu oracle | grep tns | grep -v grep'"
ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
if [ $ora_version -ge  11 ]
then
echo "alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'"
echo "alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'"
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
echo "alias oradiag='cd $ORACLE_BASE/diag/'"
fi
if [ $ora_version -lt 11 ]
then
echo "alias bdump=cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
echo "alias talert=tail -f \"/u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias valert=\"view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
fi
echo ""
}

showsid


sqlplus / as sysdba



=============================


[oracle@wcp12cr2 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH


# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export TMOUT=
export ORACLE_BASE=/oracle/db
export ORACLE_HOME=/oracle/db/ohome
export ORACLE_SID=orcl

export GG_HOME=/dumps/gghome_capture
export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME:$PATH:$HOME/.local/bin:$HOME/bin

export PATH

echo " orcl  /oracle/db/ohome "

echo "  "
echo "  "
echo "  "
echo "  "

EDITOR=vi
SQLPATH=/home/oracle/dba
export PATH EDITOR SQLPATH

export ORACLE_PATH=/home/oracle/dba/






setsid ()
{
unset ORATAB
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
ORACLE_BASE=/u01/app/oracle
ORATAB=/etc/oratab
if tty -s
then
        if [ -f $ORATAB ]
        then
                line_count=`cat $ORATAB | grep -v ^# | sed 's/:.*//' | wc -l`
                # check that the oratab file has some contents
                if [ $line_count -ge 1 ]
                        then
                        sid_selected=0
                        while [ $sid_selected -eq 0 ]
                        do
                                sid_available=0
                                for i in `cat $ORATAB | grep -v ^# | sed 's/:.*//'`
                                        do
                                        sid_available=`expr $sid_available + 1`
                                        sid[$sid_available]=$i
                                        done
                                # get the required SID
                                case ${SETSID_AUTO:-""} in
                                        YES) # Auto set use 1st entry
                                        sid_selected=1 ;;
                                        *)
                                        i=1
                                        while [ $i -le $sid_available ]
                                        do
                                                printf "%2d- %10s\n" $i ${sid[$i]}
                                                i=`expr $i + 1`
                                        done
                                        echo ""
                                        echo "Select the Oracle SID with given number [1]:"
                                        read entry
                                        if [ -n "$entry" ]
                                        then
                                                entry=`echo "$entry" | sed "s/[a-z,A-Z]//g"`
                                                if [ -n "$entry" ]
                                                then
                                                        entry=`expr $entry`
                                                        if [ $entry -ge 1 ] && [ $entry -le $sid_available ]
                                                        then
                                                                sid_selected=$entry
                                                        fi
                                                fi
                                                else
                                                sid_selected=1
                                        fi
                                esac
                        done
                        #
                        # SET ORACLE_SID
                        #
                        export ORACLE_SID=${sid[$sid_selected]}
                        echo "Your profile configured for $ORACLE_SID with information below:"
                        echo ""
                        export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin
                        unset LD_LIBRARY_PATH
                        ORAENV_ASK=NO
                        . oraenv
                        alias oh="cd ${ORACLE_HOME}"
                        ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
                        if [ $ora_version -lt 11 ]
                        then
                        alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
                        alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        fi
                        if [ $ora_version -ge  11 ]
                        then
                        export ORACLE_UNQNAME=$ORACLE_SID
                        alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'
                        alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'
                        fi
                        alias pmon='ps -fu oracle | grep pmon | grep -v grep'
                        alias tns='ps -fu oracle | grep tns | grep -v grep'
                        alias oradiag='cd $ORACLE_BASE/diag/'
                        alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'
                        unset ORAENV_ASK
                        echo
                        #
                        #GIVE MESSAGE
                        #
                        else
                        echo "No entries in $ORATAB. no environment set"
                fi
        fi
fi
}




stty erase "^?"
stty erase "^H"


[oracle@wcp12cr2 ~]$

[oracle@wcp12cr2 ~]$ alias
alias l.='ls -d .* --color=auto'
alias ll='ls -l --color=auto'
alias ls='ls --color=auto'
alias sys='sqlplus "/ as sysdba"'
alias vi='vim'
alias vmctl='/oracle/scripts/vmctl.sh'
alias which='alias | /usr/bin/which --tty-only --read-alias --show-dot --show-tilde'
[oracle@wcp12cr2 ~]$








export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/dba_scripts/common/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=vihaan
export PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/dba_scripts/common/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp

=============================================================================


cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
export TMOUT=
export ORACLE_BASE=/u01/app/oracle
#export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_SID=ibrac1

export GG_HOME=/dumps/gghome_capture
export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME:$PATH:$HOME/.local/bin:$HOME/bin

export PATH


echo " -MGMTDB /u01/app/12.2.0/grid "
echo " +ASM1   /u01/app/12.2.0/grid "
echo " ibrac1  /u01/app/oracle/product/12.2.0/dbhome_1 "
echo " capture /u01/app/oracle/product/12.1.0/dbhome_1 "
echo "  "
echo "  "
echo "  "
echo "  "

EDITOR=vi
SQLPATH=/home/oracle/dba
export PATH EDITOR SQLPATH

export ORACLE_PATH=/home/oracle/dba/






setsid ()
{
unset ORATAB
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
ORACLE_BASE=/u01/app/oracle
ORATAB=/etc/oratab
if tty -s
then
        if [ -f $ORATAB ]
        then
                line_count=`cat $ORATAB | grep -v ^# | sed 's/:.*//' | wc -l`
                # check that the oratab file has some contents
                if [ $line_count -ge 1 ]
                        then
                        sid_selected=0
                        while [ $sid_selected -eq 0 ]
                        do
                                sid_available=0
                                for i in `cat $ORATAB | grep -v ^# | sed 's/:.*//'`
                                        do
                                        sid_available=`expr $sid_available + 1`
                                        sid[$sid_available]=$i
                                        done
                                # get the required SID
                                case ${SETSID_AUTO:-""} in
                                        YES) # Auto set use 1st entry
                                        sid_selected=1 ;;
                                        *)
                                        i=1
                                        while [ $i -le $sid_available ]
                                        do
                                                printf "%2d- %10s\n" $i ${sid[$i]}
                                                i=`expr $i + 1`
                                        done
                                        echo ""
                                        echo "Select the Oracle SID with given number [1]:"
                                        read entry
                                        if [ -n "$entry" ]
                                        then
                                                entry=`echo "$entry" | sed "s/[a-z,A-Z]//g"`
                                                if [ -n "$entry" ]
                                                then
                                                        entry=`expr $entry`
                                                        if [ $entry -ge 1 ] && [ $entry -le $sid_available ]
                                                        then
                                                                sid_selected=$entry
                                                        fi
                                                fi
                                                else
                                                sid_selected=1
                                        fi
                                esac
                        done
                        #
                        # SET ORACLE_SID
                        #
                        export ORACLE_SID=${sid[$sid_selected]}
                        echo "Your profile configured for $ORACLE_SID with information below:"
                        echo ""
                        export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/bin
                        unset LD_LIBRARY_PATH
                        ORAENV_ASK=NO
                        . oraenv
                        alias oh="cd ${ORACLE_HOME}"
                        ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
                        if [ $ora_version -lt 11 ]
                        then
                        alias bdump="cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
                        alias talert="tail -f /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        alias valert="view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log"
                        fi
                        if [ $ora_version -ge  11 ]
                        then
                        export ORACLE_UNQNAME=$ORACLE_SID
                        alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'
                        alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'
                        alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'
                        fi
                        alias pmon='ps -fu oracle | grep pmon | grep -v grep'
                        alias tns='ps -fu oracle | grep tns | grep -v grep'
                        alias oradiag='cd $ORACLE_BASE/diag/'
                        alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'
                        unset ORAENV_ASK
                        echo
                        #
                        #GIVE MESSAGE
                        #
                        else
                        echo "No entries in $ORATAB. no environment set"
                fi
        fi
fi
}

setsid


showsid()
{
echo ""
echo "ORACLE_SID=$ORACLE_SID"
echo "ORACLE_BASE=$ORACLE_BASE"
echo "ORACLE_HOME=$ORACLE_HOME"
echo "alias oh=cd ${ORACLE_HOME}"
echo "alias pmon='ps -fu oracle | grep pmon | grep -v grep'"
echo "alias tns='ps -fu oracle | grep tns | grep -v grep'"
ora_version=`echo $ORACLE_HOME | awk 'BEGIN {FS="/"} ; {print $6}' | awk 'BEGIN {FS="."} ; {print $1}'`
if [ $ora_version -ge  11 ]
then
echo "alias talert='tail -f $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias valert='view $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace/alert_${ORACLE_SID}.log'"
echo "alias bdump='cd $ORACLE_BASE/`adrci exec="show homes" | grep $ORACLE_SID`/trace'"
echo "alias alasm='cd $ORACLE_BASE/`adrci exec="show homes" | grep ASM`/trace'"
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
echo "alias oradiag='cd $ORACLE_BASE/diag/'"
fi
if [ $ora_version -lt 11 ]
then
echo "alias bdump=cd /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump"
echo "alias talert=tail -f \"/u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias valert=\"view /u01/app/oracle/admin/${ORACLE_SID//[!A-Za-z]}/bdump/alert_${ORACLE_SID}.log\""
echo "alias ldiag='cd $ORACLE_BASE/diag/tnslsnr/'"
fi
echo ""
}

showsid





function _gp ()
{
    SetIt=0;
    [ "$1" == "1" ] && [ ${#PWD} -gt $[COLUMNS-60] ] && SetIt=1;
    [ "$1" == "2" ] && [ ${#PWD} -lt $[COLUMNS-60+1] ] && SetIt=1;
    if [ "$SetIt" == "1" ]; then
        IsGit=0;
        GitDir="$PWD";
        while [ ${#GitDir} -gt 1 ] && [ "$IsGit" == "0" ]; do
            if [ -d "$GitDir/.git" ] && [ ! -e "$GitDir/.git/nawakom" ]; then
                IsGit=1;
            else
                GitDir=${GitDir%/*};
            fi;
        done;
        if [ "$IsGit" = "1" ]; then
            TmpStr="$(< "$GitDir/.git/HEAD")";
            TmpStr="${TmpStr##* }";
            TmpStr="${TmpStr/refs\/heads\//}";
            TrimedGitDir=${GitDir//\/}
                        NbLevels=$(expr ${#GitDir} - ${#TrimedGitDir})
            if [ ${NbLevels} -eq 1 ]; then
              Niveau1=$(dirname ${GitDir#${GitDir%/*}});
                        else if  [ ${NbLevels} -eq 2 ]; then
                 Niveau1=$(dirname ${GitDir#${GitDir%/*/*}});
                          else
                Niveau1=$(dirname ${GitDir#${GitDir%/*/*/*}});
                   fi
                        fi
            Niveau2_2=${GitDir##*/};
            Niveau2_1=${PWD/$GitDir/};
            GitDirX="$PWD";
            [ "$1" == "1" ] && builtin echo -e "\001\e[43m\e[30m\002 \001\xEE\002\x83\xAA ${Niveau1}/\001\e[31m\002${GitDir##*/}\001\e[30m\002${Niveau2_1} \001\e[41m\e[33m\002\001\xEE\x82\002\xB0\001\e[30m\002 \001\xEE\x82\002\xA0 $TmpStr \001\e[0m\e[31m\002\001\xEE\x82\002\xB0\001\e[m\e[0m\002\001\xEE\x82\002\xB0 ";
            [ "$1" == "2" ] && builtin echo -e "\001\e[43m\002\001\xEE\x82\002\xB0\001\e[30m\002 \001\xEE\002\x83\xAA ${Niveau1}/\001\e[31m\002${Niveau2_2}\001\e[30m\002${Niveau2_1} \001\e[41m\e[33m\002\001\xEE\x82\002\xB0\001\e[30m\002 \001\xEE\x82\002\xA0 $TmpStr \001\e[0m\e[31m\002\001\xEE\x82\002\xB0 ";
        else
            [ "$1" == "1" ] && builtin echo -e "\001\e[43m\e[30m\002 \001\xEE\002\x83\xAA ${PWD#${PWD%/*/*/*}/} \001\e[0m\e[0m\e[33m\002\001\xEE\x82\002\xB0\001\e[m\e[0m\002\001\xEE\x82\002\xB0 ";
            [ "$1" == "2" ] && builtin echo -e "\001\e[43m\002\001\xEE\x82\002\xB0\001\e[30m\002 \001\xEE\002\x83\xAA ${PWD#${PWD%/*/*/*}/} \001\e[m\e[0m\e[33m\002\001\xEE\x82\002\xB0 ";
        fi;
    fi
}

export PS1='$(_gp 1)$(_gp 2)\e[37m'



# If id command returns zero, you got root access.
if [ $(id -u) -eq 0 ];
then # you are root, set red colour prompt
  PS1="\\[$(tput setaf 1)\\]\\u@\\h:\\w #\\[$(tput sgr0)\\]"
else # normal
  PS1="[\\u@\\h:\\w] $"
fi


alias sqlme='sqlplus / as sysdba'

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Thursday, 29 September 2011

ORA-01555, snapshot too old errors on transactions

Monday, 26 September 2011

Oracle Undo statistics

Oracle Undo statistics ..

Oracle ASM balanced files report

Oracle ASM usage by file type

Oracle ASM Disk fail group

Oracle ASM diskgrop report

Oracle ASM diskgrop report ..

Sunday, 25 September 2011

Oracle default user

Saturday, 24 September 2011

Oracle RAC FAQ metalink 220970.1

Friday, 23 September 2011

Oracle Index metadata for given table

Thursday, 22 September 2011

Oracle user bash_profile file

Oracle DBA

anuj blog Archive