DBA Blogs

locking disconnected tables

Tom Kyte - Wed, 2018-01-24 09:06
Hi Tom, I have this problem: I need to "lock for update" records in two disconnected tables. For "disconnected" I mean two tables that haven't any relation, for example the tables EMPLOYEE and FOLDER: when I have to update an employee record I woul...
Categories: DBA Blogs

"APOS;" appearinig in SQL*PLus output. Want actual apostrophe instead.

Tom Kyte - Wed, 2018-01-24 09:06
How can I suppress the appearance of "apos;" and similar web-friendly tags from my SQL*Plus output. In place of "apos;" I'm actually looking for the apostrophe "'". Thank you!! - Mike Kemp
Categories: DBA Blogs

Upgrade 11204 to 12201 with standby in place

Tom Kyte - Wed, 2018-01-24 09:06
We are upgrading 11204 Databases to 12201. We build new linux server, create 12201 DB, expdp from 11204 then impdp into 12201. We don't do "in place" upgrade. To upgrade a master with physical standby, what will you recommend ? I think the be...
Categories: DBA Blogs

clob data conversion

Tom Kyte - Wed, 2018-01-24 09:06
I Have table, which has clob data type, value stored is in hexadecimal data type. Please let me know how can i convert clob data to varchar
Categories: DBA Blogs

How much overhead is caused by adding a mostly empty BLOB column to a table?

Tom Kyte - Wed, 2018-01-24 09:06
Let's say I have a large-ish (a few hundred thousand rows) table that contains file names, file sizes and other meta-information for files, but not the actual files themselves. I then decide to add a BLOB column to the table to make it possible to...
Categories: DBA Blogs

Evaluation of expressions

Tom Kyte - Wed, 2018-01-24 09:06
Does Oracle perform short-circuit boolean evaluation when evaluating expressions? For example, consider the following part of a query: select lcs_id, loan_id, client_id, security_id, masterfund_id, category, calc_lvr, price, ...
Categories: DBA Blogs

Critical Production Databases running on Standard editions

Tom Kyte - Tue, 2018-01-23 14:46
Dear Tom Please explain the consequences of running a production database on Standard edition and no-archivelog mode. Regards Hari
Categories: DBA Blogs

Primary key with Non Unique Index

Tom Kyte - Tue, 2018-01-23 14:46
Hi Team, I was just playing around with some stuff so thought sharing with you. My understanding for Primary key was that Oracle uses Unique Index to enforce primary key constraint on table's column to be unique. Below is the code that I tri...
Categories: DBA Blogs

@DATENOW()

Tom Kyte - Tue, 2018-01-23 14:46
We have a situation where the golden gate is used to replicate the source data into target ( both source and target are oracle 12c). on the target we have a column ( Target Timestamp) , primarily to track the date/time the a row is replicated. this ...
Categories: DBA Blogs

XMLTYPE null element behavior on 11gR2 vs 12.1/12.2

Tom Kyte - Tue, 2018-01-23 14:46
Hi Guys, I've just noticed inconsistency between behavior of conversion of object/collection data to XMLTYPE between 11g R2 and 12.1/12.2 database. Testcase: <code> create or replace type test_dummy_object as object ( id number, "name...
Categories: DBA Blogs

Merge statement failing when selecting from dual

Tom Kyte - Mon, 2018-01-22 20:46
Hi Tom, How do i make use of the query in the ref cursor inside a merge statement. in the "ON" condition it says "Src.column_name " is an invalid identifier. <code> create or replace PROCEDURE TingTong ( in_tax_year IN tdir_le...
Categories: DBA Blogs

Optimizer choosing Index Full Scan over Index Fast Full Scan

Tom Kyte - Mon, 2018-01-22 20:46
Per Connor's request, I asked our DBA to generate the trace files and sent them to the asktom_us e-mail address. Thanks. Alfred ------------------------------------------------------------------------------ Hello, I have observed in an Ora...
Categories: DBA Blogs

Tables with no tablespace

Tom Kyte - Mon, 2018-01-22 20:46
Hi Tom, This may sound a little silly question but have not come across such situation yet. I have few tables create by user (not owned by sys or system) which do not have tablespace name. The following query returns table names. select ...
Categories: DBA Blogs

ORA-14300 on Interval partition

Tom Kyte - Mon, 2018-01-22 20:46
Team, We have an application, where we receive a set of files to be processed by application in 24/7. Each time a file recieves we generate a file_key in master_table using sequence, and take that file_key to load the file contents into this tab...
Categories: DBA Blogs

SQL*Loader-510: Physical record size limit

Tom Kyte - Mon, 2018-01-22 20:46
Dear Tom, I tried to load a text file to Oracle db using SQL*Loader. Here is the table definition: create table mytable ( key varchar2(40), adate date, atag number(10), aclob clob, version number(10) ); Here is...
Categories: DBA Blogs

Query not working in Oracle , Missing expression error

Tom Kyte - Mon, 2018-01-22 20:46
i want to get column 2 of y table if column 1 of y table is equal to column 1 of z table this query working in mysql but,this query not working in oracle am getting missing expression error....can u please help me to sort this issue
Categories: DBA Blogs

Partner Webcast – Exadata Database Machine X7 is the biggest change in Exadata in many years

body {-webkit-text-size-adjust:none; -ms-text-size-adjust:none;} body {margin:0; padding:0;} table td {border-collapse:collapse;} h1, h2, h3,...

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

Data Guard And Notification

Tom Kyte - Mon, 2018-01-22 02:26
As part of the my project, we are migrating legacy system to new system. As a part of one process, we have reporting data base from which different reports are generated. Report generation is started by trigger(alert) once the replication of producti...
Categories: DBA Blogs

how to recover a bad block in a big file tablespace

Tom Kyte - Mon, 2018-01-22 02:26
some days ago,I asked a question about big file tablespace vs normal small file tablespace, you said "That said, its rare to need to recover a particular datafile - the most common scenarios are recover a database, or recover/fix some blocks. In eith...
Categories: DBA Blogs

How many context switches my session does?

Tom Kyte - Mon, 2018-01-22 02:26
Hello, My questios is already in subject. What performance views has information about about PL/SQL and SQL context switches? SELECT n.Name FROM v$statname n WHERE n.Name LIKE '%context%' Returns only "OS Voluntary context switches" and "OS...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs