Feed aggregator

Optimal PCTFREE, PCTUSED and INITTRANS settings.

Tom Kyte - Thu, 2017-06-08 22:46
Hello Tom We have a online transaction database with huge volumes of inserts/updates. Initially we have set a value of PCTFREE 40, PCTUSED 50 and INITTRANS 100 on all the tables in the database schema. However now we know that there are some ta...
Categories: DBA Blogs

How long will Oracle APEX remain an included feature of the Oracle Database?

Joel Kallman - Thu, 2017-06-08 14:44
Another great question today from an internal employee.  Tom, a Managed Cloud Services Delivery Director, asked:
"I am the OMCS Client Manager for <redacted>.  They are currently a large Discoverer customer who would like to convert to Oracle APEX.  To that goal, they have asked me to find out how long Oracle APEX will continue to be a no-cost feature of the Oracle Database (could be an unanswerable question, I realize)."Great question!  And my response to Tom:

  1. Oracle Application Express has been an included feature of the Oracle Database since 2004.  It remains an included feature of the Oracle Database in 12cR2 and in the foreseeable future.
  2. There are no plans to charge separately for Oracle Application Express.  It has *never* been discussed.
  3. Application Express is a feature of every Oracle Database Cloud Service (Database Schema Cloud Service, Database Cloud Service, Exadata Express, Exadata Service)
  4. In my 21 year career at Oracle, I have never seen a situation where Oracle has included something as part of the database license and then decided later to charge separately for it.  It might have happened, but I'm not aware of it.  I have seen the reverse (where we used to charge a separate license fee, and then eventually included it with the Oracle Database - Oracle Text is a good example).

People often classify APEX as "free".  I call it "an included feature of the Oracle Database." If you're licensed and supported for the Oracle Database, you're licensed and supported for Oracle Application Express.  APEX runs wherever the Oracle Database runs - on premises, your cloud, Oracle Cloud.  We have customers running APEX on a laptop on the free version of the Oracle Database Express Edition (XE), we have customers running APEX on 28-node Oracle RAC clusters, and everything in-between.

If you're interested in learning more about APEX, see A Simple Guide to Oracle Application Express (APEX).

GoldenGate Extract RBA Not Moving LAG Increasing Appears Hung

Michael Dinh - Thu, 2017-06-08 05:55

OGG Extract RBA Not Moving And LAG Increasing And Appears Hung (Doc ID 964705.1)

Typically, when Goldengate is performing recovery:

In recovery[1] – Extract is recovering to its checkpoint in the transaction log.
In recovery[2] – Extract is recovering from its checkpoint to the end of the trail.
Recovery complete – The recovery is finished, and normal processing will resume.

Current status: In recovery[1]: Reading from data source


First time I have seen Processing data with empty data queue and is essentially the same process.

send e* status  
Current status: In recovery[1]: Processing data with empty data queue

send e* status
Current status: Recovery complete: Processing data with empty data queue    

The scary part is current redo vs Goldengate Checkpoint.

To prevent this, make sure there is no lag, no long running transactions before stopping extract.

select thread#,sequence# from v$log where status='CURRENT' order by 1;

---------- ----------
        1     1198566
        2     1291021
info e*
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:44:53  Thread 1, Seqno 1198492, RBA 112396
Log Read Checkpoint  Oracle Redo Logs
                     2017-06-07 14:45:33  Thread 2, Seqno 1290939, RBA 1060244424

What if integrated extract is used? Where’s my Seqno?

info e*
Log Read Checkpoint Oracle Integrated Redo Logs
2017-02-12 18:36:06
SCN 0.4928929 (4928929)

Read more about at Log sequence# and rba# of integrated extract checkpoint

Connect By with multiple tables - what clauses are needed?

Tom Kyte - Thu, 2017-06-08 04:26
I have a question on something that I always get confused on. Most of the demo's on "Connect By" feature a single table and the "Connect" Clause is simply <code>Connect By Prior emp_id = mgr_id </code> or similar. However I have a situation whe...
Categories: DBA Blogs

ASM Discovery of Larger LUN

Tom Kyte - Thu, 2017-06-08 04:26
Can Oracle ASM use the additional space of a LUN assigned to a disk group where the LUN has been grown at the array level?
Categories: DBA Blogs

Peculiar behavior of REGEXP_REPLACE

Tom Kyte - Thu, 2017-06-08 04:26
Hi Tom, Here's something I observed and couldn't make anything out of it. Considering the below two queries: (To summarize, I took a string of 9 repeating characters, and replacing the nth character (3rd in the example) with another character. ...
Categories: DBA Blogs

SQLNET encryption between an Oracle 32bit client and a 64bit Server

Tom Kyte - Thu, 2017-06-08 04:26
Can an Oracle 32 bit client securely communicate with and Oracle 64 bit server via SQLNET encryption? I cannot find this scenario specifically documented in the 12c documentation. Our DBA is telling us that this will not work. Any help guidance yo...
Categories: DBA Blogs

Fast extract from oracle to TEXT/CSV file

Tom Kyte - Thu, 2017-06-08 04:26
Hi Oracle Gurus! We have encountered strange speed issue and need your assistance... Setting: 1) Oracle 11.2 Exadata machine, 4 nodes, 2) several linux servers which need large amounts of data (10-s of gigabytes) from Oracle extracted to tex...
Categories: DBA Blogs

Connection Pool from App Server and RAM usage in Database Server

Tom Kyte - Thu, 2017-06-08 04:26
Hi Tom, Configuration:- --------------------- 1) Oracle weblogic app server is used 2) The connection pool in side weblogic is set as MIN=1200 INITIAL=1200 MAX=1200 3) 8 managed + 1 admin servers inside weblogic 4) so total number sessions es...
Categories: DBA Blogs

trace files location

Tom Kyte - Thu, 2017-06-08 04:26
Tom right now every time I trace my session Iam having to ask my dba to email my trace file and the tkprof outputfile to me, as I dont have access to the files on the database server (os-unix,a nd my client is on windows 2000) side. is there a...
Categories: DBA Blogs

Webcast: Advanced Architectures for Oracle E-Business Suite

Steven Chan - Thu, 2017-06-08 02:00

Oracle University has a large number of free recorded webcasts for Oracle E-Business Suite.  Here's an excellent webcast for a wide range of architectural options for more-complex deployment requirements:

Elke Phelps, Senior Principal Product Manager shares how to build on the three-tier logical Oracle E-Business Suite architecture to meet your business requirements for high availability, scalability, and performance. Topics include load-balancing and clustering. Understand how to securely deploy Oracle E-Business Suite for internet access with a demilitarized zone (DMZ). Apply various topics covered in the session to assist you with planning a disaster recovery project for your environment. Learn about new tools that automate scale-out and other advanced architectures when deploying E-Business Suite to the Oracle Cloud. This material was presented at Oracle OpenWorld 2016.


Categories: APPS Blogs

Using DataPump on Oracle/Docker environment

Marcelo Ochoa - Wed, 2017-06-07 14:51
Image: oracletechnocampus blogContinuing with my previous post about Doing full hot backups with RMan now is time for Data Pump.
The example is how to backup a full schema from a RDBMS running as Docker container, let see, a DB started using:
$ docker run --name test \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_PWD=Oracle2017\! \
-v /home/data/db/test:/opt/oracle/oradata \
Once the DB is ready to use and assuming that there is an SCOTT schema with one table for testing the steps for doing a full schema backup using DataPump are:

  • Create a RDBMS directory object to allows backups on the container external directory
$ docker exec -ti test sqlplus system/Oracle2017\!@pdb1
SQL> !mkdir -p /opt/oracle/oradata/backup
SQL> create directory bdir as '/opt/oracle/oradata/backup';
SQL> exit

  • Start DataPump doing a full schema backup:
$ docker exec test expdp system/Oracle2017\!@pdb1 dumpfile=scott.dmp directory=bdir logfile=scott.log schemas=SCOTT
Total estimation using BLOCKS method: 13 MB
. . exported "SCOTT"."TEST_SOURCE_BIG"                   10.92 MB  135795 rows
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 7 19:13:18 2017 elapsed 0 00:01:03
ready, a full schema backup will be outside your container at /home/data/db/test/backup.

-rw-r----- 1 oracle oinstall  11743232 jun  7 16:13 scott.dmp
-rw-r--r-- 1 oracle oinstall      1983 jun  7 16:13 scott.log

Announcing My Employer-Related Twitter Account

Kevin Closson - Wed, 2017-06-07 14:47

When I tweet anything about Amazon Web Services it will be on the following twitter handle:  https://twitter.com/ClossonAtWork (@ClossonAtWork).

If you’re interested in following my opinions on that twitter feed, please click and follow. Thanks.

Filed under: oracle

Version Control for PL/SQL

Gerger Consulting - Wed, 2017-06-07 14:23

We are hosting a live webinar for IOUG on June 8th at 10:00am PST. Attend and learn how you can manage your PL/SQL source code with Git. We have a new cool demo to show off. :-)

Sign up at this link.

The sign up page as 12 mandatory questions. :-) We are sorry for the inconvenience. We had no control over it. But please bear with us. Bite the bullet. Take a leap of faith. It'll be worth your time. We promise. :-)

Categories: Development

Unable to drop tables from a database

Tom Kyte - Wed, 2017-06-07 10:06
Hi We have recently created a new database from an existing database via RMAN incremental backup level 1. We have created a user and provided all the privileges to it. But, we are currently facing an issue, we are unable to drop any tables...
Categories: DBA Blogs

about directory location of oracle database files,has it haven one parameter?

Tom Kyte - Wed, 2017-06-07 10:06
SYS@orcl28> col banner for a90 SYS@orcl28> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release - 64bit Production PL/SQL Rel...
Categories: DBA Blogs

Regarding trigger

Tom Kyte - Wed, 2017-06-07 10:06
Hi, my Question is 1) i have a statement level trigger and row level trigger on the same table but i want to update some date on the same table i finished my update then i select to see the data which has updated but output is no rows select....
Categories: DBA Blogs

direct connect Database links in the exadata environment

Tom Kyte - Wed, 2017-06-07 10:06
Can Dblinks be utilized in the Exadata environment. If not, how are direct connects to external databases done?
Categories: DBA Blogs

Convert loop to bulk collect forall

Tom Kyte - Wed, 2017-06-07 10:06
Here is my code: <code>procedure load_cmpnt_history is cursor c1 is select --+rule h.ndc_emp_id , nvl(upper(h.cmpnt_id),'FAA00000') cmpnt_id , h.compl_dt , nvl(nvl(h.crs_grade,h.cmpl_stat),'OTHER...
Categories: DBA Blogs

Partition Maintenance Induced Concurrency

Tom Kyte - Wed, 2017-06-07 10:06
Hi TOMs, A little back story: Our application incurs many "cursor: pin s wait on x" and "library cache lock" waits during our nightly partition maintenance job. It has been expected behavior and one that we've been living happily with for quit...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator