DBA Blogs

How to calculate free space in the Database

Tom Kyte - Wed, 2017-11-01 19:46
How to calculate the actual database size and the free space in the database. I tried below queries <code>select round(sum(bytes)/1024/1024/1024) size_in_gb from dba_data_files; = 1073 GB select round(sum(bytes)/1024/1024/1024) size_in_gb from dba...
Categories: DBA Blogs

Oracle Cloud Application Development Platform 17.4.2 Anncounced

We are excited to announce the new enhancements to Oracle Application Container Cloud and Oracle Java Cloud Service that were rolled out as part of the 17.4.2 release. New Language runtime Support...

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

Retrieve fields from clob field with envelope and HTML chacaters

Tom Kyte - Wed, 2017-11-01 01:26
Dear I have tried to parse a clob field located in a Oracle table that contains a envelope and also some HTML characters (&quot, &lt, &gt), as shown below: <?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xm...
Categories: DBA Blogs

Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g

Tom Kyte - Wed, 2017-11-01 01:26
We are noticing that large table inserts with CLOB is taking more time on 12c than 11g. The inserts are during an online transaction processing where we copy data from one business unit to another. There is one table in particular that is taking cons...
Categories: DBA Blogs

Get "LPX-00242: invalid use of ampersand" from xmltype() even though ampersand is already escaped

Tom Kyte - Wed, 2017-11-01 01:26
Any idea why I get the error message "LPX-00242: invalid use of ampersand ('&') character (use &amp;)" even though the ampersand in the below test case is already escaped? Tested with same result both in 11g XE, 11.2.0.4 SE, and on apex.oracle.com (w...
Categories: DBA Blogs

Implementing Old style SQL outer join along with condition query to the new style

Tom Kyte - Tue, 2017-10-31 07:06
Hi, I have SQL outer join query which is written in old format with (+) notation and I am working on its migration to the new presentation and without the (+) notation. I am unable to convert the last line of the query with the outer join condition....
Categories: DBA Blogs

Need to print latin ä charater through Dbms out into apex sql developer ?

Tom Kyte - Tue, 2017-10-31 07:06
Hi gurus, I am trying to print this character a through apex sql developer but it return me ? instead of this a , any one know how can I achieve this. begin dbms_output.put_line ('http://fam.co/') ; end; if my question is not clear please...
Categories: DBA Blogs

two stage RMAN backups

Tom Kyte - Tue, 2017-10-31 07:06
Hi team. We have an Oracle instance, which I think is using ASM on SAN LUNs. The SAN is multi-path, multi-fabric with the LUNs are provisioned from a high quality H/A storage array. At the moment the DBAs use RMAN to push backups via MML directly ...
Categories: DBA Blogs

Make a simple audit of table connections

Tom Kyte - Mon, 2017-10-30 12:46
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...
Categories: DBA Blogs

Data Model (Design) Question

Tom Kyte - Mon, 2017-10-30 12:46
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...
Categories: DBA Blogs

ASSM and basicfile LOB

Tom Kyte - Mon, 2017-10-30 12:46
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...
Categories: DBA Blogs

Install Apex on Database Vault

Tom Kyte - Mon, 2017-10-30 12:46
Dear Tom, I am trying to install oracle apex 5.1.3 on oracle database 12c 12.1 release which is vault enabled. I was complete my apex install all are done successfully. but when i call on browser a messages shown is (Connection was reset). ...
Categories: DBA Blogs

Does update query returns affected row(s) value in key holder ?

Tom Kyte - Mon, 2017-10-30 12:46
Hi TOM, Does Oracle returns affected rows for update SQL statement in key holder? For insert query we supposed to receive the auto generated key. Such a way update query returns the affected rows id ?
Categories: DBA Blogs

Partner Webcast – IoT in Action on Oracle Cloud Platform

Oracle Internet of Things (IoT) Cloud Service is a managed Platform as a Service (PaaS) cloud-based offering that helps you make critical business decisions and strategies by allowing you to connect...

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

Oracle 12c - query behavior change (11g not thrown error while "group by" not mention in sub-query/in-line)

Tom Kyte - Sun, 2017-10-29 00:06
Hi Team, We are migrating from 11g to 12c. We observed one behavioral change, as "group by" not mention in in-line query but 11g not thrown error. Query characteristics ---------------------------------- 1. Main query contains a inline...
Categories: DBA Blogs

ORA-06565: SET ROLE in LOGON TRIGGER

Tom Kyte - Sun, 2017-10-29 00:06
We have a trigger CREATE OR REPLACE TRIGGER TRG_AFTER_LOGON AFTER LOGON ON DATABASE BEGIN LogonCheck; END; / with CREATE OR REPLACE PROCEDURE LogonCheck AUTHID current_user AS BEGIN EXECUTE IMMEDIATE 'SET ROLE NONE'; END; / T...
Categories: DBA Blogs

Local unique indexes on partitioned tables

Tom Kyte - Sun, 2017-10-29 00:06
Tom, I'm wondering why Oracle requires the partitioning key on a local unique index. I realize that internally Oracle doesn't want to have to scan all local index segments to check the constraint. But why should the partitioning key need to be o...
Categories: DBA Blogs

12c release 1 dump questions confusions

Tom Kyte - Sun, 2017-10-29 00:06
Greetings, Every time i submit a question, i get a sense of confidence that i will surely get a valid and updated answer from the world's most interactive and supportive forum. The group is a ORACLE DICTIONARY for me. I thank you from bottom of...
Categories: DBA Blogs

Counting the number of business days between 2 dates.

Tom Kyte - Sat, 2017-10-28 05:46
how to get the number of days between two dates excluding saturdays and sundays using pl/sql?
Categories: DBA Blogs

PARALLEL Hint not getting used in SELECT

Tom Kyte - Sat, 2017-10-28 05:46
Hi, I have a SQL written in stored procedure: SELECT /*+ PARALLEL(TB_TEST, 8) +/ FROM TB_TEST WHERE DT <= TO_DATE('01/01/2015', 'MM/DD/YYYY'); When i execute the procedure and monitor v$session, I could see only one session with having SQL_I...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs