select b.username, a.disk_reads, a.executions, a.sql_text from v$sqlarea a, dba_users b where a.disk_reads > 100000 and a.parsing_user_id = b.user_id order by a.disk_reads /
Category: SQL – DDL
SQL Joins Made Easy
Run SQLcl commands from SQL Developer
SQLcl has many new little tricks that can help us on a daily basis, below is an example how we can execute SQLcl command from SQL developer: Here is an info <table> Here is an DDL for the same table: ddl <table name>
Extract Index DDL from expdp
I recently wanted to drop the indexes during a load and recreate after. There are several ways to do that, but this time since I had a schema export taken I wanted to extract from the dump file. Here are the scripts files: JDEPROD@proa1-/cloudfs/DBA/scripts$ cat expdp_JDE8_schemas.sh export NOW=$(date +"%F_%H%M%S") export ORACLE_SID=JDEPROD export ORAENV_ASK=NO . oraenv … Continue reading Extract Index DDL from expdp
Purge DDL tables in GoldenGate for multiple instances
When setting up a DDL replication in Integrated Capture there are couple tables that gets populated on a regular basis, Oracle provides a purge process that one can add to the manager parameter file and it will purge as configured. e.g: userid ogg, password xxxx PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30 PURGEMARKERHISTORY MINKEEPDAYS 3, … Continue reading Purge DDL tables in GoldenGate for multiple instances
How to add a new not null column in a table without providing a default value?
The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value. SQL> create table scott.testn ( x number not null); Table created. SQL> insert into scott.testn values ( 11 ); 1 row created. SQL> commit; Commit complete. -- First … Continue reading How to add a new not null column in a table without providing a default value?
alexlima.com 