DBA Blogs

A select from a view is much much slower than the same SELECT directly from a view creation query

Tom Kyte - Thu, 2017-05-11 17:26
<code>Hi, Tom, A developer complains the gradually slowness in his query from a view. But query was run much faster from the same logic without using the view. Basically using the same SELECT logic directly from the view creation script, it is muc...
Categories: DBA Blogs

database opening error of unnamed datafile

Tom Kyte - Thu, 2017-05-11 17:26
Dear Sir, I recover my database from my backup files manually by simple copy paste command in redhat linux. Now my database is opening till mount stage only. Whenever i try to come in open stage it shows an error of to use resetlogs or noreset...
Categories: DBA Blogs

Reference Partitioning - Child Partition_name issue.

Tom Kyte - Thu, 2017-05-11 17:26
Hi, This is the scenario: Consider Parent_table that is list partitioned based on dept_no - with partition_name specified as 'P_Dept_no'. Parent_table -> Child_table_1 (reference_partitioned based on Parent_Table) -> Child_table_2 (reference_par...
Categories: DBA Blogs

UTL_HTTP POST with filename

Tom Kyte - Thu, 2017-05-11 17:26
Tom, I am trying to POST the webservice all using UTL_HTTP function with the file from pl/sql procedures. The same processed working from Curl scripts. PL/SQL : <code> DECLARE req utl_http.req; resp utl_http.resp; l_value VARCHAR2(10...
Categories: DBA Blogs

Extract Value From XML

Tom Kyte - Thu, 2017-05-11 17:26
Hi Team, I need to create a stored procedure which will take below mentioned XML as an input and display the ITEMNO using DBMS_OUTPUT_PUT_LINE statement. <code> INPUT XML: <column name="EVENTINFO" type="xml-fragment"> <ORDER type="xm...
Categories: DBA Blogs

ORA-12535: TNS: operation timed ORA-609 opiodr aborting process

Tom Kyte - Thu, 2017-05-11 17:26
ORA-12535: TNS: operation timed along with this ORA-609 opiodr aborting process as the secondary error.While this error is occurring tnsping takes longer than expected and SQLPLUS / also is taking too much time ,ping using the IP is working fine ...
Categories: DBA Blogs

Exchange Partition and data loss

Tom Kyte - Thu, 2017-05-11 17:26
Assumption: 1) INSERTS are continuously applied on table A Requirement: Need to exchange partition with another table but do not want to loose any data ( I am fine with the "ORA-00054: resource busy and acquire with NOWAIT specified or timeout ...
Categories: DBA Blogs

ORA-12154 Error Connecting to A Database Using ODBC (ONLY) (Using SQLPlus It connects Just Fine)

Tom Kyte - Thu, 2017-05-11 17:26
I am trying to setup an ODBC connection on Linux. I have create the following odbcinst.ini file. <code> [OracleODBC-12.1] Description = Oracle ODBC driver for Oracle Driver = /u01/app/oracle/product/12.2.0/client_1/lib/libsqora.so.12.1 FileUs...
Categories: DBA Blogs

Calling PL/SQL packaged stored procedure from Java with TABLE OF RECORD

Tom Kyte - Thu, 2017-05-11 17:26
Dear Oracle Masters, I am trying to call an Oracle packaged stored procedure that has a formal input parameter of TABLE OF RECORD type. There are plenty of examples of calling a SP with either a RECORD or TABLE type but not both. I also want to ta...
Categories: DBA Blogs

Oracle is hanging after few days (intermittent) and cannot connect as user sys even with prelim option and lsnrctl do not work too

Tom Kyte - Wed, 2017-05-10 23:06
I have several clients that is having this intermittent issue where Oracle just hang. Only solution is stop and start the win DB service and sometimes it needs to be done twice. When the DB is hanging both CPU and memory are good. I tried co...
Categories: DBA Blogs

Object No Longer Exists - Non DDL Issue

Tom Kyte - Wed, 2017-05-10 23:06
We have partitioned tables (with indexes) with about 12 million records in each partition. We transfer data to non-partitioned (no indexes) table, perform calculations, and move data back to partitioned table when finished. These are all parallel INS...
Categories: DBA Blogs

expdp with same schema

Tom Kyte - Wed, 2017-05-10 23:06
I have export schema of production (CPG_PROD) now i want to restore on development server where i have alredy CPG_PROD schema. Now i want to restore production CPG_PROD On development but same name is already there so How can i import the produc...
Categories: DBA Blogs

ORA-01400: cannot insert NULL into ( blob )

Tom Kyte - Wed, 2017-05-10 23:06
Hello Tom, I have table with two column, this is sql code : CREATE TABLE AVR_xml ( x_xml NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL, xml BLOB NOT NULL ) STORAGE (INITIAL 101204K NEXT 9200K) TABLESPACE "EX_DBS" ...
Categories: DBA Blogs

How to make rows to columns

Tom Kyte - Wed, 2017-05-10 04:46
Hi Tom, <code>create table emp_det ( name varchar2(15), occupation varchar2(15) ); insert into emp_det values('samantha','doctor'); insert into emp_det values('julia','actor'); insert into emp_det values('maria','actor'); insert into emp_...
Categories: DBA Blogs

inactive session kill

Tom Kyte - Wed, 2017-05-10 04:46
Hi team, As i have seen i my db where active users are 15 and inactive session are 1500 so load become high then i kill the inactive session like alter system kill session 'SID,SERIAL#' immediate; it works fine and load become normal. So ho...
Categories: DBA Blogs

db cache advice

Tom Kyte - Wed, 2017-05-10 04:46
How are the ON, READY and OFF values supposed to be used ? At first I had the impression I had to do: OFF -> READY -> ON, so READY would allocate memory to be used. Should I do: OFF -> ON -> READY (to maintain memory allocated) -> ON ?
Categories: DBA Blogs

Same SQL, Same Plan but different times on 11gR2 Vs 12cR1

Tom Kyte - Wed, 2017-05-10 04:46
Hi Chris/Connor, Hope you are doing good!! Need you advise on this strange situation we are stuck in, So We have moved from 11gR2 to 12cR1 database and currently in UAT phase, however, it all came to an halt when one of our critical process ...
Categories: DBA Blogs

Reset 12c identity column sequence to 1

Tom Kyte - Tue, 2017-05-09 10:26
For entire schema need a script to reset all 12c identity columns seq to 1. Identify all identity columns....I believe there should be a data dictionary view. Then do some alter script. Like select 'alter identity ' from view ; Thanks. ...
Categories: DBA Blogs

Daily Big Amount of data deleteion on partitioned table, but not by partition key

Tom Kyte - Tue, 2017-05-09 10:26
Hi Tom, First, Thanks for the great effort answering everyone questions. lately im struggeling in deleting data in our system(OLTP). The tables we need to delete are all partitioned by start_Time(which mostly being used by every query in the s...
Categories: DBA Blogs

Table join only if records exist

Tom Kyte - Tue, 2017-05-09 10:26
I have the following tables... Student Info: Student ID* Student Name etc. Available Courses: Semester* Course ID* Subject Course Number Course title Registration: Student ID* Semester* Course ID* *Designates primary key As ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs