Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 28 min 51 sec ago

Grant Access on DB_Link to other User

Mon, 2016-07-11 23:06
I need to give access to other user on current DB_LINK, can you provide command ? Current Link: sittest Grant to User: Z9XBIQ1_DW Regards Chaudhry
Categories: DBA Blogs

Forcing a query to timeout

Mon, 2016-07-11 23:06
I have several PL/SQL stored procedure reports that monitor various aspects of our database environment (ASM, tablespace, backups, alerts, ...). These all have the same framework, loop thru the entries in a table, using a db_link, query the remote sy...
Categories: DBA Blogs

Network-based IMPDP with local SCN?

Mon, 2016-07-11 23:06
Hi, I found a procedure for a network based impdp. The procedure sets the actual (flashback) scn of the target system (see below). That?s a mistake, isn?t it? What will impdp do with this invalid scn? Simply ignore it? Cheers, Markus ...
Categories: DBA Blogs

Question on multiple DML in FORALL

Mon, 2016-07-11 04:46
Hi, I have below question on FORALL 1) <code> forall 1 .. l_var.count delete tab1 where id=l_var(i); forall 1 .. l_var.count delete tab2 where id=l_var(i); forall 1 .. l_var.count delete tab3 where id=l_var(i); </code> 2)...
Categories: DBA Blogs

Error when import large data from 11.2.0.3 into 11.2.0.4 Oracle Exadata

Mon, 2016-07-11 04:46
Hi, We hit this and no clue how it suddenly happen. Issue cronology as below (using Toad version 10.2, 10.5 and 11). 1) Export out from Exadata DB version 11.2.0.3 table A with 4million records - ended successfully. 2) Import into Exadata DB ...
Categories: DBA Blogs

Adding subpartitions

Mon, 2016-07-11 04:46
Hello Sir I have a table partitioned on RANGE (daily) (interval partitioning) - there are about 300 partitions . Now I want to add a sub-partition to each of these based on a value in a different column. Should I come up with a script with 300 A...
Categories: DBA Blogs

Distinct Values from Multidimensional Collection

Mon, 2016-07-11 04:46
Hi Tom, Is there a way to retrieve Unique records from a multidimensional collection? DISTINCT and MULTISET operators seem to work on collections only when they have 1 field. I have the below <b>TYPE TYP_TABLE_REC IS RECORD ( SCHEMA_NA...
Categories: DBA Blogs

Best Way to Design a Table

Mon, 2016-07-11 04:46
I've to design Professional Tax table for Payroll(INDIA) , For every state it has its own prof tax according to their gross salary Consider One state Say MAHARASHTRA IF PR-GROSS < 2500.00 TAX= 30.00 IF PR-GROSS < 3500.00 TAX= 60.00 IF PR...
Categories: DBA Blogs

oracle SQL — Replace comma separated string with another set of values

Mon, 2016-07-11 04:46
I have the following table: <code>B_ID I_ID R_ID W00001 1234 1235,1237 B00001 1235 1236,1235 T00001 1236 1235,1235,1235 X00001 1237 1234,1236,1238 M00001 1238 1238</code> I need output like below using sql <code>B_ID I_ID New...
Categories: DBA Blogs

Clob and minus - alternatives for Diff Deltas

Mon, 2016-07-11 04:46
Hello Great Asktom Team, We can't do a minus query with a clob column. So what are the alternatives. I have ETL process which finds deltas of table based on minus. Can't do that with a column having data type clob So how to do it in pure...
Categories: DBA Blogs

Table creation script

Mon, 2016-07-11 04:46
Hai Tom sir...I have doubt. 1)How can i create this table.see below. RED GREEN YELLOW AAA 1 2 3 BBB 4 5 6 CCC 7 8 9 Please create this one.. and more question only... 2)How can in write a quer...
Categories: DBA Blogs

RFS not starting for standby

Mon, 2016-07-11 04:46
HI, what could be the reason for RFS not starting at standby. anything specific I have to look for. DR:- SQL> select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby; SQL> INST_ID PROCESS STATUS ...
Categories: DBA Blogs

SQL questions for improving skills

Sun, 2016-07-10 10:26
Hi Tom, My question is little weird. Where can i find good SQL questions (scenarios) for practice. I tried google but did not find good list. Can you please suggest any website or book/magazine? Thanks, DP
Categories: DBA Blogs

How to register a "service" with a listener without using local_listener

Sun, 2016-07-10 10:26
Hello I create a service like so: exec dbms_service.create_service('MYSERVICE','MYSERVICE'); exec dbms_service.start_service('MYSERVICE'); Then, I add the service in the listener.ora sid_list parameter like so: SID_LIST_LISTENER = (SI...
Categories: DBA Blogs

Errors during iPod touch iOS update

Fri, 2016-07-08 21:46
My IPod touch iOS update keeps saying that an error has accured and when i tap on settings it asks me to update it! I'm trying to update to iOS 8.14. Please help!!
Categories: DBA Blogs

Revert 12.1.0.2.0 CDB to stand alone non-CDB with same product version

Fri, 2016-07-08 21:46
Hello Team, We have installed Oracle 12c version 12.1.0.2.0 to be pluggable. However, many scripts would require the stand alone non pluggable mode of oracle. Can you please guide to convert CDB to non-CDB without downgrading to older versions. ...
Categories: DBA Blogs

datapump export - ORA-39126 / ORA-12838

Fri, 2016-07-08 21:46
HTP0#>expdp system/***** full=y DIRECTORY=data_pump_dir4bos dumpfile=EZTAG_UiAT.dmp logfile=myfullexp.log job_name=eztag_uat_ex5 Export: Release 11.2.0.4.0 - Production on Fri Jul 8 06:46:18 2016 Copyright (c) 1982, 2011, Oracle and/or its affi...
Categories: DBA Blogs

How to get database name in oracle db node js

Fri, 2016-07-08 21:46
<b>How to get database name in oracle db in node js without query using connection object</b> <code> var oracledb = require('oracledb'); oracledb.getConnection( { user: 'c##root', password: 'root', connectString: ...
Categories: DBA Blogs

How to load a xlsx file in to a table

Fri, 2016-07-08 21:46
I have a xlsx file ,and i am using sql loader in toad to load in to table.I also have created control file for the same. the file content is given below: ID Name Employee name Designation Status 0000700 John,Sam John sam (000070) Sr.Developer, ...
Categories: DBA Blogs

GATHER_TABLE_STATS difference between 11g and 12c (DBMS_STATS.flush_database_monitoring_info)

Fri, 2016-07-08 03:26
Hi Tom, So we are looking to be refreshing stats when the stats become stale while a lot of DML is happening on a table, and were testing how to handle this. FLUSH_DATABASE_MONITORING_INFO is the right procedure as it updates the dictionary. We ...
Categories: DBA Blogs

Pages