DBA Blogs

Automatically archive data based on timestamp

Tom Kyte - Fri, 2018-12-21 17:26
Hi Tom I need to perform data archival in Oracle 12c, after which the archived data must be accessible by the application, when queried for. Is there a way to automatically archive data which are >1 year old based on the 'updated_date' column for ...
Categories: DBA Blogs

@OracleIMC Season's Greeting & Happy 2019

.container{ -webkit-box-shadow: 0px 2px 8px rgba(147, 150, 153, 0.3); -moz-box-shadow: 0px 2px 8px rgba(147, 150, 153, 0.3); box-shadow: 0px 2px 8px rgba(147, 150, 153,...

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

Data types

Tom Kyte - Thu, 2018-12-20 23:06
Hi Tom Tom I am sure you have answered my question several times as " floats are synonyms for numbers. But I have a situation at work where my colleague wont agree with me that there is no difference between oracle float and number datatype with ...
Categories: DBA Blogs

join example, new vs old syntax

Tom Kyte - Thu, 2018-12-20 23:06
Hi Tom, I know in oracle9i we have the cross join and full outer join. Do they exist for 8i if so can you point me to the documentation. If they don't can you give an example/breakdown of how to rewrite in 8i. Thanks in advance,
Categories: DBA Blogs

Show previous YTD if query is ran in January else Current YTD

Tom Kyte - Thu, 2018-12-20 23:06
I'm looking for the logic to filter data Previous YTD if the query is ran in January else filter Current YTD. Any help would be greatly appreciated. Thank you Marc
Categories: DBA Blogs

SQL Plus doesn't consider special characters in filenames

Tom Kyte - Thu, 2018-12-20 23:06
<b>There are files that contain special characters (i.e. $, #) in the name, and SQL Plus cannot find them. </b> <code>$ sqlplus -L user/password@host:port/SID @/path/to/file/create$spec_view.sql SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec...
Categories: DBA Blogs

ORA-29283: invalid file operation

Tom Kyte - Thu, 2018-12-20 23:06
Hi Tom, Thank you for being with us and for all your support over the years. From last few days I am getting an error while writing file from my application i.e Exception Info: ORA-29283: invalid file operation. But if I run same procedure f...
Categories: DBA Blogs

Partner Webcast – Oracle on Serverless Computing: Developing FaaS with Oracle Fn Project

It is probably one of the most wrongly named idea in the world of IT (what do you mean ‘serverless’, there are no servers? Where should I run my code then?) which is quite likely to take...

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

Unique constraint on function based index

Tom Kyte - Thu, 2018-12-20 04:54
Hi TOM, I try to create an unique constraint using FBI, it fails with "ORA-00904: : invalid identifier" error. It looks like "alter table" doesn't accept functions in expressions for unique constraints - <code> create table ctest (a integer, b i...
Categories: DBA Blogs

Scheduling backup

Tom Kyte - Thu, 2018-12-20 04:54
Schedule ?backup Oracle database? as following: 1- Saturday backup incremental 0 at 22 :15 2- Rest of the week incremental 1 at 23:00
Categories: DBA Blogs

How to unload table data to csv file in old oracle version, the fastest way

Tom Kyte - Wed, 2018-12-19 10:46
We are currently using Oracle Database 8i. We have multiples table and around 600 millions of rows. We use SQLPLUS to export rows to csv with nested queries. Configuration: <code> set term off set feedback off set linesize 32767 set hea off...
Categories: DBA Blogs

Recursive query to get base tables for views, but from a list of views

Tom Kyte - Wed, 2018-12-19 10:46
Hi, I appreciate that similar questions have been asked before, but I am struggling with the next step if you like. I want to get all the base tables (recursively) used by a list of views. From various posts here I know how to do this for a sin...
Categories: DBA Blogs

Transfer data from one data file to another data file,so that I can drop a data file without losing data

Tom Kyte - Wed, 2018-12-19 10:46
Dear Team, My table space has 28 data files each with size of 30g(approx). Below the query I used to find out each data file size and free space in that data file. <code> select t1.file_id,total,free from (select file_id,trunc(bytes/1024/10...
Categories: DBA Blogs

PL/SQL Parallel function performance

Tom Kyte - Wed, 2018-12-19 10:46
Hi, I was trying to optimize one of the batch update queries (given below) in my application <code> UPDATE schema1.TEST_RECORDS PARTITION (PARTDEF) gr SET gr.purge_status_cd = 'PURGE_PENDING', gr.purge_date = SYSDATE WHERE EXISTS ( SELECT 1 F...
Categories: DBA Blogs

Nesting of functions

Tom Kyte - Wed, 2018-12-19 10:46
Hi all, I have seen many number of times a SQL function nested one another to greater depth Concat(substring(instring,concat()))... this is just an example My question is exactly this nesting work and how it is evaluated to ,which function goin...
Categories: DBA Blogs

Can one segment contain data from more then one object & Block size of Buffer cache

Tom Kyte - Wed, 2018-12-19 10:46
Hello Tom, I have a few doubts regarding Segments, extents & data_block 1. Can a Segment contain data from more then one object (Table, Index or MV) i) if no then new segments must be created with each new objects and upon DDL operations (like...
Categories: DBA Blogs

Huge Read Only Data Protection and H.A, Best Practice

Tom Kyte - Wed, 2018-12-19 10:46
Hi TOM, Suppose we have a VLDB with partitioned tables on distinct tablespaces per partition. Data on partitions (Tablespaces) can be read-only after a few months. Now we have huge (RMAN) backups from this read-only tablesapces. We want to setu...
Categories: DBA Blogs

UKOUG “Lifetime Achievement Award” Speaker Award

Richard Foote - Tue, 2018-12-18 17:32
I was recently very honoured and flattered to have received the “Lifetime Achievement Award” Speaker Award from the UKOUG. I have only managed to get to the excellent UKOUG Tech conferences on a couple of occasions, so it was both a thrill and a surprise to be so honoured. Unfortunately, I wasn’t able to make […]
Categories: DBA Blogs

Database Design for Invoices Table with Many Null Columns

Tom Kyte - Tue, 2018-12-18 16:26
Hello, Ask Tom Team. I have a 70 columns table storing valid invoices. There are cases where the invoices are rejected because business rules. When the invoices are rejected just a few columns are filled (up to 5), then all 65 remaining columns ar...
Categories: DBA Blogs

Parallel execution of Select .. for update with Insert

Tom Kyte - Tue, 2018-12-18 16:26
Hi Tom We have a stored proc something like below: <code>Proc P1 (p_id in varchar2) is cursor c1 is select a,b,c from t1, t2 where t1.pk = t2.fk and t1.id = p_id ; v_id varchar2(10); Begin Open c1 (p_id); fetch c1 into v_...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs