DBA Blogs

Index rebuild

Tom Kyte - Sat, 2016-08-27 11:46
Hi team, As i want to know that the view in oracle with the help of that we can check when the index is required to rebuild. like leaf node , deleted node something So, could you pls let me know the exact view or query with the help of that we can...
Categories: DBA Blogs

storing and indexing prefixed values

Tom Kyte - Sat, 2016-08-27 11:46
Hi Tom, We've got a table that'll hold about 1M rows over the course of its lifetime. We need to add a column that the business want to search for, so indexing it seems like a good idea. Column is - a 10-character string, - mostly unique (...
Categories: DBA Blogs

Difference between DBMS and UTL packages

Tom Kyte - Sat, 2016-08-27 11:46
Hello, There are around 13 UTL packages for accomplishing many purposes in Oracle database, and their package name starts as UTL_(some text). For eg., UTL_SMTP, UTL_TCP, etc. I was just wondering why we have the name of these packages as UTL_(s...
Categories: DBA Blogs

Isolation Level Serialization

Tom Kyte - Fri, 2016-08-26 17:26
Hi, We have two sessions: Setup Data: insert into table1 values(table1_seq.nextval, 'A', 'AA'); insert into table1 values(table1_seq.nextval, 'B', 'BB'); commit; Session 1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; select * from ta...
Categories: DBA Blogs

Conditionally join (left or inner) two table

Tom Kyte - Fri, 2016-08-26 17:26
Hi! First of all thanks for taking time to read and answer my question. So, I got two set of rows which needs to be joined. In some cases it should be outer left join, but in rest it should be inner. Is it possible make such query? Small ...
Categories: DBA Blogs

Commit Frequency and Rollback

Tom Kyte - Fri, 2016-08-26 17:26
Hi Tom, In our application, we have set the commit frequency as 50 and in one case one record fails to get inserted (due to a constraint violation exception). So, in that case, would all the 50 records in that commit slot get rolled back? P...
Categories: DBA Blogs

SQL Translator in 12c

Tom Kyte - Fri, 2016-08-26 17:26
Started reading about 12c SQL Translator from documentation <u>http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#CHDEIGFA</u> Here is a quick demo on that. <code>demo@ORA12C> create table t1 as select * from all_objects; Table cre...
Categories: DBA Blogs

pivot twice

Tom Kyte - Fri, 2016-08-26 17:26
First, the following sql return this. select trim(p.proc_name) as com_client ,op.name as trait_name , op.value trait_value from oen_procinfo p , oen_personality op where op.interfaceid = p.interfaceid and not p.proc_name like ...
Categories: DBA Blogs

Bulk Collect

Tom Kyte - Fri, 2016-08-26 17:26
There is column called jobstream_dt in a table spc_records I want to run a Delete script using Bulk collect technique to delete rows from that table where jobstream_dt is older than 540 days. We have around 125 million rows in that table which are...
Categories: DBA Blogs

Links for 2016-08-25 [del.icio.us]

Categories: DBA Blogs

Readers don't block Readers

Tom Kyte - Thu, 2016-08-25 23:06
HI Connor Readers don't block Readers, is this true? As I understand waiting for a resource is also a kind of blocking. I am waiting because the access is blocked. Then please help understanding the below " ---- Another example of a prob...
Categories: DBA Blogs

Oracle forms to create a database user

Tom Kyte - Thu, 2016-08-25 23:06
Hello, Is there a way to create a form with username and password and create the user in the database? I have tried using a host command to log into sqlplus and execute the create user .... but it doesn't want to execute from the form host comm...
Categories: DBA Blogs

WHILE Loop Running forever

Tom Kyte - Thu, 2016-08-25 23:06
Hi , My aim is to print week days from a given date, for ex : IF I provide a date like '12-DEC-2012' ( Which is Thursday ) then my program should print as below FRIDAY SATURDAY But below code is running in a loop for ever, I am unable ...
Categories: DBA Blogs

Lock for "grant"

Tom Kyte - Thu, 2016-08-25 23:06
Hi Tom, When we *grant* a privilege on a table acquires a lock on the table. Just curious to know the following... -- Why does Oracle acquires a lock on the table (apart from grant being a DDL) when we issue a grant. Does it change the definiti...
Categories: DBA Blogs

Synchronous refresh cannot be used for the non-partitioned tables?

Tom Kyte - Thu, 2016-08-25 23:06
Hi, as I want to use the new technology in 12c called "Synchronous refresh" of the MView or group of MViews, I read the documentation ( https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG029 ), but for me it's not 100% clear what are the res...
Categories: DBA Blogs

The last query gives the error "Invalid Identifier error".Please explain me whether I have refer the correct table?

Tom Kyte - Thu, 2016-08-25 23:06
---------------------types------------------- create type course_ty1 as object( cid number, titlte varchar(15), credits_req integer ) / create type unit_t as object( ui number(5), credit number(5) ) / create type offering_t as obj...
Categories: DBA Blogs

Partner Webcast – Oracle Big Data Preparation Cloud Service: Transform to Self Service Data Preparation for Business Users

When you work with data, the perception is that you spend most of your time on analytics. But in reality, you will devote much more time and effort on importing, profiling, cleansing, repairing,...

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

Future of Oracle PL\SQL

Tom Kyte - Thu, 2016-08-25 04:46
Hi Tom, I am working as PL\SQL developer, I wants to know the future job opportunities for PL\SQL,else whether I needs to update my skills in some other technology to survive in IT industry? Kindly do me suggestion. Thanks, Ganesh
Categories: DBA Blogs

Insert statement finished as per user_segment table but session is not releasing control

Tom Kyte - Thu, 2016-08-25 04:46
Hi Tom, I need to dump data into a temporary table where I do not need to have any data foreigh key, primary key. I kept the table in NOLOGGING mode with parallel 4. I am inserting with /*+ APPEND */ direct path insert. The data is getting inserte...
Categories: DBA Blogs

Oracle SQL Developer

Tom Kyte - Thu, 2016-08-25 04:46
I have a query that returns 50,000 records, how can I quickly get to the last record? if I use the scroll arrow of slide, it takes a long time before it gets to the bottom of the 'Query Results" is there a quick way to go to the last record? I hop...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs