DBA Blogs

Can I get a single table where each column consists of a query (each query returning three rows with ids)?

Tom Kyte - Wed, 2019-10-23 15:46
Hi, I have 27 different queries, all of which yield 3 rows max. Something like this: <code> SELECT CUSTOMER_NUMBER FROM customer WHERE name = 'SomeName' order by CUSTOMER_NUMBER fetch first 3 rows only; </code> and <code> SELECT CUSTOM...
Categories: DBA Blogs

Database Event Error Tracking

Tom Kyte - Wed, 2019-10-23 15:46
Hi Guys, I have a schema with multiple Schema objects like Procedures,Functions,Triggers and Packages. While testing via application if any DB error occurs we need to check the log to identify errors. Is there any way to create a single trig...
Categories: DBA Blogs

XML Aggregation

Tom Kyte - Wed, 2019-10-23 15:46
Consider: <code>with data as (select 'MH' initials, to_date('01092019','ddmmyyyy') cal_date, 23 quantity from dual union all select 'MH' initials, to_date('02092019','ddmmyyyy') cal_date, 18 quantity fro...
Categories: DBA Blogs

Remove a hint with DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR failed

Tom Kyte - Wed, 2019-10-23 15:46
Hello Masters, I am testing the two packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR and they work fine : for exemple I can remove an ORDER BY from a SELECT. But, there is always an exception, I have problems with Hints : I want to remove...
Categories: DBA Blogs

Constraining JSON Data

Tom Kyte - Wed, 2019-10-23 15:46
Hello AskTom-Team, is there a possibility to contrain and check JSON data? For example: <code>insert into test(questionnaire_id, var_val, year) '{ "f1": "2571", "f11": "38124", "f31": "332.64", "f4...
Categories: DBA Blogs

How to Convert BMP to JPG: 2 Easy Ways!

VitalSoftTech - Tue, 2019-10-22 08:45

Are you looking to convert BMP to JPG? There are plenty of ways you can do that, through software installed in your laptop or through online BMP to JPG converters. We’ll take a look at some of the way you can make a conversion in this article! But first, let’s take a look at what […]

The post How to Convert BMP to JPG: 2 Easy Ways! appeared first on VitalSoftTech.

Categories: DBA Blogs

FAR SYNC FAILOVER

Tom Kyte - Tue, 2019-10-22 00:46
Dear Sir, In case of an outage on the primary database, the standard failover procedure applies, and after some time primary server available then how it going to sync all 3 server. please help me to understand this. Thanks Pradeep
Categories: DBA Blogs

Oracle query running slow

Tom Kyte - Tue, 2019-10-22 00:46
Hi Team, we have a SQL query which is a source query for the ETL load job, this take around 3 hours to run, could you please help us how we can make it run faster. The row count of the tables involved are as follows. D_PERSON 4618595 ...
Categories: DBA Blogs

Keep pooI

Tom Kyte - Tue, 2019-10-22 00:46
when I assign any of the segment to keep pool , It is nessesary to set appropriate DB_KEEP_CACHE_SIZE as per the sizes of the segment. or it will dynamically set DB_KEEP_CACHE_SIZE?
Categories: DBA Blogs

Limit parallelism

Tom Kyte - Tue, 2019-10-22 00:46
Hi ASKTOM team, I am not very good with parallelism, so have a question about DW database. These are my current settings: <code> parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_li...
Categories: DBA Blogs

Log Files in Oracle External Tables

Tom Kyte - Tue, 2019-10-22 00:46
My External Tables were working fine before i accidentally deleted all the .log and .bad files from the default location. Now I am getting below error ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-...
Categories: DBA Blogs

(Theoretical) Confusion with roles and public synonyms

Tom Kyte - Tue, 2019-10-22 00:46
Hi Tom, <b>Confusion1:- </b> Suppose I have three user accounts in my database: A, B and C. 'A' user has the privilege to create a role. Suppose there is a table named 'employee' in schema 'A' and 'A' issues:- 1. create role GiveAccess; 2...
Categories: DBA Blogs

Stat gather impact on production environment

Tom Kyte - Mon, 2019-10-21 12:45
On OLTP production environment, during huge transaction period, what is an impact if we run the stat gather of used schema for transaction???, It will missed any indexes, and other operation issues???
Categories: DBA Blogs

Best practice for "archiving" legacy tables and their data

Tom Kyte - Mon, 2019-10-21 12:45
Hi, I recently removed the last piece of front-end functionality that relied on a table, and am certain that that table and its data is no longer needed for the application to function. We'll have more similar tables in this situation in the near ...
Categories: DBA Blogs

Fetch across commit

Tom Kyte - Mon, 2019-10-21 12:45
what do you mean by 'Fetch across commit'
Categories: DBA Blogs

Is there a maximum number of schemas that can be included in a datapump par file?

Tom Kyte - Mon, 2019-10-21 12:45
I've been tasked with migrating a very large warehouse database (9TB) from hardware in one data center to new hardware in a different data center. For various reasons, the method I've selected for the migration is datapump. I'm breaking up the data...
Categories: DBA Blogs

Index Rebuild and analyze

Tom Kyte - Mon, 2019-10-21 03:45
Hello Tom , I have a query regarding Index rebuild . what according to you should be time lag between index rebuilds. We are rebuilding indexes every week .but we found it is causing lot of fragmentation. is there any way we could find out whet...
Categories: DBA Blogs

Views of Views

Tom Kyte - Mon, 2019-10-21 03:45
I remember hearing some time ago that creating views based upon other existing views should be avoided as it can often confuse the optimiser and result in full table scans. I expect that this is just another urban myth however I would be intereste...
Categories: DBA Blogs

Inserts with APPEND Hint.

Tom Kyte - Mon, 2019-10-21 03:45
<code>insert /*+ append */ into t select rownum,mod(rownum,5) from all_objects where rownum <=1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- -----...
Categories: DBA Blogs

Kill a session from database procedure

Tom Kyte - Mon, 2019-10-21 03:45
How i can kill a session from a stored database procedure. There is some way to do this?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs