Tom Kyte

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

Security

Thu, 2016-06-30 12:46
how to shadow user's tables' structure for other users. I have two users (schemas) - USER1 and USER2. USER1 has roles CONNECT and RESOURCE, but if i run 'DESC USER2.TABLE_NAME' i can show user2's table's structure. How can I forbid to show table'...
Categories: DBA Blogs

Avoid triggers

Thu, 2016-06-30 12:46
Hi Tom I have table A which goes through various DML and owned by different team, we want to capture a similar data with all the DML that's happening. we want to avoid triggers as this is owned by different team , is there a solution to capture wit...
Categories: DBA Blogs

querying data from backup file

Thu, 2016-06-30 12:46
Hi Tom / Connor, As a regular DBA Activity, everyday Production DBs are scheduled for backup and the backup files will be stored in archive location for a retention period depending on business requirement. Now my query is, I just want to pic...
Categories: DBA Blogs

how to store output of dynamic sql into a cursor.

Thu, 2016-06-30 12:46
Hi tom, I want to know how to declare a cursor which contains the data obtained by dynamically executing a query,where the query was taken from another cursor. CURSOR c1 IS SELECT QRY from QUERY_MASTER WHERE Q_ID IN (1,2); where QRY(an SQ...
Categories: DBA Blogs

How to export xml data ( clob column ) to a csv file

Thu, 2016-06-30 12:46
Hi, Let say, I have a table employee ( id number, name varchar2, notes clob ). I use dbms_util to write the data to a file as csv. The id (number) and name( Varchar2) columns are writing to csv file as expected. But when it comes to notes (clo...
Categories: DBA Blogs

Title on Report.

Thu, 2016-06-30 12:46
Hello All- May be another set of eyes!! How do I get the title to show on the spool output report file? Below is my report lay out. Thanks! <code> set heading on SPOOL U:\...\tt.txt ttitle left " Test report " skip 1 COLUMN...
Categories: DBA Blogs

covert string data to columns

Thu, 2016-06-30 12:46
Hi, I need some help to converting the string data from string to coulmns wise, i have the data in one column like : #1#5#3#2#5#7 #5#3#5#4#3#2 #1#7#5#3#6 #3#5#2#1#5 #6#5#4#7#1#2 and i need to each numberic should be columns wise like this...
Categories: DBA Blogs

Transpose the data in oracle

Wed, 2016-06-29 18:26
Hi Tom, I have a table (TABLE1): CREATE TABLE TABLE1 ( STDNO NUMBER(10), SUBJ VARCHAR2(5), MARKS NUMBER(10) ); with this data in it: INSERT INTO TABLE1 VALUES (1, 'A', 50); INSERT INTO TABLE1 VALUES (1, '...
Categories: DBA Blogs

Transposing the data from rows to columns and to split the columns into new line during particular data intervals

Wed, 2016-06-29 18:26
Hi Tom, Can you suggest me the solution for transposing the data from rows to columns, and to split the data into new column during particular interval. Here is the sample tables and data that i am working on and the desired output as well: ...
Categories: DBA Blogs

shell script to send two outputs to single file

Wed, 2016-06-29 00:06
Hi Tom, I need to prepare one shell script, which contains start and end time of the script(which is another shell script) and one database query. In this script, i am good to execute the db query and send output to file.In the same way,i want t...
Categories: DBA Blogs

Convert rows to columns

Wed, 2016-06-29 00:06
Hi Tom, I have a table: CREATE TABLE TEACHER (NAME VARCHAR(20), PRO VARCHAR(20)); with this data in it: INSERT INTO TEACHER VALUES ('A','PROFESSOR'); INSERT INTO TEACHER VALUES ('B','DOCTOR'); INSERT INTO TEACHER VALUES ('C','ENGINEER');...
Categories: DBA Blogs

Moving Constraints from one table to another

Wed, 2016-06-29 00:06
Hi asktom team, I want to convert a NON - PARTITIONED table to LIST - partitioned table. So to do that I am doing the following: FOR EXAMPLE take ORDERS table: -Renaming the TABLE ORDERS to ORDERS_OLD. -Creating a table ORDERS same as ORDER...
Categories: DBA Blogs

What are possible meanings of error OCCI ORA-12560: TNS:protocol adapter error?

Wed, 2016-06-29 00:06
Hi, The Oracle error messages are not always very descriptive. Searching the internet I found couple reasons why we can get ORA-12560 error: 1) Oracle database instance is not started 2) ORACLE_HOME is not set (for version 10) What are other ...
Categories: DBA Blogs

Peformance issue with user_tab_partitions query

Wed, 2016-06-29 00:06
Hi, We have the below query which is taking lot of time....while reading the partition name for the given high_value of table table_1. Here P_SEQ is the input value that we are passing through a procedure to this query. SELECT to_char(x.parti...
Categories: DBA Blogs

Inserting a BLOB file into oracle using a pl/sql script

Wed, 2016-06-29 00:06
I cannot for the life of me figure out how to successfully insert a file into a blob column in a DB, using a PL/SQL script. Should be able to reuse this script indefinitely, preferably, with several files at the same time, but as long as I can do on...
Categories: DBA Blogs

getting error for NOLOGGING NOCACHE

Wed, 2016-06-29 00:06
Hi there, We have recently migrated our database from Oracle 9i to Oracle 11g. Now,One of our procedure is having query to create external table of oracle loader type. It also include NOLOGGING NOCACHE. This procedure was working properly in Oracl...
Categories: DBA Blogs

Can Oracle 8i client connect to Oracle 12c server?

Wed, 2016-06-29 00:06
Can Oracle 8i client connect to Oracle 12c server? If yes, please anybody can help me that what are the changes does in Oracle 8i client's file and Oracle 12c Server's file...
Categories: DBA Blogs

Generating Dates between two date ranges

Wed, 2016-06-29 00:06
<code>Hi Tom , I need to write a query which will give me a list of dates between two date ranges (start date and end date) . For Example if my start date is 12/01/2003 and end Date is 12/31/2003. i need to generate my output as Date ---...
Categories: DBA Blogs

Refresh Materialized view when there is records in the source table

Wed, 2016-06-29 00:06
Hi , Need help to refresh one materialize view only when there is records in the table. say example . create materialize view MABCD as select field1,field2,field3 from abcdef ; my requirement is if abcdef have records greater than zer...
Categories: DBA Blogs

Different between FGA and virtual private database?

Tue, 2016-06-28 05:46
I am very confused with some terms such as difference between Fine Grained Auditing and virtual private database?
Categories: DBA Blogs

Pages