Home » RDBMS Server » Server Administration » dite long pls help
| dite long pls help [message #195510] |
Fri, 29 September 2006 02:20  |
oracle_coorgi
Messages: 188
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  |
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
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 07:33:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
