Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 7 hours 19 min ago

Shrink partition table

Tue, 2017-12-12 15:06
Hi, The shrink table doesn't defragments the partition tables. executed : alter table test_table_1 shrink space and checked with below query wasted space before and after but the values are identical. <code>select table_name,rou...
Categories: DBA Blogs

Imdp xml schema date format issue ORA-01858

Tue, 2017-12-12 15:06
Hi, I exported a schema from Oracle 11.2.0.1 and trying to import it in Oracle 12c. My Oracle schema contains xml schema and xmltype columns in a table. My xml fragment is <code><Tag0> <Tag1> <Tag2 Id="10202" date1="2017-11-15T13:36:34.00000...
Categories: DBA Blogs

Removal of Archive Files when using OS to backup offline DB

Tue, 2017-12-12 15:06
I have been thrown in at the deep end and given an Oracle DB to look after. I have no prior experience of Oracle so everything I am doing is new and a massive learning curve. The current DB data set is approx 400GB and we are working with the appl...
Categories: DBA Blogs

how to count the number of records in a file

Fri, 2017-12-08 16:06
How do i get to count the number of records in a flat file(.csv/.dat) including the header and trailer records.The code was return in a plsql using utl_file.Can you suggest me the best method to implement the logic in Plsql.And the trailer record sho...
Categories: DBA Blogs

advise given by segment advisor 12c

Fri, 2017-12-08 16:06
Hi Tom, My question is, Does <b>segment advisor in 12cR1</b>, advise the use of <b>online table redefination for tables</b> in both d<b>ictionary managed</b> and <b>locally managed tablespace</b>? In addition, What about the <b>use of segm...
Categories: DBA Blogs

Dynamic fields in External File

Fri, 2017-12-08 16:06
Sorry if its a weird requirement. I have an input feed coming in like the following(only two rows per file).. col1, col2, col3,Attribute_1,Attribute_2,Attribute_3,......,Attribute_n col1, col2, col3,Value_1,Value_2,Value_3,......,Value_n ...
Categories: DBA Blogs

Reconstruct sale from audit commands

Fri, 2017-12-08 16:06
We are running on 11.2.0.4, 12.1 and 12.2. SQL> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail ...
Categories: DBA Blogs

Reg: Elapsed time

Fri, 2017-12-08 16:06
Hello Team, Quite confused with elapsed time definition. elapsed time is equal to db time. but DBtime= cpu time + wait time. so its correct or not. somewhere i found elapsed time is nothing but wall time. plz comment on above statem...
Categories: DBA Blogs

Data Guard vs Active Data Guard

Thu, 2017-12-07 21:46
Hi, Please could i get some answers for the following: What is the difference between DG and ADG ? What are the benefits of DG ? What are the benefits of ADG ? What are the risks / constraints of using a DR environment for near real ti...
Categories: DBA Blogs

Grouping Id

Thu, 2017-12-07 01:06
Hi Tom I never really understood the usage of GROUPING_ID function in OTN.I heard it avoids using multiple GROUPING functions.Can you please illustrate with a small example Thanks
Categories: DBA Blogs

Dynamic Sql to get the value of the column which is formed by concatenating two strings.

Mon, 2017-12-04 18:06
Hi Team, I have a query like this I will get the column name at run time something like IF conditions 1 then Column A. IF conditions 2 then Column B. IF conditions 3 then Column C. IF conditions 4 then Column D. Once i get to know whi...
Categories: DBA Blogs

I dropped a table in oracle but when i saw the indexes became like 'BIN$...' i rebuild them the state is still VALID

Sun, 2017-12-03 23:46
I dropped the table with cascade option, after importing the table the indexes are there with BIN$... name and the state is VALID. Are they really valid i try to rebuild its rebuilding but name is not changing.
Categories: DBA Blogs

Forms Builder won't connect to database - ORA 28040

Fri, 2017-12-01 16:46
I've installed an Oracle 12.2 database on my PC running Windows 10. That seems to be fine and I can connect to the database and the sample HR database is all there. I have then installed the Oracle Developer Suite v10.1.2. That installation appears t...
Categories: DBA Blogs

Service and module

Fri, 2017-12-01 16:46
Dear Team, Hope doing well..!!!! please help me to understand difference between service and module in oracle database. Whats use of module? Thanks Pradeep
Categories: DBA Blogs

Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 unable to connect to Oracle DB 10 Release 2

Thu, 2017-11-30 22:26
I have installed Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 on a server to allow our SIEM to connect to our customer's Oracle DB 10G R2 for monitoring purpose. However, I'm still getting error saying "ORA-12541: TNS:no listener" when I test...
Categories: DBA Blogs

pragma autonomous_transaction; and database links

Thu, 2017-11-30 22:26
I have a package of functions that return data from a SqlServer database through a link. Usually the results are just displayed in optional fields on a web page or client program. They take the form of: <code> function get_info(ar_key number) ...
Categories: DBA Blogs

How can i read a csv file

Wed, 2017-11-29 09:46
Hi TOM :) Resourse: 1) i have a table that was milions of records of the clients 2) and i have a CSV with only 1,200 clients 3) i don't have permitions to create a table. Problem: how can i read from the CSV to join with the pr...
Categories: DBA Blogs

UTL_FILE or EXTERNAL Table operation on files in Application Server

Tue, 2017-11-28 15:26
Hi Tom, We are running Oracle EBS R12.2.6. We have application server and database server hosted on two separate physical servers. They are not having any shared file locations. We are receiving the inbound files from a third party system in...
Categories: DBA Blogs

Validate constraint recursively run a SELECT with an "ordered" hint

Tue, 2017-11-28 15:26
I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate ? a...
Categories: DBA Blogs

Oracle Live SQL

Mon, 2017-11-27 21:06
Hello, Is there a way I could run explain plan on Oracle Live SQL? Apparently, it gives an error that PLAN_TABLE doesn't exist. I ran utlxplan.sql in my session but still could not execute statement SELECT * FROM TABLE(DBMS_XPLAN.display) W...
Categories: DBA Blogs

Pages