DBA Blogs

MTS Configuration

Tom Kyte - Thu, 2018-07-12 23:06
Hi Tom ! I have configured MTS. My INIT.ORA and tnsnames.ora file configurations are given below. I have given PORT=1528 in the INIT.ORA file and when I query the V$DISPATCHER, I am surprised to see that the PORT numbers are chaning always i.e ...
Categories: DBA Blogs

Which Indexes are being used by our Application?

Tom Kyte - Thu, 2018-07-12 04:46
We have just gone live and I have noticed alarming growth in the indexes. Closer investigation shows several large, heavy transactional tables with a large number of indexes. I believe we are duplicating indexes and that some of the could be remove...
Categories: DBA Blogs

Is it Possible to Audit Manual Partition Creation Specifically?

Tom Kyte - Tue, 2018-07-10 16:06
Are you aware of any way to specifically audit the addition of new partitions? So far my searching has come up fruitless. Auditing is enabled within the DB which records each <code>alter table</code> command. However, that is too large a net. The spe...
Categories: DBA Blogs

Passing partition name dynamically to get records of a specific partitions from a partitioned table

Tom Kyte - Tue, 2018-07-10 16:06
<code> CREATE TABLE TOM_RESER_DERESERVATION ( ELEMENT_id VARCHAR2(200 BYTE), ELEMENT_LABEL VARCHAR2(200 BYTE), PRODUCT_NAME VARCHAR2(100 BYTE), CIRCLE VARCHAR2(100 BYTE), COUNTRY VA...
Categories: DBA Blogs

Partition query - limiting results

Tom Kyte - Mon, 2018-07-09 21:46
I have a situation where I am trying to determine the taxability of an invoiced line. If the invoiced line quantity is 6, for example, the detail lines should not exceed 6. The problem is that if one of the detailed lines causes the cumulative q...
Categories: DBA Blogs

COLUMNS to ROWS

Tom Kyte - Mon, 2018-07-09 21:46
Hi Team, Could you please have a look at below use case and help to form SQL/PLSQL using which I can get the below report.. Table: order_country : holds order id and country its belong. There can be 100 and more countries in that but for sampl...
Categories: DBA Blogs

Using identity columns in Oracle 12c

Tom Kyte - Mon, 2018-07-09 03:26
What is the difference between using sequence.netxval as DEFAULT value in a column or check the column as identity? Please, check the following scenarios: <b>SCENARIO 1:</b> CREATE TABLE USER1.TEST_TABLE ( ID NUMBER GENERATED BY DEFAULT AS I...
Categories: DBA Blogs

Using Factored Subqueries within SQLPLUS

Tom Kyte - Mon, 2018-07-09 03:26
I have a Perl script that invokes SQLPlus to execute an Oracle query. the SQL uses the WITH clause to create several named queries with the purpose of reusing them later within the query. the general structure of the SQL is as follows: <code> S...
Categories: DBA Blogs

Multi threading in PLSQL

Tom Kyte - Mon, 2018-07-09 03:26
Team, In current project, one of the batch process procedure is called in Java layer for Multi threading purpose. As current requirement, we need to call same stored procedure in PLSQL layer instead of Java layer. we tried to implement using DBMS...
Categories: DBA Blogs

insert primary key value by trigger

Tom Kyte - Mon, 2018-07-09 03:26
create or replace trigger t_emp_no before insert on emp_info begin insert into emp_info(emp_no) values(s_emp_no.nextval) end; / i tried to entry primary key emp_no values without showing it on oracle 10g form with S_emp_no sequence .Problem is...
Categories: DBA Blogs

Updating salary rows

Tom Kyte - Mon, 2018-07-09 03:26
hello Tom, i wrote that mentioned plsql update salary procedure.It compiled also successfully.But it didn't change any of my salary row.Actually,I am new in programming.I want to know .how does is work. how can i confirm that it is working & how ca...
Categories: DBA Blogs

Equality check and TO_NUMBER bug with trailing CHR(0) on SUBSTR function

Tom Kyte - Fri, 2018-07-06 20:46
Hello Tom ! It seems that Oracle has a bug in TO_NUMBER function on strings with trailing CHR(0). For example it happens when SUBSTR function is used for which there is not defined length parameter. Example: <code>select substr(session_id...
Categories: DBA Blogs

Inherit Privileges privilege to PUBLIC

Tom Kyte - Fri, 2018-07-06 02:26
I was doing a query on my database against dba_tab_privs where the grantee was PUBLIC. I noticed that for every user I have in my database, PUBLIC has "INHERIT PRIVILEGES" on that user. Should this be something that is allowed? How exactly is this...
Categories: DBA Blogs

LOB Storage parameters

Tom Kyte - Fri, 2018-07-06 02:26
TOM, I wrote a script to create a table with LOB columns. I specified the LOB storage parameters as follows: <code>CREATE TABLE CONFIGURATION (CFG_ID NUMBER(19,0) NOT NULL ENABLE, CFG_CONTEXT_ID VARCHAR2(255) NOT NULL ENABLE, CFG_...
Categories: DBA Blogs

maximum number of expressions

Tom Kyte - Fri, 2018-07-06 02:26
Hi, I have a this... Select * from MYDB where MYDB.ID IN (1,2,3...5000000) Any way to export without 1000 output lines ?? Thanks
Categories: DBA Blogs

How to manage disable/enable Maximize/Resize in oracle forms?

Tom Kyte - Fri, 2018-07-06 02:26
i need to manage disable/enable Maximize,Resize button by programming in oracle mdi parent forms in 10g. Please give me solution of this problems.
Categories: DBA Blogs

Where is alter session coming from? Every time I log in, I see "Session altered."

Tom Kyte - Fri, 2018-07-06 02:26
Every time I log in to our DB via sqlplus, I see this: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 5 11:48:29 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c E...
Categories: DBA Blogs

Does Oracle 9i Data Guard have real time sync?

Tom Kyte - Fri, 2018-07-06 02:26
Hi We have a Oracle 9i database ,When I set up the data guard I want to use real time sync I got a error .So I want to confirm whether Oracle 9i have this future
Categories: DBA Blogs

update cascade

Tom Kyte - Fri, 2018-07-06 02:26
How do I cascade update on the key fields of linked tables thereby ensuring referencial integrity.
Categories: DBA Blogs

I want to understand this decode function which applied in this query

Tom Kyte - Fri, 2018-07-06 02:26
<code> SELECT a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode, SUBSTR (a.accode, 1, 6) || '0000' saccode, accode, DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))), 1, SUM (...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs