DBA Blogs

Optimizing minus funtion

Tom Kyte - Wed, 2017-07-19 10:26
Hi , I have a table with almost 500+ million records in it. i need to select distinct record based on the change code indicator , by which i mean , in my table for a given set business set key columns there can be many records with "A" , "U" an...
Categories: DBA Blogs

Display custom error using pragma

Tom Kyte - Wed, 2017-07-19 10:26
Hi, I am trying to associate a PL/SQL exception with a number and then use that to show my own error message to the caller. I keep on getting the default error message rather than my own error message. I have searched over the last couple of days ...
Categories: DBA Blogs

sql commands show different output from 11g to 12c

Tom Kyte - Wed, 2017-07-19 10:26
sql commands show different output from 11g to 12c Ex: 12.1.0.2 --------- SQL> sho parameter nls_date_format NAME TYPE ------------------------------------ -------------------------------- VALUE ----------...
Categories: DBA Blogs

Partner Webcast - Oracle Bare Metal for Oracle EMEA Partners

Oracle Bare Metal Cloud Services combine the elasticity and utility of public cloud with the granular control, security, and predictability of on-premises infrastructure to deliver high-performance,...

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

How to use DBMS_PARALLEL_EXECUTE to chunk over DB link

Tom Kyte - Tue, 2017-07-18 16:06
Hi Team, Can you please have a look at below: I tried copying table data from One DB to Other over DB link using DBMS_PARALLEL_EXECUTE. And it works as expected. Challenge I am facing here is: I have a table TB_TXN created on both Oracle D...
Categories: DBA Blogs

What type of role/grant that should be given to allow to grant permissions on other schemas

Tom Kyte - Tue, 2017-07-18 16:06
Hi, I have user, with name of "grant_schema". This user should be able to run below command. SQL> grant select, insert, update, delete on claims.table_name to CLAIMS_USER; What type of role/grant that should be give to my "grant_schema" to b...
Categories: DBA Blogs

Error in converting a date from one timezone to other

Tom Kyte - Tue, 2017-07-18 16:06
I want to convert a date in some time zone to date in the db timezone. Now for some reason i get UNKNOWN as the timezone_region when i run the query - <code>select extract(timezone_region from systimestamp) from dual;</code> But I was able to get...
Categories: DBA Blogs

Synchronize two databases

Tom Kyte - Tue, 2017-07-18 16:06
How do I sync two Oracle databases, so that every transaction from A is copied to B using database link. How do I go about it. We do not want to use GOLDENGATE or DATAGUARD for this. PLEASE HELP!!!
Categories: DBA Blogs

Upgrading 11.2.0.2 to 11.2.0.3 using import/export

Tom Kyte - Mon, 2017-07-17 21:46
I have I have a Solaris 11.1 system with Oracle 11.2.0.2 with grid/ASM and databases. I need to migrate the databases to another Solaris 11.1 system that will have Oracle 11.2.0.3 software. 1. Can I export the ENTIRE 11.2.0.2 database and import ...
Categories: DBA Blogs

Mirror Schema to another database

Tom Kyte - Mon, 2017-07-17 21:46
I have schema access to database A and have DBA access to database B. I have the same schema in both databases. How do I synchronize every changes that occur in A to happen in B on a daily basis.
Categories: DBA Blogs

How to Create Missing Records with Analytical Functions

Tom Kyte - Fri, 2017-07-14 20:26
Hi AskTom Team, I am having some trouble figuring out a query to do the following: I have a staging table populated by an external system. This table stores information about how much an item sold during a day. If an item hasn't sold anything d...
Categories: DBA Blogs

PostgreSQL Inheritance in Oracle

Tom Kyte - Fri, 2017-07-14 20:26
Hi Tom, How do I implement inheritance this way in oracle? create table requests(); create table requests_new() inherits (requests); create table requests_old() inherits (requests); I should be able to query the child tables independentl...
Categories: DBA Blogs

help

Tom Kyte - Fri, 2017-07-14 20:26
For which constraint does the Oracle Server implicitly create a unique index? a)PRIMARY KEY b)NOT NULL c)FOREIGN KEY d)CHECK Which tablespace can NOT be recovered with the database open? a)USERS b)TOOLS c)DATA d)SYSTEM ...
Categories: DBA Blogs

Query Returns via SQL*Plus - but not via ODP.net Driver

Tom Kyte - Fri, 2017-07-14 20:26
We have a database with some partitioned tables (main table by value, the children by reference). We have a query that includes a function call in the where clause. <code>Select bunch_of_columns, package.function(parameter) as column18 from ta...
Categories: DBA Blogs

SQL*Loader save filename into table column

Tom Kyte - Fri, 2017-07-14 20:26
I need to import different csv-files into 1 table. I need to use the sqlloader.(Oracle Version 12.1.0.2) This is my control-file: <code>load data append into table SAMP_TABLE fields terminated by ',' OPTIONALLY ENCLOSED BY '"' AND '"' traili...
Categories: DBA Blogs

SqlPlus spool extract getting truncated

Tom Kyte - Fri, 2017-07-14 20:26
Hi, I have a script to extract data from the Oracle EBS database. I have provided an extract of the script below for reference: ---------------------------------------------------------------------- <code>set feedback off set trims on set li...
Categories: DBA Blogs

How to get rowid by chain row

Tom Kyte - Fri, 2017-07-14 20:26
Hi Tom, I create a table, like this create table pan0.t (c1 int, c2 varchar2(4000), c3 varchar2(4000), c4 varchar2(4000)); and then, insert data to generate a chain row insert into pan0.t values(7, rpad('`',4000,'`'), rpad('!',4000,...
Categories: DBA Blogs

How to group timestamps in 10 minute buckets and aggregate

Tom Kyte - Fri, 2017-07-14 20:26
I've been grappling with this problem for almost a week. Other threads on this site have gotten me close but not all the way. I created a table and populated it using sqlldr. The queries for that are below this question. I want dateValidFrom ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs