DBA Blogs

Tuning SQL not in v$sql with sqltrpt.sql

Tom Kyte - Thu, 2016-08-11 11:06
Hi tom, I have not very much in performance tuning but due to learn i am taking the performance related query also from user end. For tuning i have generated awr report and get top 10 queries and take the execution plan of that and generate ...
Categories: DBA Blogs

Oracle Proxy User & database User Connections

Tom Kyte - Thu, 2016-08-11 11:06
Is there a way to create a database user that is only allowed to connect via a proxy user? A logon trigger to check how a user connected would work but I was wondering if there was a user or profile setting that could be set so a logon trigger woulf...
Categories: DBA Blogs

Tips on creating temporary table

Tom Kyte - Thu, 2016-08-11 11:06
Hi Tom, First of all, I would like to thank you for all the contribution you made on the oracle community. I started working on oracle DB 9 years ago, and I can confidently say that I got all my knowledge from asktom :) I have followed your blog f...
Categories: DBA Blogs

How to do a combination to sum to find all the options up to a certain value

Tom Kyte - Thu, 2016-08-11 11:06
Hello TomConChris I can have up to 7 (in reality 50) values, and I must sum all the combination and choose all the combination that get an amount In example All the combinations with the numbers to get a pizza of 5 kg. option 1 5Kg, Option 2 3K...
Categories: DBA Blogs

HOW TO VALIDATE DATA IN UNIQUE_ID FROM TABLES?

Tom Kyte - Thu, 2016-08-11 11:06
example: Suppose I have a unique_id created from 4 parts i.e (college_id - dept_id -student_id-subject_id). So there must be 4-5 tables also respectively. First, i have to extract separate parts from unique_id like college_id or dept_id and then hav...
Categories: DBA Blogs

Nested set comparison subqueries using a GROUP BY clause

Tom Kyte - Thu, 2016-08-11 11:06
I have an instructor(ID,name,dept_name,salary) schema and need to run a query to " Find the maximum salary across all departments of the total salaries of each department" The queries (i) and (ii) below give me the same (correct) result, but t...
Categories: DBA Blogs

Set Default Connection to PDB in Oracle 12c

Tom Kyte - Thu, 2016-08-11 11:06
Hi Tom and Team, I am using a Oracle 12c and i have created a PDB,( say PDB_1.), a USER (say USER_1) and a table (say TABLE_1) When i query to USER_1.TABLE_1 from a java application it is not running due to the default session is CDB. If i run ...
Categories: DBA Blogs

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

Categories: DBA Blogs

converting row to column efficiently

Tom Kyte - Wed, 2016-08-10 16:46
Hi, I am trying to migrate the data from 1 table to another another. My current table like below: create table Employee( emp_id NUMBER NOT NULL, emp_setup_type VARCHAR2(10) NOT NULL, emp_col_1 NUMBER DEFAULT 0, emp_col_2 NUMBER DE...
Categories: DBA Blogs

DBMS_REDEFINITION and copying indexes

Tom Kyte - Wed, 2016-08-10 16:46
Hi Tom, I have a scenario where I need to refresh a partitioned table from another database. The existing process copies the data over a db link and creates a temporary partitioned table. Then we exchange the partitions with the target table and at ...
Categories: DBA Blogs

how can we load the data in to that table without performing truncate operation

Tom Kyte - Wed, 2016-08-10 16:46
Hi , I have a table which contains 70000 records ,how can we load the data in to that table without performing truncate operation. Can we do using merge command to do the update and insert,we need to update,insert and delete .The table should not...
Categories: DBA Blogs

I'm now learning PL/SQL for develop Oracle ERP

Tom Kyte - Wed, 2016-08-10 16:46
Hi,Tom As a fresh graduate to be an programmer with Oracle ERP, I'm now learning PL/SQL. I've read lots of pages about it, and I've practiced how to use PL/SQL to create a report for Oracle ERP. But I've searched on the internet and cannot find ...
Categories: DBA Blogs

Query regarding join order in ANSI

Tom Kyte - Wed, 2016-08-10 16:46
I wrote below two queries, one written using ANSI syntax and other using conventional syntax. While writing these, I expected them to be logically equivalent and return same number of records (whatever that may be). When I actually ran these queries,...
Categories: DBA Blogs

After import/export rowdependecies is lost

Tom Kyte - Wed, 2016-08-10 16:46
Hello, We did dupm and load of our schema using impdp. And few months later we noticed that all tables that in original database where created with rowdependencies in new database are created without without rowdependencies. 1. Is this possib...
Categories: DBA Blogs

When does uncommited data rollback from data file?

Tom Kyte - Tue, 2016-08-09 22:26
Dear Team, DBWR write flush data from db buffer cache at the time ? Checkpoint occurs ? Dirty buffers reach threshold ? There are no free buffers ? Timeout occurs ? Tablespace OFFLINE ? Tablespace READ ONLY ? Table DROP or TRUNCATE ...
Categories: DBA Blogs

Create a database trigger (Dynamic) on a table

Tom Kyte - Tue, 2016-08-09 22:26
Hi Tom, I have a requirement where I have to create a table level trigger dynamically. Here are more details : A user will be provided an option to choose the columns in a table XYZ which will be tracked for any changes - inserting, updati...
Categories: DBA Blogs

excessive log generation

Tom Kyte - Tue, 2016-08-09 22:26
hi - we have a 11.2.0.4 database in production that we saw was generating too many logs even when the database is dormant. We initially thought it was due to rman backups but now we are seeing that if we issue one log switch it is causing too many lo...
Categories: DBA Blogs

SMTP ACL gets dropped ocassionally

Tom Kyte - Tue, 2016-08-09 22:26
Hi, I have a schema which uses the ACL like below to send mails.The ACL gets dropped ocassionally and application using teh schema is unable to send the mails.We need to manually add the ACL again and everything gets back to normal.Can anyone expe...
Categories: DBA Blogs

Query Mysql database from oracle

Tom Kyte - Tue, 2016-08-09 22:26
Hi!, I am trying to query a mysql database from oracle. I have configured everything and everything is working fine but when i query any table of mysql database from oracle, i dont get the columns having number data type. In hs file i have follwing ...
Categories: DBA Blogs

ORACLE Query to return First Row for a DataSet

Tom Kyte - Tue, 2016-08-09 22:26
I'm writing a query which would return first row of a set of grouped data. I tried using OVER PARTITION BY clause, but somehow I'm not getting the desired result : select row_number() OVER(PARTITION BY leafv , value_group , l1d ,l2d ,l3d ,l4d ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs