Oracle Default Users

‘ANONYMOUS’ — HTTP access to XDB ‘APPQOSSYS’ — QOS system user ‘AUDSYS’ — audit super user ‘BI’ — Business Intelligence ‘CTXSYS’ — Text ‘DBSNMP’ — SNMP agent for OEM ‘DIP’ — Directory Integration Platform ‘DMSYS’ — Data Mining ‘DVF’ — Database Vault ‘DVSYS’ — Database Vault ‘EXDSYS’ — External ODCI System User ‘EXFSYS’ — Expression…

Script to add trandata for GoldenGate

Here is a simple script to check which tables are missing trandata and generate the GG (so call DDL) to enable it on them. select ‘add trandata ‘||t.owner||’.’||t.table_name stmt from (select owner, table_name from dba_tables) t, (select owner, table_name from dba_log_groups) s where t.owner = s.owner (+) and t.table_name = s.table_name (+) and s.table_name is…

How to change properties/attributes of partition table?

At times one would want to change properties/attributes of a table so new partitions are affected by change not the partitions already created, like example compression, change PCTFREE, default tablespace. -- enable compression for all operations and change PCTFREE SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5; -- Change default…

How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode=first_rows; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc Contents of…

How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’); Output: RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION —————————— ——————- ————— processes 146 196 sessions…

Example shows how “DISABLE TABLE LOCK” works

Following example shows what happens when locks are disabled on the TABLE. -- disable lock on a table SQL> alter table t disable table lock; Table altered. -- shows one can’t drop table as table locks are disable SQL> drop table t; drop table t * ERROR at line 1: ORA-00069: cannot acquire lock —…