create database link syntax
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database: CREATE DATABASE LINK local CONNECT TO hr IDENTIFIED BY hr USING ‘local’;
Category: SQL scripts
AWR settings- MMON is not taking snapshot.
POTENTIAL SOLUTION(S) for AWR Snapshot Error ============================================ (*) To take the snapshots we need to populate WRM$_SNAPSHOT table successfully and for that instance metadata should be present in WRM$_DATABASE_INSTANCE. (*) To regenerate the AWR objects run catnoawr.sql and catawr.sql scripts from $ORACLE_HOME/rdbms/admin (*) Then RESTART the database So, if we have regenerated the AWR objects…
Changing the Global Database Name
Changing the Global Database Name: Example The following statement changes the global name of the database and includes both the database name and domain: ALTER DATABASE RENAME GLOBAL_NAME TO demo.world.oracle.com;
Vivek Tuning for Row Locks.
/************************ 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…
Mutating Table Error while using database trigger
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” »
USER_TABLES.Freelists
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.
My Test Case On 21-OCT-2005
(*) 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…
TABLE SIZING WITH DB_BLOCK ARCHITECTURE Reference : Metalink note : 10640.1
=================================================================== 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” »
FGA Part-I
=========================================================================================================================================== 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…
Updated LCK.SQL file.
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;