DBA Blogs

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...

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 […]

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 ...

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...

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...

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; ...

Hi, Just wanted to check if there will there a recording of this available ?

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...

What benefits can be gained from changing from 2k blocks to 4k blocks or even 8k blocks

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...

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...

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.

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...

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.

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...

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 ...

<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>...

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-...

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...

Hi Tom I have a question regarding DBMS_SQLTUNE_UTIL0 package. This seems to be helper package. Is it okay to use this package directly in code or use of such internal packages should be avoided? I did not find any documentation on this packa...