Home » RDBMS Server » Server Administration » dite long pls help
dite long pls help [message #195510] Fri, 29 September 2006 02:20 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi

this is dite long issue as i need to genrate report i need ur help,as i could not put in different topics .pls give me with

some info on
===issue====
Space Monitoring: Anticipating the growth of the objects and planning accordingly which includes Disk free space, monitoring

growth of the tablespaces and capacity planning at the tablespace and segment level

Last Archived Log

how to find mode of Database open/closed read/write etc

--how do i find or check weekly
Database Growth
CurrentSize_in_MB Increased_by_MB GrowthPercent

Table Growth
CheckDate SegmentName Extents

Index Growth
CheckDate SegmentName Extents

Lob Growth
CheckDate SegmentName Extents

Log switches
Date Logswitch Count

Row Chaining
CheckDate TableName No.ofRows ChainedRows ChainedPercent

Tables having more than 10% Chained Rows
CheckDate TableName No.ofRows ChainedRows ChainedPercent

Tablespaces Growth
TablespaceAllocatedSpace(MB) IncreasedbyMB GrowthPercent

Total Log switches this week
TotalLogswitchcount

Log switches
Date LogswitchCount

Datafiles added to Database this week
Datafile Path Datafile Name

Snapshots Status
Snapshot Name Refresh Group Last Refresh Next Refresh Failures

Objects Created this week
Owner Object Name Type Bytes Tablespace Created On

Objects Modified this week
Owner Object Name Object Type Last Transaction Time

Errors From Alert Log File For this week
Error Description

and Report on any Invalid Objects, Disabled Triggers, Broken Jobs, Unusable indexes, Objects which cannot extend etc.

thanxs


Re: dite long pls help [message #195860 is a reply to message #195510] Mon, 02 October 2006 16:30 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Oracle's Enterprise shows many of the things you are looking for.

In your .profile put:
showlsnr ()
{
ps -ef|grep "LIST"|grep -v "grep"|more
}
showprocdb ()
{
ps -ef|grep $ORACLE_SID|grep "ora_"|grep -v "grep"|more
}
showprocall ()
{
ps -ef|grep $ORACLE_SID|grep -v "grep"|more
}

alias donhelp="fgrep -e '##' /usr/orap816/scripts/don-funcds |more"
#
# Helpful tips
## Show history of commands
## ESC K
## Show all files under currend directory(and sub) that contain a string
## find ./ -print |xargs grep -i <string>
##
## Show total disk usage for a directory
## du -s <directory>
##
# Move among directories
#
## cd to arch
## cdarch
alias cdarch='cd $DBA/$ORACLE_SID/arch'
## cd to bdump
## cdbdump
alias cdbdump='cd $ORACLE_HOME/trace/bdump'
## cd to udump
## cdudump
alias cdbdump='cd $ORACLE_HOME/trace/udump'
## cd to dbs
## cddbs
alias cddbs='cd $ORACLE_HOME/dbs'
##
##########################
## Oracle monitoring commands
#
## Start os2p database
## dbastartos2p
alias dbastartos2p='/usr/os2p816/scripts/dbasu.ksh'
#
## Show last 200 lines of database alert log
## showalert
alias showalert='tail -200 $ORACLE_HOME/trace/bdump/alert_$ORACLE_SID.log |more'
#
## Display current number of connected users
## showusrcnt
alias showusrcnt='ps -ef |grep $ORACLE_SID |grep -v grep |grep -v ora_ |wc -l'
##
##
##########################
## System monitoring commands
#
## Display current top CPU consumers
## showtopcpu
alias showtopcpu='ps auxgw |sort +2 |tail'
## Show active dedicated Oracle user
## showoraconnections
##
## Display vmstat 8 times
alias showvmstat1='vmstat -S 5 5'
##
## SAR area
## -b = Solaris buffer cache activity
## -w = Solaris swapping acitivity
## -u = CPU utilization
## -r = memory utilization
## -p = Solaris paging activity
##
## Display sar -p (paging)
## showsarp
alias showsarp='sar -p 5 5'
## Display sar -w (swapping activity)
## showsarw
alias showsarw='sar -w 5 5'
## Display sar -b (disk performance)
## showsarb
alias showsarb='sar -b 5 5'
## Display sar -u (look for %wio - % of cpu time spent waiting on io)
## showsaru
alias showsaru='sar -u 5 5'
##
##
# Display iostat area
##
alias showiostat='iostat 5 5'
##
## Display Swapfile data
## showswap
alias showswap='swap -l'
##
## Display mpstat data
## showmpstat
alias showmpstat='mpstat 5 5'
##
alias showconnections='ps -ef |grep $ORACLE_SID |grep -v |grep -v ora_ |wc -l'
## Show RAM size
## showram
alias showram='prtconf |grep -i mem'
## Show allocated memory segments
## showallocatedmemory
alias showallocatedmemory='ipcs -pmb'
## Show number of CPU's
## showcpucount
alias showcpucount='psrinfo -v |grep "Status of Processor" |wc -l'
## Show used Semaphores
## showsemaphores
alias showsemaphores='ipcs -sa'
## show users and privs
alias showusers.ksh='. ./showusers.ksh'
##
#
## - end



spool ${DIR}/ocr_${ORACLE_SID}.userprivs.txt
set pagesize 2000
set echo off
set feed off
set verify off

-- Users list
ttitle 'All users'
select username "Users"
from dba_users
where username not in ('SYS','SYSTEM','OUTLN',
'DBSNMP','SCOTT','DB_CONTROL',
'OPS$ORACLE','ORADBA')
/

-- All user's granted
break on user skip 1 on user
col user format a15
col grant format a30
ttitle 'All users granted'
select grantee "User" ,granted_role "Grant", 'role' "Type"
from dba_role_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and granted_role not in ('CONNECT')
union all
select grantee "User", privilege "Grant", 'priv' "Type"
from dba_sys_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and privilege not in ('CONNECT')
union all
select grantee "User", owner||'.'||table_name "Grant", lower(privilege)
"Type"
from dba_tab_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
order by 1
/

-- All role's granted



#!/bin/ksh
#@(#) show users and prives on S2000
###
# Korn script to show users and their profiles
#
# Autor: Neil MacDannald
# Date: 03/10/2005
###
#
# Start the job
#
sqlplus ORADBA/<a password> << EOF
@showusers.sql
exit
EOF
#*************
# For now just sent the report in full
#*************

cat /usr/orap816/scripts/userprivs.txt |mailx -s "$ORACLE_SID user privs"\
nmacdannald@deltacollege.edu\
jazzare@deltacollege.edu\
cmacdannald@deltacollege.edu\
ncuneo@deltacollege.edu
Previous Topic: Need Information
Next Topic: Installing OEM on WindowsXP
Goto Forum:
  


Current Time: Sat Dec 03 06:07:41 CST 2016

Total time taken to generate the page: 0.06437 seconds