DBA Blogs

Materialized View Fast Refresh from heterogeneous database via oracle gateway

Tom Kyte - Tue, 2017-10-24 10:06
Ora Masters, We pull a huge volume of data from source database (MySQL) to Oracle database (11.2.0.3) using Oracle gateway (12.2.0) through a remote database link. But the issue is that we receive following error when the MV is tried to refresh ...
Categories: DBA Blogs

How to configure Apex

Tom Kyte - Tue, 2017-10-24 10:06
Hi. We have Oracle database 12c installed and I can see below Apex schemas as well. APEX_030200 APEX_040200 APEX_PUBLIC_USER FLOWS_FILES Below query also returns 'VALID' value. SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX'; Can ...
Categories: DBA Blogs

Export time doubled after adding encryption

Tom Kyte - Tue, 2017-10-24 10:06
Recently, we added encryption directives to database exports. We noticed that the export time doubled. In your experience, is this expected behavior? Database1: Before 54 mins After 1 hr 40 mins Database2: Before 30 mins After 58 mins ...
Categories: DBA Blogs

Disk I/O monitoring sql query

Tom Kyte - Tue, 2017-10-24 10:06
Dear Tom, My team is trying to write a query for measuring Disk I/O latency in milli seconds. my team is looking to query v$system_event, but this view has accumulated values of metrics from the startup time of the instance. Please advise which ...
Categories: DBA Blogs

No Exection Generated in 10046 Event Trace

Tom Kyte - Tue, 2017-10-24 10:06
Hi Tom, I'm using the event 10046 to trace other sessions. But sometimes I find that there is not execution plan information included in the result trace file. Here is an example: 1. From my session, I executed dchen@SAPU> exec sys.dbms_syst...
Categories: DBA Blogs

Re-ordering selective rows in a alpha column

Tom Kyte - Mon, 2017-10-23 15:46
Hi Masters, The example below makes an attempt to swap the A before B when they appear together whilst ignoring the nulls in colC as well as still following the original order from colA but comes unstuck with id = 3333 and 4444 where for a few exa...
Categories: DBA Blogs

Materialized View or dblink - which one is better and why

Tom Kyte - Mon, 2017-10-23 15:46
We have an ETL process that has to get data from remote db (Oracle 12c). For some reasons, we cannot give direct access on the remote tables to the ETL process. We are considering two options: Option 1. On local db, create dblink to remote db, cre...
Categories: DBA Blogs

UNUSABLE index after Partition maintenance operations

Tom Kyte - Mon, 2017-10-23 15:46
Team, Was reading through this documentation. <u>http://docs.oracle.com/database/122/SQLRF/CREATE-INDEX.htm#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__BABCHJDH</u> <quote> When an index, or some partitions or subpartitions of an index, are cr...
Categories: DBA Blogs

Insert a text file into oracle table , the file is in oracle directory

Tom Kyte - Mon, 2017-10-23 15:46
I want to insert a text file into a table . But the file is in oracle directory something like /tmp/test.txt , Is it possible , a procedure with a parameter which will take the file path as parameter and fetch the file from server and copy it to th...
Categories: DBA Blogs

Update same table in parallel using batch number

Tom Kyte - Mon, 2017-10-23 15:46
Hi, I have this statement, create table TWO nologging parallel 15 as select a, b, c, pkg_xyz(l,m,n) d from ONE; As you can see, there is a package call in the select query. There are 7 million rows in table ONE. It is taking approx 3....
Categories: DBA Blogs

Tablespace in EM Express

Tom Kyte - Mon, 2017-10-23 15:46
Hi Tom, I need to ask you for help, I am new to Oracle and trying to understand some aspects of db administration. I have one of the exercises to create tablespace using EM Express, however I should navigate to storage-> tablespace but under the s...
Categories: DBA Blogs

how to query and monitor the memory and CPU usage of the database

Tom Kyte - Mon, 2017-10-23 15:46
I'm maintaining a oracle db which version is 10.2.0.1, a lot of time, i just know how to query and monitor the memory and CPU usage on OS level, now I want to query and monitor the memory and CPU usage of the database on db level , could you pleas...
Categories: DBA Blogs

performance tunning

Tom Kyte - Mon, 2017-10-23 15:46
Hi Team, Currently we have seen huge spike for our database (non-RAC ) . This spike was due to log file sync that was around 900 session , blocker of this sessions was log file parallel write . whereas their was no blocker for log file paralle...
Categories: DBA Blogs

Deinstall Enterprise Manager System (12c) for a fresh install.

Tom Kyte - Mon, 2017-10-23 15:46
Hi, I have installed the simple configuration (oem 12c) and want to deinstall it so I can freshly install for the "small" configuration (under Advanced configuration in the GUI). Note: somebody made a mistake to install the "simple configuration...
Categories: DBA Blogs

Partner Webcast – Code Your Infrastructure using Oracle Stack Manager in Oracle Cloud

Oracle's end-to-end DevOps solutions are not only designed and leveraged for cloud native application development but also for allowing deployment of standards-based, enterprise-grade...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Procedure that deletes an input from table with foreign key constraints

Tom Kyte - Sun, 2017-10-22 21:26
I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS. <code>CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER (CUSTOMER_ID NUMBER) AS TOT_CUSTOMERS ...
Categories: DBA Blogs

Parameterized dynamic query

Tom Kyte - Sun, 2017-10-22 21:26
Hi, I'm trying to improve the performances of some queries in my production environment. The current scenario is: 1) retrieve some data from "<b>ParentTable</b>". The query, in software code, is composed dynamically, based on the specified s...
Categories: DBA Blogs

SQLcl works worse with pipelined function than SQL*Plus

Tom Kyte - Sun, 2017-10-22 21:26
Let's imagine, we have a pipelined function like shown in this example https://oracle-base.com/articles/misc/pipelined-table-functions#pipelined_table_functions My aim is to show, wether that function really return data row by row, rather than at o...
Categories: DBA Blogs

OGG-00868/ORA-01291 – Missing log files with Integrated Extract

DBASolved - Sun, 2017-10-22 10:05

Oracle GoldenGate is reliant on the Oracle Redo Logs and Archive Logs when capturing transactions. Since I do a lot of testing on VMs (limited space) and in the cloud (limited space … don’t want to burn to much $), I often delete my archive logs. Normally this is not a problem; however, every once-in-awhile I delete more archivelogs than I should. This throws the IE into a state where it will not start because of OGG-00868/ORA-01291 – Missing Log Files.

On some level this is to be expected, but when you are using IE you have to remember that the extract is registered with the database. Since I’m using Integrated Extract, we have to reset how the extract is registered with the database. The below steps will show you how this should be done:

Note: Registering/Unregistering process have to be done at the container database (CDB) level.

adminclient> dblogin useridalias <alias> domain <domain>
adminclient> stop extract <extract_name>
adminclient> unregister extract <extract_name> database
adminclient> register extract <extract_name> database container <container_name>
adminclient> start extract <extract_name>
adminclient> info extract <extract_name>

Once the extract is re-registered with the database; the integrated extract will start.

Enjoy!!!


Filed under: Golden Gate
Categories: DBA Blogs

Fragmentation

Tom Kyte - Sat, 2017-10-21 08:46
Can tables which only experience inserts and updates and not delete be fragmented.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs