/************************ wait.sql *************************/ set pages 2000 set lines 132 column p1text format a18 column event format a35 column WT format 9999 column SW format 9999 select sid, event,p1text,p1raw,p1,p2,p3,seconds_in_Wait “SW”,Wait_time “WT” from v$session_wait where event not in( ‘KXFQ: Dequeue Range Keys – Slave’, ‘KXFQ: Dequeuing samples’, ‘KXFQ: kxfqcls – consumer closing TQ’, ‘KXFQ: kxfqdeq – dequeue…
The Mutating table error is a well-known problem encountered in development; most developers have come across this error. ORA-04091: table is mutating, trigger/function may not see it The basic reason for this error is the way that Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the…
Read More “Mutating Table Error while using database trigger” »
When u add a datafile in Primary then those changes are not immediately propagated to standby. Once Log with this changes applied to standby, standby database create a filename in $ORACLE_HOME/dbs. This is just a file name and does not have physical file. After this even all log apply service will fail as this is…
Read More “Adding Datafile on Primary Server and Impact on Standby Server” »
When you are using tablespace with segment space management AUTO then following command will not show FREELISTS. Alter table move tablespace my_data storage (initial 20M freelists 9) initrans 9; If tablespace is crreated with segment space management AUTO Then USER_TABLES.FREELISTS does not show FREELISTs as 9, but it displays blank in FREELISTS column.
(*) if you grant any role to grantee while grantee is connected to database. He cannot use that role immediately. To use it immediately he needs to execute “Set role role_name” in his session. This will enable that role for him or he needs to logoff and login again. (*) If you have given any…
=================================================================== STEP-I First, calculate the total block header size: =================================================================== block header = fixed header + variable transaction header + table directory + row directory where: fixed header = 57 bytes (the 4 bytes at the end of the block have already been taken account of in the 24 bytes for the cache header) variable…
Read More “TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1” »
=========================================================================================================================================== Fine-Grained Auditing Part-I: Reference : from Arup Nanda on http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html ——————————————————————————————————————————————- (*) Traditional Oracle Database auditing options let you track the actions users perform on objects at the macro level for example, if you audit for SELECT statements on a table, you can track who selected data from the table. However, you don’t know…
select c.sid, c.serial#, f.spid as osprocess, c.username, a.object_id, b.object_name, c.program, c.status, d.name, c.osuser, e.sql_text, DBMS_ROWID.ROWID_CREATE(1, c.ROW_WAIT_OBJ#, c.ROW_WAIT_FILE#, c.ROW_WAIT_BLOCK#, c.ROW_WAIT_ROW# ) locked_rowid from v$Locked_object a, All_objects b, v$session c, audit_actions d, v$sqlarea e, v$process f where a.object_id=b.data_object_id and a.session_id =c.sid(+) and c.command=d.action and c.row_wait_obj# != -1 and c.sql_id=e.sql_id and c.paddr = f.addr;
************************************** **** Building Our Own Namespaces ***** ************************************** The USERENV namespace does store extensive information, but the power of SYS_CONTEXT does not stop there. You can also create secured namespaces and store context in them for retrieval within a session or across the instance. For example, if I create a new namespace via the CREATE…
Read More “Building Our Own Namespaces with “Create Context”” »
Note : 1011899.6 – Roles and Creating Stored Objects / Views Note : 180028.1 – Set up a Secure Access to Application Data within a Database: DBAs, Schemas and Users.
