Search This Blog

Total Pageviews

Friday 8 July 2011

Oracle port information

Oracle port info
Oracle port information



oracle@apt-amd-02:/opt/app/oracle/product/11.2/install> ls -ltr port*
-rw-r----- 1 oracle oinstall 95 2011-01-10 15:44 portlist.ini
oracle@apt-amd-02:/opt/app/oracle/product/11.2/install> cat portlist.ini
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
oracle@apt-amd-02:/opt/app/oracle/product/11.2/install>





http://hostname.domainname:7777/ --> Infrastructure

http://hostname.domainname:7778/ --> Mid-Tier thru Oracle Web Cache

http://hostname.domainname:7779/ --> Mid-Tier directly on Oracle HTTP Server

http://hostname.domainname:7777/pls/orasso
--> Login and logout as orcladmin/

http://hostname.domainname:7777/oiddas
--> Login and logout as orcladmin/

http://hostname.domainname:7778/pls/portal/ --> Oracle Portal
http://hostname.domainname:1810/ --> Application Server Control




Location of PORT number: $ORACLE_HOME/install/portlist.ini

Location of useful links: $ORACLE_HOME/install/setupinfo.txt

Location of Apache Config: $ORACLE_HOME/Apache/Apache/conf/httpd.conf

List user in system tablespace

set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 130
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks



column username format a30 heading "USER" wrap
column guilty heading "USES SYSTEM FOR" word_wrap

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool systemTablespaceUsersInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
center "Users having SYSTEM as default tablespace in " &s_dbname " on " &s_machine skip 2

select username,
decode(default_tablespace,'SYSTEM',
decode(temporary_tablespace,'SYSTEM',
'DEFAULT AND TEMPORARY TABLESPACES','DEFAULT TABLESPACE'),
'TEMPORARY TABLESPACE') guilty
from sys.dba_users
where default_tablespace = 'SYSTEM'
or temporary_tablespace = 'SYSTEM';

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on

Oracle user privileges report

Oracle user priv
Oracle user privilege



from web


column "Username" format a14 heading "Username"
column "Obj Owner" format a14 heading "Object Owner" wrap
column "Obj" format a25 heading "Object" wrap
column "Obj Priv" format a10 heading "Object|Privileges" wrap
column "Sys Priv" format a24 heading "System|Privileges" wrap
column "Granted Role" format a22 heading "Granted Role" wrap
column "dummy" noprint

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on

PROMPT The database has the following schema owners :
PROMPT
-- Get list of segment owners
select distinct(owner) "schema owner" from dba_segments
where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
order by owner;

-- allow user name to be entered. NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of Oracle user to show privileges for (return for all users) : '

--set termout off
set feedback off

set head on

spool userPrivilegesInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
center "List of Privileges For Database Users in " &s_dbname " on " &s_machine skip 2

break on Username skip 1

select username "Username",
owner "Obj Owner",
table_name "Obj",
privilege "Obj Priv",
' ' "Sys Priv",
' ' "Granted Role",
1 "dummy"
from dba_users u,
dba_tab_privs t
where u.username = t.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
union
select username,
' ',
' ',
' ',
privilege,
' ',
2 "dummy"
from dba_users u,
dba_sys_privs s
where u.username = s.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
union
select username,
' ',
' ',
' ',
' ',
granted_role,
3 "dummy"
from dba_users u,
dba_role_privs r
where u.username = r.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
order by 1, 7;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on

SQL> @priv

NAME
---------
ORCL

1 row selected.


TODAY
---------------------------------------------
July 08, 2011

1 row selected.


HOST_NAME
----------------------------------------------------------------
apt-amd-02

1 row selected.

The database has the following schema owners :


schema owner
------------------------------
ANUJ
ANUJREP
ANUJTEST
APEX_030200
APEX_040000
CTXSYS
EXFSYS
GGATE
HR
IX
MDSYS
OE
OLAPSYS
ORDDATA
ORDSYS
PM
RMAN
SH
WMSYS
XDB

20 rows selected.

Enter the name of Oracle user to show privileges for (return for all users) : ANUJ
old 12: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 12: and nvl(u.username,1) like upper(nvl('ANUJ','%'))
old 25: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 25: and nvl(u.username,1) like upper(nvl('ANUJ','%'))
old 38: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 38: and nvl(u.username,1) like upper(nvl('ANUJ','%'))

July08,2011 List of Privileges For Database Users in ORCL on apt-amd-02

Object System
Username Object Owner Object Privileges Privileges Granted Role
-------------- -------------- ------------------------- ---------- ------------------------ ----------------------
ANUJ UNLIMITED TABLESPACE
CONNECT
RESOURCE

Oracle DBA

anuj blog Archive