DBA Blogs

Spreadsheet Upload

Tom Kyte - Fri, 2018-08-24 17:26
Hi there, If there is a way to upload the spreadsheet data in our existing application? If possible please send your answers. Regards, Aravindan Prem
Categories: DBA Blogs

JET Line Chart - Step Handling

Tom Kyte - Fri, 2018-08-24 17:26
I have a problem generating the vertical lines in a line chart. E.g. take this query: with nums as ( select rownum as rnum from dual connect by rownum < 300) select rnum/9 as x, sin(2*rnum/30) as y from nums In the X-Axis, my tick mar...
Categories: DBA Blogs

New Zealand: “Oracle Indexing Internals and Best Practices” Seminars November 2018.

Richard Foote - Thu, 2018-08-23 17:37
Good news for those of you in beautiful New Zealand. Due to popular demand, I’ll be returning to run some of my acclaimed “Oracle Indexing Internals and Best Practices” seminars in November 2018. The dates and events are: Wellington: 19-20 November 2018: Registration Here or Buy Directly Here Auckland: 21-22 November 2018: Registration Here or […]
Categories: DBA Blogs

Doing joins on denormalized data in tables

Tom Kyte - Wed, 2018-08-22 10:26
Hi Team RDBMS version: 12.2 OEL: 7.5 Trying to evaluate new table structure to query data, which I've heard is very widely used these days in e-commerce companies at scale. Though most of those companies might be using NoSQL solutions for the ...
Categories: DBA Blogs

Get difference between 2 date columns in HH:MM:SS

Tom Kyte - Wed, 2018-08-22 10:26
I am trying to get difference between 2 date columns in HH:MM:SS. Currently I have the below query which is returning the difference in HH:MM:SS. The problem with the below query is even if there is a difference of 1 day and 13 hours, it is just givi...
Categories: DBA Blogs

Documentation claims that multiset conditions also work with varrays, which they don't

Tom Kyte - Wed, 2018-08-22 10:26
According to the documentation, a lot of multiset conditions should also work with varrays, which they don't (at least not in the SQL language): https://docs.oracle.com/database/121/SQLRF/conditions006.htm#SQLRF52138 I've created a Stack Overflow...
Categories: DBA Blogs

QUERY CLAUSE for 2 join tables

Tom Kyte - Wed, 2018-08-22 10:26
Hi Tom, I have 2 SQL scripts giving lots of rows :- ---Export Data for fy17 filename: w_camp_hist_f_2016.dmp select count(*) from olap.w_camp_hist_f,olap.w_day_d where w_camp_hist_f.ld_dt_wid=w_day_d.row_wid and w_day_d.petc_year = 2016; ...
Categories: DBA Blogs

Question from Office Hours on Data Visualization with Oracle APEX

Tom Kyte - Wed, 2018-08-22 10:26
Hi, Just wanted to check if there will there a recording of this available ?
Categories: DBA Blogs

Duplicate columns in Oracle query using row limiting clause

Tom Kyte - Tue, 2018-08-21 16:06
Since Oracle 12c, we can finally use the SQL standard row limiting clause like this: <code>SELECT * FROM t FETCH FIRST 10 ROWS ONLY</code> Now, in Oracle 12.1, there was a limitation that is quite annoying when joining tables. It's not possible...
Categories: DBA Blogs

Blocks - 2 or 4 or 8k?

Tom Kyte - Tue, 2018-08-21 16:06
What benefits can be gained from changing from 2k blocks to 4k blocks or even 8k blocks
Categories: DBA Blogs

How to get session id (without using V$ tables )

Tom Kyte - Tue, 2018-08-21 16:06
How to get session identifier (without using V$ tables). -- Normally DBA deny access to V$tables to developers. To get the os_user if we query the following we will get os_user. SELECT SYS_CONTEXT ('USERENV', 'OS_USER') F...
Categories: DBA Blogs

PUBLIC SYNONYM is not compiling using execute immediate

Tom Kyte - Tue, 2018-08-21 16:06
Hi There, I am having a problem trying to compile public synonyms using a block. The block runs fine - however, it doesn't compile the public synonym. When I run the query select * from all_objects where status = 'INVALID', the public synonyms are...
Categories: DBA Blogs

data guard drift

Tom Kyte - Mon, 2018-08-20 03:26
I have setup DR with maximum performance mode, at any time of instance, how to find the duration of time of data difference between DC and DR.
Categories: DBA Blogs

DATABASE STORAGE USAGE IN ASM

Tom Kyte - Mon, 2018-08-20 03:26
Hey Guys, I am working in a RAC environment and have been struggling to get a perfect query to view ASM Storage usage based on specific and each databases on the ASM. <b>Simply, i want a result that shows a database and how much size that databas...
Categories: DBA Blogs

Materialized View Add Columns

Tom Kyte - Mon, 2018-08-20 03:26
Hi Tom, I have added a new column in my master table and i want this column to be reflected in my Materialized view is this possible without Re creating it i have to fetch all columns from my master table in my view.please suggest.
Categories: DBA Blogs

Event SQL*Net break/reset to client in refresh of materialized view

Tom Kyte - Mon, 2018-08-20 03:26
Dear, I have had event of "SQL*Net break/reset to client" in session (job) with refresh of materialized view Same after kill it the session, kill the running job, the new session again remains with this event. The solution always is re-create th...
Categories: DBA Blogs

oracle utl_file encoding from utf8; missing character

Tom Kyte - Sat, 2018-08-18 14:46
I like to export large amount of text data from db to file. The characterset in db is UTF8. The excepted result in the file is ISO8859P2 or MSWIN1250. My db settings: <code> SELECT * FROM v$nls_parameters; 1 NLS_LANGUAGE HUNGARIAN 0 ...
Categories: DBA Blogs

How do I determine how much storage will be required for NUMBER(p, s)?

Tom Kyte - Sat, 2018-08-18 14:46
<i>Oracle Database SQL Language Reference</i> states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did: <code>SQL> create table t (a number(10, 0)) 2 / Table created. SQL>...
Categories: DBA Blogs

Exception when executing Bulk Collect

Tom Kyte - Sat, 2018-08-18 14:46
I am using bulk collect with save exceptions, it runs fine sometimes and sometimes it errors out. The error is as follows - <code>BEGIN * ERROR at line 1: ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-...
Categories: DBA Blogs

Performance issue after database upgrade from 11.2.0.4 to 12.1.0.2

Tom Kyte - Sat, 2018-08-18 14:46
Hi Team, We have recently upgraded a SAP database from 11.2.0.4 to 12.1.0.2, After encountered performance issues that did not exist on the previous version(11.2.0.4). Proper keeping sga_target/sga_maxsize did not help Proper keeping db_file_m...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs