DBA Blogs

Finding data which exist in one set but not the other.

Tom Kyte - Wed, 2017-08-30 10:06
In this example we find data that exists in one set but not the other. but why did I have to use CTE? (--x denote records expected in results) <code>With test1 (A,b,c) as ( SELECT 2001, 'abc', 'john' from dual union all-- SELECT 2008, 'ca...
Categories: DBA Blogs

oracle 12c Sharding

Tom Kyte - Wed, 2017-08-30 10:06
Hi, I need oracle 12c sharding detail. deployment steps . My target is to using 12c sharding keep each partitioned data into seperate shard-node. ( like oracle range partition where each rang/partitioned can b stored in separate tablespace...
Categories: DBA Blogs

Loading more than 45 columns into a table using wizard - Oracle apex 5.0.4

Tom Kyte - Wed, 2017-08-30 10:06
I was wondering if there's any workaround for uploading data into the tables using the apex wizard when there are more than 45 columns. I am using Apex 5.0.4, Oracle 12 C database. I was going through the documentation where it lists that number of c...
Categories: DBA Blogs

NVL - invalid number

Tom Kyte - Wed, 2017-08-30 10:06
Hi Tom, Can you explain why NVL has to process expr2 when expr1 is not null? I see the error is not occuring when used in WHERE condition. Error:- UPDATE mytable SET number_col = nvl(1234,'dummy') where number_col is null or select...
Categories: DBA Blogs

Show error about 'ORA-04043: object dba_data_files does not exist' on my Oracle DataGuard 11.2.0.1.0 physical standby when query 'desc dba_data_files' ??

Tom Kyte - Wed, 2017-08-30 10:06
Hi, When i query description of table 'dba_data_files' and 'dba_temp_files' with SQL command 'desc dba_data_files' and 'desc dba_temp_files' on Oracle DataGuard 11.2.0.1.0 physical standby,there has an error,content as follows, <code> SYS@standby>...
Categories: DBA Blogs

Perfomance Tuning

Tom Kyte - Wed, 2017-08-30 10:06
Hi Team/Steven. We have one table"A" with 100000 records based on "A" Table we have sequence id we are using that sequence id and fetching two more tables like insert into gtt_table select * from b join c where B.id=c.id and b.seq_id=rec.seq_id; ...
Categories: DBA Blogs

Oracle Big Data Cloud services for all - Insights built to last

Big data remain a topic everyone seems to be talking about it, but still many wonder "what is big data really?" How is it changing the way researchers at companies, non-profits,...

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

Presenting at UKOUG Tech17 Conference in December (Here Come The Warm Jets)

Richard Foote - Tue, 2017-08-29 19:39
I presented at the UKOUG Tech14 conference in Liverpool and thoroughly enjoyed the experience. It was one of the better User Group conferences I’ve attended and winning both the Best New Oracle Speaker and Best Oracle Speaker awards made that bit more special. So it’s with real excitement that I have the opportunity to again […]
Categories: DBA Blogs

How to get the Number of Day between two dates, subject to some conditions

Tom Kyte - Tue, 2017-08-29 15:46
Team, Please find below the Create Table stmt <code> CREATE TABLE ST_TA_STOP_INTEREST(LOAN_CODE NUMBER, TRANSACTION_DATE DATE,EVENT_ID VARCHAR2(5)); </code> Please find below the insert stmts for the above created table: <code> SET DEFIN...
Categories: DBA Blogs

Cant we take a backup of datafile using expdp y ??

Tom Kyte - Tue, 2017-08-29 15:46
hi, can not we take a backup of datafile using expdp and impdp ?? can we take a backup of schema in rman ??
Categories: DBA Blogs

Pro*C - DML on double variable

Tom Kyte - Tue, 2017-08-29 15:46
Hi Tom, While inserting a double variable into a database columns it gets round off.The Database column is number(12,2). when i tried inserting 2146327346.47,it was inserted as 2146327346.47. Similarly 2152186221.53 was insert as 2152186222.00 and...
Categories: DBA Blogs

How to create constraints and indexes for very large table/Running out of TEMP space

Tom Kyte - Tue, 2017-08-29 15:46
I am rebuilding a database on a new server and I have reloaded the tables via datapump exp/imp. I have one particular table that has a range partition by year and has roughly 20+ billion rows of data. When I try to add the constraints and indexes I...
Categories: DBA Blogs

Finding the correct value based on a date range.

Tom Kyte - Tue, 2017-08-29 15:46
I have 3 tables one with payroll information (payroll), one of pay periods (erpaydates) and one of retirement contribution percents (planeecontrpct). I am trying to find a way to select what the employee's contribution percent (planeecontrpct.contrpc...
Categories: DBA Blogs

Partner Webcast – Oracle IoT Cloud Service: Getting Started

Eight zetabytes of data are coming from tens of millions of devices. Where are you going to put the data, and what are you going to do with it? Internet of things (IoT) is all about integrating and...

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

Process Cloud Service - Using correlations to communicate between processes (part 1)

There are several possible ways to communicate between processes like, request and response, fire and forget, calling sub-processes using call activities. All of those  creates a new process or...

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

Confiqured file watcher isn't started by sy.file_watcher

Tom Kyte - Mon, 2017-08-28 21:26
I created a file watcher job 'fw_job', but it seems the file watcher doesn't start it. "JServer JAVA Virtual Machine" is VALID I granted "CREATE CREDENTIAL" and "CREATE ANY JOB" to my user SYS.FILE_WATCHER FREQUENCY is set to 'FREQ=MINUTELY;IN...
Categories: DBA Blogs

Dynamic SQL with sys_refcursor in Oracle Stored Procedure

Tom Kyte - Mon, 2017-08-28 21:26
Hello Experts, We have created below procedure and need to get result sets for that. Please find below the stored procedure, <b>Stored Procedure:</b> Create or replace procedure test_fetch_details (p_emp_id in employee.emp_id%type, p_emp_cur...
Categories: DBA Blogs

Backup/restore onto other hardware

Tom Kyte - Mon, 2017-08-28 21:26
Hi, I want to make a simple restore / recovery on a new identical oracle (same os(windows), os version and oracle version) (DRP, oracle 11 or 12). DB is in archivelog mode. I have a rman backup in e:\backup (controlfile autobackup is on), nocatal...
Categories: DBA Blogs

Parsing XML with PLSQL

Tom Kyte - Mon, 2017-08-28 21:26
Could you please provide a PLSQL code to parse XML data?
Categories: DBA Blogs

JSON object access using JSON_TABLE

Tom Kyte - Mon, 2017-08-28 21:26
Hi I have a JSON object containing complex MVA, and would like to have relational access to my JSON object using JSON_TABLE + View. The way I am doing it right now, gives me 4 rows when I query the View, and I would like the SQL to return two r...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs