Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 4 weeks 1 day ago

Find if event spanning several dates happened via SQL

Thu, 2019-10-31 09:47
Hi Tom, I have data like below <code> event_flag event_date 1 date1 1 date2 0 date3 1 date4 0 date5 0 date6 1 date7 1 date8 1 date9 ...
Categories: DBA Blogs

pragma autonomous_transaction within procedure before creating synonyms

Thu, 2019-10-31 09:47
Hi Tom, I have created a stored procedure with in oracle package which creates list of synonyms depending upon the change of dblink server name. I need to execute this procedure to create/replace synonym pointing to another dblink server. My quest...
Categories: DBA Blogs

Undo Tablespaces.

Thu, 2019-10-31 09:47
Hi Tom, Waiting to ask u this question. What is a Undo Tablespace in 9i. Is this similar to Rollback Segments. What are NonStandard Block sizes Why that non-Standarad. Why am i not able to create a RS on a Locally Managed Automatically Si...
Categories: DBA Blogs

How to count no of records in table without count?

Thu, 2019-10-31 09:47
2)How to count no of records in table without count? ---Actually,this question asked when i had attended an interview in Dell company.I don't know why they people are asked these type of questions,but i said an answer like in my own way. --->...
Categories: DBA Blogs

Oracle database software client 18c 32 bit win 7

Sat, 2019-10-26 15:46
I need to install Oracle database software client 18c on win 7 32 bit I visit Oracle website. They only offer instant client without setup file.i don't know configure on win 7.especially about odbc technology. I want to open odbc windows 32 bit an...
Categories: DBA Blogs

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [1384], [1270], [2885], [], [], [], [], [], [], []

Sat, 2019-10-26 15:46
------------------------------------- Oracle Database 11.2.0.1.0 Archive Log Mode RMAN Backup is performed daily, with backup control file and backup archive log file 1 time to disk. ------------------------------------ Hi Ask Tom t...
Categories: DBA Blogs

Last run date of a function on a menu

Sat, 2019-10-26 15:46
Is it possible extract the last run time of a function run on a menu from the Oracle database?
Categories: DBA Blogs

SQL to return 12 months of this year

Sat, 2019-10-26 15:46
select (to_char(add_months (sysdate,level-10),'Month')) as Month ,to_char(TRUNC(add_months(sysdate,level-10),'month'),'mm/dd/yyyy') as firstdayofthemonth ,to_char(last_day(add_months(sysdate,level-10)),'mm/dd/yyyy') as lastdayofmonth from dual ...
Categories: DBA Blogs

Oracle - validate date format (yyyy-mm-ddThh24:mi:ssZ) in XML against XSD

Sat, 2019-10-26 15:46
<b>Oracle version:</b> The result of this query select * from v$version; is: <code>Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Li...
Categories: DBA Blogs

REGR_R2 returns a value of 1 when y column contains a constant value (e.g., all rows have value of 0.0038).

Sat, 2019-10-26 15:46
I was searching a large emissions database for pairs of columns with correlated values. My first effort identified a large number of sets with an R2 value of 1 based on blindly applying the REGR_R2 function to discrete pairs of numeric data columns....
Categories: DBA Blogs

Multiple SQL statements in a single Execute Immediate

Wed, 2019-10-23 15:46
What is the exact syntax to be able to execute multiple sql statements from within a single execute immediate statement.
Categories: DBA Blogs

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

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

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

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

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

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

FAR SYNC FAILOVER

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

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

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

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

Pages