DBA Blogs

Date in comparison with string

Tom Kyte - Fri, 2018-11-09 18:06
Hi Tom, I have a table named RTBS_SUBS_EVENT and there is a date column named expiry_date. expiry_date is a partitioned column too. a column named ID and it's indexed. NLS_DATE_FORMAT of the database is 'DD-MON-RR'. I run this query and th...
Categories: DBA Blogs

Join elimination

Tom Kyte - Fri, 2018-11-09 18:06
Hello All, While making some tests using LiveSQL, which is Oracle 18c, I found a strange behavior regarding join elimination. That is, join elimination DOES happen even if the constraints are DEFERRED ! My LiveSQL test script can be found he...
Categories: DBA Blogs

Is there any point backing up flashback 'flb' files?

Tom Kyte - Fri, 2018-11-09 18:06
Hi, Is there any point/benefit in backing up flb files to tape using an OS utility? Or, to put it another way: would backing up flb files just be a waste of tape? Thanks
Categories: DBA Blogs

Sorting -- sort a string like a number.

Tom Kyte - Thu, 2018-11-08 23:46
I have a alphanumeric column in the database. It contains only numeric values.Is it possible to do a numeric sort directly by a sql command like "order by <column>"
Categories: DBA Blogs

Blocks read using index vs. full table scan

Tom Kyte - Thu, 2018-11-08 23:46
Hi Tom, I have a 3 part question about performance... 1) The first question is sort of a introductory summary question. 2) Then I have an example ending with a question. 3) Finaly I have a very specific 'important' question. Why is it some...
Categories: DBA Blogs

Do we know for sure from the trace file, that the application uses transactions on the database?

Tom Kyte - Thu, 2018-11-08 05:26
We got deadlock. In the trace file there is the deadlock graph: <code> Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds wai...
Categories: DBA Blogs

Using REGEXP to find ten consecutive digits with possible separators

Tom Kyte - Thu, 2018-11-08 05:26
I HAVE THIS FREE FLOWING DATA FIELD IN A VARCHAR2 COLUMN THAT I AM CLEANING UP. I WOULD LIKE TO GET THOSE VALUES THAT HAVE A TOTAL OF 10 DIGITS IN ALL AND MIGHT CONTAIN THESE CHARACTERS : . OR - OR / OR ( ) CREATE TABLE E ( PHONE VARCHAR2(5...
Categories: DBA Blogs

Oracle Sequence vs GUID

Tom Kyte - Thu, 2018-11-08 05:26
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...
Categories: DBA Blogs

Graceful Stop of Goldengate Extract

Pakistan's First Oracle Blog - Wed, 2018-11-07 18:58
It's always a good idea to stop extracts after checking if there is any long running transaction in the database being captured. Failing to do so might later result in hung or unstable processes.





Use following command to check the extract:

GGSCI (test) 3> send e* status

Sending STATUS request to EXTRACT ext ...


EXTRACT ext (PID 16649)
Current status: In recovery[1]: Processing data with empty data queue

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

testsrv:/u05/ggate> grep -i bound dirrpt/ext.rpt
Bounded Recovery Parameter:
2017-06-07 15:48:18 INFO OGG-01639 BOUNDED RECOVERY: ACTIVE: for object pool 2: p2628_Redo_Thread_2.
2017-06-07 15:48:18 INFO OGG-01640 BOUNDED RECOVERY: recovery start XID: 129.15.2953694.

-- And then Check RBA is moving with following commands
./ggsci
info e*
lag e*

Categories: DBA Blogs

Goldengate ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

Pakistan's First Oracle Blog - Wed, 2018-11-07 18:37
One extract got abended and wasn't able to start in Oracle Goldengate Version 12.2.0.1.160517 23194417_FBO. The redologs were present but extract was still abended and threw following error in the report file.

Error:

ERROR OGG-02037 Failed to retrieve the name of a missing Oracle redo log.

Solution:




The solution for this error is to unregister, register and then start the extract as follows:


GGSCI (test) 6> unregister extract ext database 2018-11-07 17:07:03 INFO OGG-01750 Successfully unregistered EXTRACT ext from database. GGSCI (test) 7> GGSCI (test) 7> register extract ext database 2018-11-07 17:07:56 INFO OGG-02003 Extract ESTATDEV successfully registered with database at SCN 1373637632014. GGSCI (test) 8> start extract ext

Hope this helps.
Categories: DBA Blogs

Row visibility set in a package procedure EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE'

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, I've got a table DAVE_ELOF set to row archival (alter table dave_elof row archival;). I want that a user can set row visibility via a package procedure setStudyVisibleYN(aStudyId number, aVisible_YN varchar2). Code snipp...
Categories: DBA Blogs

Parallelism in IOT's

Tom Kyte - Wed, 2018-11-07 11:06
I am trying to join (outer join) a number of IOT's all with the same key in Parallel Mode; and am expecting the optimizer to use a Merge Join in order to avoid sorts (as the documentation clearly says it should); but it does not. - I'm tracing the pr...
Categories: DBA Blogs

history of session for particular sql id

Tom Kyte - Wed, 2018-11-07 11:06
I have a query which takes times to execute. When a user connects to the system and I monitor database I also get the SQL statement which takes times. but when user complete his task and after days he talks to me report takes too much time. I analyz...
Categories: DBA Blogs

How to determine the faster of two queries based on the explain plan

Tom Kyte - Wed, 2018-11-07 11:06
Hello I have two SQL scripts that are producing the exact same result (returning approx. 30000 rows) but the first one does it for 30min. and the second one for 2min. The slower query has (by my understatnding) the better explain plan (because it ...
Categories: DBA Blogs

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

Tom Kyte - Wed, 2018-11-07 11:06
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...
Categories: DBA Blogs

Flashback data archive for auditing

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, Greetings !! we would like to leverage the 18c Flashback Data archive feature for auditing in our environment. Currently its trigger based with some business logic implemented in it and only 30% of tables being audited and capturing all...
Categories: DBA Blogs

Which is better to use Direct SQL or Bulk Collect

Tom Kyte - Wed, 2018-11-07 11:06
I have a source table of almost 20 million records. I have to load another table from source table after some checks and validation. What would be the better way to transfer, Insert through Direct SQL or Use Bulk Collect. what is difference between...
Categories: DBA Blogs

How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Tom Kyte - Wed, 2018-11-07 11:06
Hi Tom, In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope. So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. T...
Categories: DBA Blogs

Who last updated an Oracle object

Tom Kyte - Wed, 2018-11-07 11:06
Hello, My goal is to produce a list of all objects (procedures, packages, functions, triggers, and tables) that have changed in the database since they were first created. I have a query that compares "created" and last_ddl_time" in the vi...
Categories: DBA Blogs

Counter to indicate rate changes

Tom Kyte - Tue, 2018-11-06 16:46
Hi Tom! Glad to have you back. Oracle: 12.1.0.2 My challenge is as follows: The t_res table contains hotel room reservations that consist of a reservation id (res_id), date of stay (res_date), and a rate card (rate_id) which typically dic...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs