DBA Blogs

data updaion using bulk collect

Tom Kyte - Wed, 2017-02-15 05:06
Hi Tom , I am bit new to the PLSQL world and trying to write a block that will read clob data and update(only first 4000 byte as we are still using 11g) to a varchar column . The block is ready but when I execute with some good number(close to 70 K) ...
Categories: DBA Blogs

SQLLDR fails on data specified as filler in control file

Tom Kyte - Wed, 2017-02-15 05:06
I have a sqlldr control file that looks like this name char(255) enclosed by '"', contactemail char(255) enclosed by '"', rawtext1 filler enclosed by '"', rawtext2 filler enclosed by '"', auditdate ...
Categories: DBA Blogs

ORA-02089: COMMIT is not allowed in a subordinate session

Tom Kyte - Wed, 2017-02-15 05:06
Hello Tom, I got an issue above. I have some stored procedure which enables/disables some Oracle queue, procedure is being called from java, java method is wrapped by '@Transactional' annotation. It opens the main transaction, where my db procedu...
Categories: DBA Blogs

Servers for Pratice

Tom Kyte - Wed, 2017-02-15 05:06
Hi, Just visited the site livesql.oracle.com, look like it is very good environment for developers to improve their skills in Coding. I am learning RAC & Apps DBA concepts.Is there any environment to implement Apps DBA & RAC concepts like liv...
Categories: DBA Blogs

Facing error while fetching partition_position from all_tab_partitions

Tom Kyte - Wed, 2017-02-15 05:06
Hi Tom Am facing trouble with a simple query. Basically, my ultimate goal here is to purge the data older than 2 years for few tables(which I have mentioned in purge_table_list table). I have renamed all the partitions to the format of last 7 ch...
Categories: DBA Blogs

Dropping all objects under a schema

Tom Kyte - Wed, 2017-02-15 05:06
Hi Connor, The below query will drop all the table which are present in the current user A's schema (normal scenario login using user 'A'). <code> select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in...
Categories: DBA Blogs

Subtract 2 timestamps to find the difference in hours

Tom Kyte - Wed, 2017-02-15 05:06
Hi, How can I find out the time difference in hours between 2 timestamp with timezone I tried below one <code> select extract(HOUR FROM diff) from ( select '2/14/2017 3:39:15.097484 PM +00:00' - '2/14/2017 1:39:15.097484 PM +00:00' as dif...
Categories: DBA Blogs

Links for 2017-02-14 [del.icio.us]

Categories: DBA Blogs

Log Buffer #504: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2017-02-14 19:15

This edition of Log Buffer covers Oracle, Microsoft SQL Server, and MySQL.

Oracle:

Data Pump or Data Pain Part02 – tablespace/user

Monday Spotlight: It’s About Security – Oracle Exadata SL6

0001_specify_date_format_mask.md copy

OGG Custom Adapters: How to include a unique identifier for every record in custom adapter?

One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join” that makes certain types of merge join much more efficient.

Microsoft SQL Server:

How to import data to Azure SQL Data Warehouse using SSIS

Using Index-Organized Tables in Oracle

Query Performance Tuning – A Methodical Approach

SQL Server Temporal Tables: How-To Recipes

Fix Commvault Backup Failures After an Upgrade

MySQL:

MySQL may return results in non-deterministic order with ‘order by’

Shinguz: MySQL and MariaDB authentication against pam_unix

MySQL in the Cloud – Pros and Cons of Amazon RDS

With 500+ VividCortex Users, Shopify Eliminates High Latency Queries From Redis and MySQL

An Introduction to MariaDB’s Data at Rest Encryption (DARE)

Categories: DBA Blogs

How to add years to a timestamp without losing fractions

Tom Kyte - Tue, 2017-02-14 10:46
Hi, I have a column of type <code>TIMESTAMP</code> t in which I safe timestamps with 6 digit precision e.g. 03-FEB-17 04.45.01.909000 PM Due to the way they have ben added to the table they are missing two things: 1. The time zone information ...
Categories: DBA Blogs

Flashback Archive, create_temp_history_table and import_history

Tom Kyte - Tue, 2017-02-14 10:46
<b>Hi Tom, I'm trying to understand how it works Flashback Archive. I have problem with commands create_temp_history_table and import_history This is the script: </b> create table prueba_fb2 (c_cadena varchar2(1000), n_numero numbe...
Categories: DBA Blogs

How to find long running queries in a database

Tom Kyte - Tue, 2017-02-14 10:46
Hi, I'm trying to get the long running queries for my database. I need the start and end time of those queries also. These long running query is causing issues in releases. Please help me to find out the sql text and start & end time of the q...
Categories: DBA Blogs

Shell Script to load data

Tom Kyte - Tue, 2017-02-14 10:46
<code>Hi, we work on oracle apps and using 12c Data Base in Back end. We Are using a concurrent program(shell script) to load data into staging table from front End.in data file we have data like below col 1 Col2 Col3 1025-1'...
Categories: DBA Blogs

Check valid SQL syntax without executing and when objects are not present in the schema

Tom Kyte - Tue, 2017-02-14 10:46
Can I validate SQL syntax without having the objects created in database? I have a free text where users are supposed to enter valid SQL text. I have to determine which are valid and which are invalid entries. create table metadata (data_src_name...
Categories: DBA Blogs

Relation Between two tables

Tom Kyte - Tue, 2017-02-14 10:46
Hi, I have two tables emp,grades without any constraints like primary and Foreign keys.My requirement is to give the grades to employees based on the grades given for salary range in grades tables. i written a Join query for that but getting p...
Categories: DBA Blogs

How to get PIN CODE from latitude and longitude in PLSQL Procedure.

Tom Kyte - Tue, 2017-02-14 10:46
Hi Tom, I have latitude and longitude for a particular location or person as my input parameter and I want to get PIN CODE for the same in PLSQL proc. Could you please help Thanks, Rajnish Malik
Categories: DBA Blogs

Histograms

Tom Kyte - Tue, 2017-02-14 10:46
Hello Tom, One question, in 10g and 11g versions, what are the impacts in a busy system of manually gathering the whole database statistics without updating histograms? I mean the impacts from the following points of view: 1- How long it wil...
Categories: DBA Blogs

How to print row value involved in merge statement

Tom Kyte - Mon, 2017-02-13 16:26
Hi, By using dbms_output.put_line( sql%rowcount) we can get the number of rows affected by merge statement. Is it possible to print value of those rows during merge using PL/sql? thanks, Amiya.
Categories: DBA Blogs

Iinconsistent size of tablespace.

Tom Kyte - Mon, 2017-02-13 16:26
Hello, I have two tables: 1st - the empty table t1 (partitioned by date) on the server s1, 2nd - the source table t2 (partitioned by date) on the server s2. Both tables t1 and t2 has the same structure. I want to copy t1 from t2 partition ...
Categories: DBA Blogs

Deduplication of rows with null values

Tom Kyte - Mon, 2017-02-13 16:26
<code>drop table test purge; create table test (ssn int, address varchar2(10), tax_id varchar2(10), identify int); insert into test values(100, '','', null); insert into test values(111, '','', null); insert into test values(111, 'xyz','', nu...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs