Oracle table PRIVILEGE info

tab_priv.sql


TABLE PRIVILEGE
TABLE PRIVILEGES
tab priv

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

-- TABLE_PRIVILEGES

-- Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:





Accept owner prompt 'Enter owner Name or all..'
col grantee format a15
col owner format a15
col table_name format a25
col grantor format a15
col select_priv format a1 Heading 's|e|l'
col insert_priv format a1 Heading 'i|n|s'
col delete_priv format a1 Heading 'D|E|L'
col update_priv format a1 Heading 'U|P|D'
col references_priv format a1 Heading 'R|E|F'
col alter_priv format a1 Heading 'A|l|t'
col index_priv format a1 heading 'I|N|D'
col created format a11 heading 'Grnted on:'
break on owner skip 4 on table_name skip 1 on report
set linesize 130
btitle skip 1 center-
'Y=granted,N=not Granted,G=granted with grant option,'-
's=Granted on specific column,A=granted on A11 olumns'-

select owner,TABLE_NAME,GRANTEE,GRANTOR,CREATED,SELECT_PRIV,INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV,REFERENCES_PRIV,ALTER_PRIV,INDEX_PRIV
from TABLE_PRIVILEGES
where owner not in ('SYS')
and owner=upper('&owner')
order by owner,table_name,grantor,grantee
/



SQL> @tab_priv
Enter owner Name or all..SYSTEM
old 4: and owner=upper('&owner')
new 4: and owner=upper('SYSTEM')

s i D U R A I
e n E P E l N
OWNER TABLE_NAME GRANTEE GRANTOR Grnted on: l s L D F t D
--------------- ------------------------- --------------- --------------- ----------- - - - - - - -
SYSTEM DEF$_AQCALL SYS SYSTEM G N N N N N N

DEF$_CALLDEST SYS SYSTEM G N N N N N N

DEF$_DESTINATION SYS SYSTEM G N N N N N N

DEF$_ERROR SYS SYSTEM G N N N N N N

HELP PUBLIC SYSTEM Y N N N N N N

MVIEW$_ADV_INDEX PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_OWB PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_PARTITION PUBLIC SYSTEM Y A Y A N N N

OL$ PUBLIC SYSTEM Y A Y A N N N

OL$HINTS PUBLIC SYSTEM Y A Y A N N N

OL$NODES PUBLIC SYSTEM Y A Y A N N N

PRODUCT_PRIVS PUBLIC SYSTEM Y N N N N N N

REPCAT$_REPPROP SYS SYSTEM G N N N N N N

REPCAT$_REPSCHEMA SYS SYSTEM G N N N N N N



Y=granted,N=not Granted,G=granted with grant option,s=Granted on specific column,A=granted on A11 olumns

14 rows selected.



SELECT grantor, table_name, privilege FROM user_tab_privs_recd;
/

set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN ( SELECT role FROM dba_roles)
GROUP BY table_name, grantee;

Oracle when datafile added

datafile added date
datafile added


select file_id, fecrc_tim creation_date, file_name, tablespace_name
from x$kccfe int, dba_data_files dba
where dba.file_id = int.indx + 1
order by file_id


FILE_ID CREATION_DATE FILE_NAME TABLESPACE_NAME
------- -------------------- -------------------------------------------------- ------------------------------
1 08/15/2009 00:16:48 /opt/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 08/15/2009 00:17:00 /opt/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
3 08/15/2009 00:52:15 /opt/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
4 08/15/2009 00:17:30 /opt/app/oracle/oradata/orcl/users01.dbf USERS
5 01/10/2011 15:34:17 /opt/app/oracle/oradata/orcl/example01.dbf EXAMPLE
6 04/29/2011 06:54:39 /opt/app/oracle/oradata/orcl/anujtest.dbf ANUJTEST
7 05/12/2011 08:30:08 /opt/app/oracle/oradata/orcl/tsapexf01.dbf TSAPEXF
8 05/12/2011 08:31:48 /opt/app/oracle/oradata/orcl/tsapexu01.dbf TSAPEXU
9 06/28/2011 11:56:10 /opt/app/oracle/oradata/orcl/test.dbf TEST
10 07/04/2011 12:07:49 /opt/app/oracle/oradata/orcl/rman.dbf RMAN

ORA-13605: THE SPECIFIED TASK OR OBJECT SYS_AUTO_SQL_TUNING_TASK DOES NOT EXIST

ORA-13605: THE SPECIFIED TASK OR OBJECT SYS_AUTO_SQL_TUNING_TASK DOES NOT EXIST



Solution
run the $ORACLE_HOME/rdbms/admin/execsqlt.sql script



SQL> @$ORACLE_HOME/rdbms/admin/execsqlt.sql

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Oracle Warning: log write elapsed time

log writer debug module

-bash-3.2$ cat aptdb_lgwr_4210.trc
Trace file /aptus/oracle/diag/rdbms/aptdb/aptdb/trace/aptdb_lgwr_4210.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /aptus/oracle/product/11.2.0/db_1
System name: Linux
Node name: apt-rdbms-01.xxx.co.uk
Release: 2.6.18-238.12.1.el5
Version: #1 SMP Tue May 31 13:22:04 EDT 2011
Machine: x86_64
Instance name: aptdb
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 4210, image: oracle@apt-rdbms-01.XXXX.co.uk (LGWR)


*** 2011-09-09 13:48:39.779
*** SESSION ID:(189.1) 2011-09-09 13:48:39.779
*** CLIENT ID:() 2011-09-09 13:48:39.779
*** SERVICE NAME:(SYS$BACKGROUND) 2011-09-09 13:48:39.779
*** MODULE NAME:() 2011-09-09 13:48:39.779
*** ACTION NAME:() 2011-09-09 13:48:39.779


*** TRACE FILE RECREATED AFTER BEING REMOVED ***


*** 2011-09-09 13:48:39.695
Warning: log write elapsed time 851ms, size 1KB
(set event 10468 level 4 to disable this warning)

*** 2011-09-09 13:55:45.549
Warning: log write elapsed time 1460ms, size 1KB
(set event 10468 level 4 to disable this warning)





-bash-3.2$ pwd
/aptus/oracle/diag/rdbms/aptdb/aptdb/trace
-bash-3.2$ cd /aptus/oracle/product/11.2.0/db_1/rdbms/mesg/


-bash-3.2$ ls -ltr oraus.msg
-rw-r--r-- 1 oracle oinstall 4777005 Aug 14 2009 oraus.msg


-bash-3.2$ cat oraus.msg|grep "log writer debug module"
10468, 00000, "log writer debug module"



10468, 00000, "log writer debug module"
// *Document: NO
// *Cause:
// *Action: Set this event to the appropriate level for log writer debugging.



warning indicates slow write to the log files , you can put log file on the separate disk .It would reduce the load on the disk



You can disable the trace by following

ALTER system SET EVENTS '10468 trace name context forever, level 4';

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-32004: obsolete or deprecated parameter




SQL> startup ;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance


go to alert log file and find out Deprecated parameter

in this case

Deprecated system parameters with specified values: remote_os_authent


SQL> alter system reset remote_os_authent scope=spfile;

System altered.


example


SQL> alter system reset log_archive_start scope=spfile sid='*' ;

System altered.






SQL> !hostname
apt-amd-02

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> startup; <<<<<<<<<<<<<<<< this time no error
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 629147640 bytes
Database Buffers 205520896 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.



SQL> select name, isspecified from v$obsolete_parameter where isspecified='TRUE';

no rows selected

Oracle process / processes  , sessions  and transactions .

Oracle backgrounds (give them 25) plus your user sessions (number of shared servers/dedicated servers),

number of job processes/aq processes you want to permit, parallel execution servers if applicable.

Select count(*) from v$process where BACKGROUND=1;

COUNT(*)

----------

25

PROCESSES = Operating System Dependant

SESSIONS = (1.1 * PROCESSES) + 5

TRANSACTIONS = 1.1 * SESSIONS

processes (x) = based on your system

sessions (s ) = x*1.1+5

transactions = s * 1.1

Sql command to increase the value

alter system set PROCESSES=100 scope=SPFILE;

alter system set SESSIONS=115 scope=SPFILE;

alter system set TRANSACTIONS=126 scope=SPFILE;

set pages 100  feed off echo offt verify off

col resource_name        head "Resource" for a30

col current_utilization  head "Current" for 99999

col max_utilization      head "Max" for 999999999

col initial_allocation   head "initial" for a10

col limit_value          head "Limit" for a10

--spool resource_limits.lis

ttitle 'Resource Limits'

select resource_name resource_name,current_utilization current_utilization,max_utilization max_utilization,initial_allocation initial_allocation,limit_value limit_value

from v$resource_limit;

 netstat -apn | grep 1521 | grep TIME | wc -l

to check file open 

for i in `ps -ef|grep -i ora_ | awk '{ print $2 }'` ; do echo "PID # ${i} open files count : $(sudo ls -l /proc/${i}/fd | wc -l)" ; done

in file 

for i in `ps -ef|grep -i ora_ | awk '{ print $2 }'` ; do echo "PID # ${i} open files count : $(sudo ls -l /proc/${i}/fd | wc -l)" ; done > file.txt

Resource Current Max initial Limit

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

processes 29 49 150 150

sessions 37 67 247 247

enqueue_locks 20 57 3010 3010

enqueue_resources 19 55 1304 UNLIMITED

ges_procs 0 0 0 0

ges_ress 0 0 0 UNLIMITED

ges_locks 0 0 0 UNLIMITED

ges_cache_ress 0 0 0 UNLIMITED

ges_reg_msgs 0 0 0 UNLIMITED

ges_big_msgs 0 0 0 UNLIMITED

ges_rsv_msgs 0 0 0 0

gcs_resources 0 0 0 0

gcs_shadows 0 0 0 0

dml_locks 0 0 1084 UNLIMITED

temporary_table_locks 0 3 UNLIMITED UNLIMITED

transactions 0 0 271 UNLIMITED

branches 0 0 271 UNLIMITED

cmtcallbk 0 3 271 UNLIMITED

max_rollback_segments 11 11 271 65535

sort_segment_locks 0 10 UNLIMITED UNLIMITED

k2q_locks 0 0 494 UNLIMITED

max_shared_servers 1 1 UNLIMITED UNLIMITED

parallel_max_servers 0 0 10 3600

col resource_name format a15 heading "Parameters"

col max_utilization format 99999 heading "Max Value Reached"

col current_utilization format 99999 heading "Current Value"

col initial_allocation format a10 heading "SPFILE Value"

SELECT upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM v$resource_limit

WHERE resource_name in ('processes', 'sessions');

Parameters Current Value Max Value Reached SPFILE Val

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

PROCESSES 29 49 150

SESSIONS 37 67 247

SQL> select name, value from v$sysstat where name like '%logon%';

NAME VALUE

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

logons cumulative 86871

logons current 26

Note what Metalink Note:187405.1 and Note:184821.1

unix parameter

oracle@apt-amd-02:~> cat /proc/sys/kernel/sem

250 32000 100 128

SEMMSL SEMMNS SEMOPM SEMMNI

These values represent SEMMSL, SEMMNS, SEMOPM, and SEMMNI.

SEMMSL = The maximum number of sempahores that can be in one semaphore set. It should be same size as maximum number

of Oracle processes (The PROCESSES parameter in the init.ora file).

SEMMNS Parameter = SEMMSL * SEMMNI (250*128=32000)

SEMOPM = Oracle recommends to set SEMOPM to a minimum value of 100 for 9i R2 and 10g R1/R2 databases on x86 and x86-64 platforms

SEMMNI = Oracle recommends SEMMNI to be at least 128 for 9i R2 and 10g R1/R2 databases except for 9i R2 on x86 platforms where the

minimum value is lower. Since these recommendations are minimum settings, it's best to set it always to at least 128 for 9i and 10g databases on x86 and x86-64 platforms.

oracle@apt-amd-02:~> ipcs -a

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 262144 root 644 80 2

0x00000000 294913 root 644 16384 2

0x00000000 327682 root 644 280 2

0x00000000 819211 oracle 660 4096 0

0x00000000 851980 oracle 660 4096 0

0x8e5a1228 884749 oracle 660 4096 0

------ Semaphore Arrays --------

key semid owner perms nsems

0xa5e152d8 327682 oracle 660 154

------ Message Queues --------

key msqid owner perms used-bytes messages

all shared memory segments that are allocated on the system

oracle@apt-amd-02:~> ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 262144 root 644 80 2

0x00000000 294913 root 644 16384 2

0x00000000 327682 root 644 280 2

0x00000000 819211 oracle 660 4096 0

0x00000000 851980 oracle 660 4096 0

0x8e5a1228 884749 oracle 660 4096 0

oracle@apt-amd-02:~> ipcs -m -i 819211

Shared memory Segment shmid=819211

uid=1001 gid=1000 cuid=1001 cgid=1000

mode=0660 access_perms=0660

bytes=4096 lpid=23741 cpid=5902 nattch=0

att_time=Thu Sep 8 08:40:00 2011

det_time=Thu Sep 8 08:40:00 2011

change_time=Mon Aug 1 08:09:25 2011

to delete the memory

oracle@apt-amd-02:~> ipcrm shm 819211

resource(s) deleted

oracle@apt-amd-02:~> ipcrm shm 819211

resource(s) deleted

oracle@apt-amd-02:~> sysresv

skgm error 27124: errno = 22, info = 3, 884749, 0, 0, 884749, 0, 4

IPC Resources for ORACLE_SID "orcl" :

Shared Memory

ID KEY

No shared memory segments used

Semaphores:

ID KEY

327682 0xa5e152d8

Oracle Instance not alive for sid "orcl"

oracle@apt-amd-02:~> sysresv -i

skgm error 27124: errno = 22, info = 3, 884749, 0, 0, 884749, 0, 4

IPC Resources for ORACLE_SID "orcl" :

Shared Memory

ID KEY

No shared memory segments used

Semaphores:

ID KEY

327682 0xa5e152d8

Oracle Instance not alive for sid "orcl"

Remove ipc resources for sid "orcl" (y/n)?y

Done removing ipc resources for sid "orcl"

semaphore settings, run:

ipcs -ls

oracle@apt-amd-02:~> ipcs -ls

------ Semaphore Limits --------

max number of arrays = 128

max semaphores per array = 250

max semaphores system wide = 32000

max ops per semop call = 100

semaphore max value = 32767

Unix Swap Space Size info

grep SwapTotal /proc/meminfo
cat /proc/swaps
free



# grep SwapTotal /proc/meminfo
SwapTotal: 2064376 kB


# free
total used free shared buffers cached
Mem: 1019568 927560 92008 0 143616 72836
-/+ buffers/cache: 711108 308460
Swap: 2064376 106496 1957880



# cat /proc/swaps
Filename Type Size Used Priority
/dev/dm-1 partition 2064376 106496 -1




vmstat 3 100

procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 668944 299936 7660 493464 2 2 31 76 12 0 21 4 74 2 0


si: Amount of memory swapped in from disk (/s).
so: Amount of memory swapped to disk (/s).