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

how to equate substring of one table column to a value in a list from another table column as part of an exists?

Tom Kyte - Tue, 2018-12-18 16:26
Hello Oracle Masters, I was looking at 'connect by level' (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way? We have a table of products ...
Categories: DBA Blogs

How to extract distinct group of associated records (not exactly hierarchical in nature)

Tom Kyte - Tue, 2018-12-18 16:26
Hello All, It would be greatly appreciated if someone can provide a way out on how to extract distinct group/array of associated (they are not of parent-child relationship or hierarchical) records as shown below. The same table has been created wi...
Categories: DBA Blogs

ORA-01779: cannot modify a column which maps to a non key-preserved table

Tom Kyte - Tue, 2018-12-18 16:26
This is on checking below link - https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113#followup-3016346200346884929 Below answer given by Tom - "Consider, if T had: <code>OBJECT_NAME OBJECT_ID -------------- ----...
Categories: DBA Blogs

How do I Declare/Define a date field in a query

Tom Kyte - Tue, 2018-12-18 16:26
When i was working with MSSQL I could DECLARE a start & End date (or other parameters) to avoid having to change a variable in the query itself. This is what the table looks like -------------------- ------ ------ DateTime Action User --------...
Categories: DBA Blogs

Business case problem

Tom Kyte - Mon, 2018-12-17 22:06
I have a problem with that i have table emp_table(name, salary, department_name) I want to move data from this table to two tables employees(name,salary,department_id), department(department_name) And i putted a trigger on departments to make ...
Categories: DBA Blogs

Cannot append more than 32kb data to a clob variable

Tom Kyte - Mon, 2018-12-17 22:06
Hello, I am trying to write a pl/sql procedure(oracle) in which I am storing the final output in a clob variable, but it gives me the error when I test the procedure by providing the input: [Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs