DBA Blogs

lob storage options for partition table

Tom Kyte - Sun, 2018-09-02 21:26
Q1/IF I HAD TWO PARTITION (p1, p2). EACH PARTITION HAVE 3 tablespaces P1?Tsdata1,, p2? tsdata2 global fields data type P1?Tsidx1,, p2?tsidx2 for primry key index And P1?Tsblob1,, p2?tsblob2 for photo field How can I create table( tab_test) wi...
Categories: DBA Blogs

Storage XMLs in Oracle 12cR2 database

Tom Kyte - Sun, 2018-09-02 21:26
Hello! I created a database in order to store XMLs. I'm going to storage a large amount of data. I was told that Oracle 12cR2 allows to storage XMLs and handle them very good. I created a table with a xmltype column. <code>CREATE TABLE test...
Categories: DBA Blogs

Slow PLSQL procedure performance

Tom Kyte - Sun, 2018-09-02 21:26
1) I have a procedure with 50 select statements, when ran it is running good performance level, but after 30 days I ran the same procedure without changing anything, it is taking more than before time, there is any system crash, network band prob...
Categories: DBA Blogs

PL/SQL date processing

Tom Kyte - Sun, 2018-09-02 21:26
Hi sir, I have one dought. How to find position of the month in pl/sql and also how to print quarter of the month. So, please tell me the syntax and explanation. Input:- JANUARY Output:- JANUARY IS FIRST QUARTER JANUA...
Categories: DBA Blogs

Want to use single select statement instead of various union all's for fetching previous quarter data

Tom Kyte - Sun, 2018-09-02 21:26
I have 5 select statements using union all(showing a few in here) want to use a single statement to fetch the revenue from a table . So based on the condition that AND period_id>=1009 AND Opp_Created_Date_Period_Id<=1009(for every previous quart...
Categories: DBA Blogs

FOR UPDATE SKIP LOCKED with ROWNUM

Tom Kyte - Sun, 2018-09-02 21:26
Hy guys, I searched all the forum but I didn't find any clue about it. I have a stage in table that multiple threads consume it. To avoid deadlock, i'm using something like this: <code> SELECT ID_MESSAGE, FROM TB_STAGE_IN S WHERE S.CD_STA...
Categories: DBA Blogs

Letter ù is OK for the table's name but not for a column's name

Tom Kyte - Sat, 2018-09-01 08:46
Hello The Oracle Masters, I have the following problem : impossible to use the lettre u for a column's name but it is OK for a table's name. Very important : I use Windows 7, in French, and the Oracle 12.1 virtual box with Linux. My principal c...
Categories: DBA Blogs

TABLE function and where clause parameters retrieving

Tom Kyte - Fri, 2018-08-31 14:26
Hi there, My goal is to return fieldX or fieldY depending on the where clause (which I don't have any control on since it's generated by a third party software). The TABLE() function is very close to what I want to achieve : select * from T...
Categories: DBA Blogs

Regarding performance issues

Tom Kyte - Fri, 2018-08-31 14:26
Hi Tom, I am stuck in a Scenario where I have to extract 85 million records in a table and that table is not having any primary key. After extracting all the data(full load) into target table my concern is I want to make this data loading increme...
Categories: DBA Blogs

Getting calling where clause or select statement in pl/sql function

Tom Kyte - Fri, 2018-08-31 14:26
Hi there ! Is there a way of getting the select statement that called a pl/sql function or its where clause ? For instance, if the call is : SELECT customFunction(parameter) AS FuncResult, field1 FROM table WHERE field2 = 'test' Is it pos...
Categories: DBA Blogs

Issue with Trigger creation

Tom Kyte - Fri, 2018-08-31 14:26
I am having issues with Trigger. Trigger creation is successful but Once user try to test it, It is failing. Before trigger creation, tester is able to add new email to email_t table. But Once I place trigger, they are getting this : <code> ...
Categories: DBA Blogs

system tablespace is full with audit files

Tom Kyte - Fri, 2018-08-31 14:26
<code></code>hi there, i have a problem here,my system tablespace is getting full these are current values for my system tablespace TABLESPACE_NAME FILE_NAME ALLOCATED_SIZE_IN_MB FREE_SIZE_IN_MB -------------...
Categories: DBA Blogs

Some Statistics on this Blog

Hemant K Chitale - Fri, 2018-08-31 00:04
This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.



Categories: DBA Blogs

Group by displaying location according to custom sort

Tom Kyte - Thu, 2018-08-30 20:06
i have a table id name location <code> 1 aaa home 1 aaa village 1 aaa office 2. bbb village 2. bbb office 3. ccc home 3. ccc office</code> when i write a query i should get single record for each having precendence home>office>villag...
Categories: DBA Blogs

Find Existing Oracle Password Encryption

Tom Kyte - Thu, 2018-08-30 20:06
We currently run Oracle 10g. We have WebDB applications. The Webpage where users can change their passwords to access the WebDB applications no longer works. The only access I have to modify or unlock user access is the Oracle Enterprise Managemen...
Categories: DBA Blogs

utl_http work with 11g and not with 12c

Tom Kyte - Thu, 2018-08-30 20:06
Hi Team Is there any reason why unsecured connection with basic authentication (http://user:password@server_address) is working with 11g and not with 12c (12.2.0.1.0)? I am using utl_http package to connect from database servers to the same external...
Categories: DBA Blogs

[Oracle Text] Score(1) and Contains

Tom Kyte - Thu, 2018-08-30 20:06
Hey AskTom, the documentation for Oracle Text is very poor so I have a question: Is it best to use the CONTAINS() clause to find a certain result, or should we use score(1) for this operator? I mean such a case where we are interested in score ...
Categories: DBA Blogs

Question with 2 partition conntain 6 datafiles

Tom Kyte - Thu, 2018-08-30 20:06
HI Tom ? ? I have a Database of more than 250 tables. I worked 3 Tablespaces (TB1, TB2, TB3). TB1: Used to store 5GB data tables TB2: Used to store basic key index indexes for general tables and has a size of 2 GB TB3: Used to store fields cont...
Categories: DBA Blogs

The Partner Experience Oracle OpenWorld 2018 #OOW18

At Oracle OpenWorld you will be surrounded by the new, the cutting-edge, and the sometime unfamiliar. From today’s most-trusted products to tomorrow’s technologies, you will leave infused...

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

Need a query to identify the number of selects, inserts, updates and deletes from an application table

Tom Kyte - Thu, 2018-08-30 01:46
Hi, One of our customer wants to know how to get the DDL DML count happened on application tables with timestamp details before 60 or 90 days. Please provide some Query to get these details. They dont want to enable Auditing, is that possib...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs