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

DBMS_SQLTUNE_UTIL0

Tom Kyte - Sat, 2018-08-18 14:46
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...
Categories: DBA Blogs

Space Fragmentation in a table which has only inserts

Tom Kyte - Sat, 2018-08-18 14:46
Hi Tom, This is Praveen. 1)I have table x which has only inserts in it and updates rarely,so my doubt is till now i have heard and read that there will be space fragmentation for delete operation done on a table and i couldn't find out how the ...
Categories: DBA Blogs

definition of v$lock table

Tom Kyte - Sat, 2018-08-18 14:46
please find below query output.. select OWNER,OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME in ('V$LOCK'); ---PUBLIC V$LOCK SYNONYM as per above query output V$LOCK is SYNONYM. so i checked in DBA_SYNONYMS for further informa...
Categories: DBA Blogs

Rownum = 1 no longer returning results after upgrade to 12.1

Tom Kyte - Sat, 2018-08-18 14:46
Hi, I have a view that I created in 11g, after we upgraded the database to 12.1 it is no longer returning the dates for the view. I pulled the SQL for the view and confirmed that it no longer works. It does return the comment. I need to use the r...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs