DBA Blogs

How to Reclaim Space After NULLing LOBs

Tom Kyte - Thu, 2017-07-27 01:46
On this table that contains BLOBs and up to today filled up a 32GB tablespace, I removed a large percentage of BLOBs by setting them to NULL with an UPDATE statement (leaving the remaining columns untouched of course). No space was actually freed so ...
Categories: DBA Blogs

Function with DML & DDL

Tom Kyte - Thu, 2017-07-27 01:46
Hi, can we write DML and DDL statements inside a function(stored procedures)?
Categories: DBA Blogs

Can i make Output parameters as optional - i dont want to send out parameters while executing stored procedure

Tom Kyte - Thu, 2017-07-27 01:46
Below is my simple dummy procedure <code> create or replace procedure sp_dummy ( p_options NUmber, cursorparam1 OUT sys_refcursor, cursorparam2 OUT sys_refcursor ) AS begin open cursorparam1 for select sys...
Categories: DBA Blogs

Partner Webcast - ODA: The exclusive partner opportunity

Join us to learn more about Oracle Systems, Engineered Systems and Storage. Learn what we understand by “Cloud Insurance” through our Systems Webcast Series for EMEA Partners. The...

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

SQL Developer Database Export Error

Tom Kyte - Wed, 2017-07-26 07:26
Hello. I'm using SQLDeveloper for coping my database from one server to another. So I choose Tools->Database Export..., then connection and couple of options (like this one https://docs.oracle.com/cd/E17781_01/server.112/e18804/impexp.htm#BABHFHGH). ...
Categories: DBA Blogs

GETTING ORA-29270: too many open HTTP requests error

Tom Kyte - Wed, 2017-07-26 07:26
Hi, Please find below procedure from which we are keep on getting "ORA-29270: too many open HTTP requests" error, Procedure: <code>CREATE OR REPLACE PROCEDURE test_task ( p_quote_number IN cct_quote.quote_number%TYPE, p_quote...
Categories: DBA Blogs

Oracle PQ processing in pluggable database

Tom Kyte - Wed, 2017-07-26 07:26
The problem - I cannot get any parallel execution in a pluggable database in version 12.1.0.2.0 and hoping you can shed some light on what I am missing. Hopefully I provided enough information to get started. It's almost like parallelism is not "...
Categories: DBA Blogs

Migrating LOB data across Oracle databases with different versions

Tom Kyte - Wed, 2017-07-26 07:26
Hello, Hope you are doing well. We are working on a database migration exercise where our source Oracle database is on 11g (11.2.0) and the target Oracle database is 12c (12.1.0). We are using expdp/impdp for exporting and importing data ...
Categories: DBA Blogs

Tuning a query with cursor expressions

Tom Kyte - Wed, 2017-07-26 07:26
<code>Hi Iam fairly new to oracle and i was given a query that takes a long time which is used to fetch some numbers associated with each info of an order, to be tuned. < select A.LS201_SEC_SEQ_NO,CHR(B.LS201_SEC_CODE+64)||'.'||B.LS201_SEC_NAM...
Categories: DBA Blogs

Update the records into the table.

Tom Kyte - Wed, 2017-07-26 07:26
Hello Experts, We have a below package procedure with that we are trying to update the table based on different if..else condition but its not updating the records based on condition. its updating the records based on last condition. For example, ...
Categories: DBA Blogs

PL/SQL Native Compilation

Tom Kyte - Wed, 2017-07-26 07:26
Hi, Based on the following link http://www.oracle.com/technetwork/database/features/plsql/ncomp-faq-087606.html , it says native compilation will perform much better than the default interpreted compilation(sorry if I misinterpreted). So, why ...
Categories: DBA Blogs

Recommended join style

Tom Kyte - Tue, 2017-07-25 13:06
Dear Oracle Masters, here is a poor disciple looking for guidance, I know the way to reach the true knowledge does not have an end, but I would appreciate few words to make my journey more safe, especially for my fellow travelers. Here is my q...
Categories: DBA Blogs

Different behaviours in implicit conversion on 11g and 12c NVARCHAR type

Tom Kyte - Tue, 2017-07-25 13:06
Hi, We have encountered the below scenario when used with 11g and 12c respectively, -- test data <code>create table t1 (tid nvarchar2(10) primary key); insert into t1 values ('123'); insert into t1 values ('123-00'); insert into t1 value...
Categories: DBA Blogs

Subpartitioning on IOT tables

Tom Kyte - Tue, 2017-07-25 13:06
Hello ! I've asked on many places, but no definite answer. We are using IOT tables as counters storage medium. They are perfect for that purpose as they consists of only PK + VALUE, or PK + VALUE1, VALUE2, VALUE3 up to some VALUEn n being s...
Categories: DBA Blogs

Interdependent Foreign Key Constraints

Tom Kyte - Tue, 2017-07-25 13:06
SQL> CREATE TABLE A(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2)); Table created. SQL> CREATE TABLE B(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2)); Table created. SQL> ALTER TABLE A ADD CONSTRAINT AA FOREIGN KEY(NO2) REFERENCES B(NO1); Table al...
Categories: DBA Blogs

Why NLS_UPPER maps lowercase 'i' to '?' when NLS_SORT is set to 'xturkish'?

Tom Kyte - Tue, 2017-07-25 13:06
Hello, Can you please explain why NLS_UPPER in following script maps lowercase 'i' to '?' whereas all other alphabets are mapped correctly to corresponding uppercase alphabets. <code>SQL> ALTER SESSION SET NLS_SORT="xturkish"; Session altere...
Categories: DBA Blogs

Why Do We Have Commit/Rollback on Explain Plan

Tom Kyte - Tue, 2017-07-25 13:06
Hi Team, I ran below query to see explain plan for my view. but after completion of query execution when i was trying to disconnect , it is asking about connection<conection_name> has uncommited. 1:- commit changes 2:- rollback changes 3;-...
Categories: DBA Blogs

How to change sequence.nextval increase amount

Tom Kyte - Mon, 2017-07-24 18:46
i have table a <code>create table a (sno number(10)); create sequence test start with 1 increment by 1 nocycle nocache; insert into a (sno) values(test.nextval);</code> I have executed the above insert statement 1000 times. now ...
Categories: DBA Blogs

Extracting very long string from JSON to CLOB

Tom Kyte - Mon, 2017-07-24 18:46
Hi, Tom. I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method. Here is a sample code: <code>DECLARE l_data CLOB := '{"text": "very long string...
Categories: DBA Blogs

Add Unique column using other column of the table

Tom Kyte - Mon, 2017-07-24 18:46
We have a table with 100 columns and number of records are around 1.3 million in it. Also having around 40 indexes created on the table. Subset of the table is as below <code>create table t ( username varchar2(100), DOJ date, recid varch...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs