DBA Blogs

How to constrain an employees salary against his manager in the same table

Tom Kyte - Tue, 2018-09-04 10:06
I just want to create triggers for checking salary that employee salary cannot exceed manager's. there are one table: EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SALARY,DEPTNO) FK:MGR references EMPNO Thanks.
Categories: DBA Blogs

Deploying Oracle RAC 18c for Linux x86_64 on OL 7.5

Tom Kyte - Tue, 2018-09-04 10:06
Hello, teams These two days I've deployed Oracle RAC 18c on OL 7.5 (my Linux server) via VirtualBox 5.2.18. During this time I encountered some problems as follows, (1) after installing RPM "oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm",...
Categories: DBA Blogs

sql performance

Tom Kyte - Tue, 2018-09-04 10:06
Hi Tom,I have met a SQL that with the latest statistics and the execution plan is in the wrong estimate-rows ,that's to say,it's a large difference between the estimate and the actual,the sql statement and execution is below: <code>select t1.card_no...
Categories: DBA Blogs

SQLLDR with multiple when conditions

Tom Kyte - Tue, 2018-09-04 10:06
Hello, we have a requirement to load multiple data based on different conditions. The details are as below, Create: <code> create table table_a (empno number, empname varchar2(50),salary number, status varchar2(30)) / Control file: opt...
Categories: DBA Blogs

Partner Webcast – Oracle New platforms for APIs and Integration on Cloud

APIs are at the center of the digital economy and new business models are associated with them. They have become a foundational technology for the development of scalable enterprise applications and...

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

SYSTEM tablespace growing too huge

Tom Kyte - Mon, 2018-09-03 15:46
My System tablespace has grown to approx 15GB. Is that normal?? The top 3 space taking segments are as follows- I_HH_OBJ#_COL# 1.375 I_HH_OBJ#_INTCOL# 1.375 I_COL1 ...
Categories: DBA Blogs

ITL waits and deadlocks

Tom Kyte - Sun, 2018-09-02 21:26
Hi Tom, We are facing one issue where we are running jobs in parallel basically same procedure called with different parameters.Proc merges into a target table , so when we are trying to run in parallel. We are seeing enq : Tx ITL transaction wait. ...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs