DBA Blogs

using max function

Tom Kyte - Mon, 2018-03-19 16:06
Hi, Look at here..... Find highest salary in each department without using MAX function. Note:- Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions a...
Categories: DBA Blogs

sqlloader versus external table.

Tom Kyte - Mon, 2018-03-19 16:06
hi Tom, Is there any advantage of sql* Loader over the new 9i feature external table. Which of them is faster. sql* loader direct path load or direct path load from the external table. thanx in advance Nikhil India
Categories: DBA Blogs

i face prob with the returned cursor from function

Tom Kyte - Mon, 2018-03-19 16:06
<code>i face prob with the returned cursor from function ,i created function to return cursor ,and in the script ,i use the function to give me the cursor after that in loop i try to print all data that is in the cursor . but it didnt work , and ...
Categories: DBA Blogs

sqlloader decimal fields

Tom Kyte - Mon, 2018-03-19 16:06
Hi Sir, hope your doing good.... How to load decimal numbers from file to table using sql loader?..Please give an practically one example...Thank You.
Categories: DBA Blogs

Generate PDF documents from PL/SQL

Tom Kyte - Mon, 2018-03-19 16:06
Hi Tom, How would you recommend generating PDF documents from within "plain" PL/SQL? Thank you, Dieter
Categories: DBA Blogs

Week start and end date for a given date

Tom Kyte - Thu, 2018-03-15 02:06
Hi Tom, I want the sql to get the start of the week date and end of the week date for a given date and my week starts from saturday and ends with friday. Example, if the given date is 03/mar/2018 then start date is - 03/mar/2018 and end date...
Categories: DBA Blogs

Converting Long Data Type to CLOB got ORA-06502

Tom Kyte - Thu, 2018-03-15 02:06
Hi, I am running following pl/sql code to extract image data from the table. I am able to extract data which has length less than 32768 how ever I get <b>ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind</b> when the length is...
Categories: DBA Blogs

PL SQL Type as bind variable in Dynamic FORALL

Tom Kyte - Wed, 2018-03-14 07:46
SQL Version: <code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - ...
Categories: DBA Blogs

Invalid number

Tom Kyte - Tue, 2018-03-13 13:26
I have a table that has two VARCHAR2 columns. create table A ( char_col varchar2(200), char_col2 varchar2(200), num1 number(30)); insert into A values ('1,23,234','1,23,234','550'); insert into A values ('44,23,234','5,130','1280');...
Categories: DBA Blogs

dump table selective rows to csv

Tom Kyte - Tue, 2018-03-13 13:26
Greetings I'm trying to include search criteria in below script's query ('select * from ' || p_tname ;) to fetch only those rows/employees which were hired during last 24 hours. I have tried below -- SQL> create or replace procedure dump_tab...
Categories: DBA Blogs

changing default tablespace of LOB in a partitioned table

Tom Kyte - Tue, 2018-03-13 13:26
Hi, I want to move a Partitioned table having LOB to a different tablespace, I have moved all data,indexes and partitions, and set the default tablespace for table and indexes every thing is moved/changed except for the default tablespace of ...
Categories: DBA Blogs

Merge statement is throwing ORA-08006 error

Tom Kyte - Tue, 2018-03-13 13:26
We have a ETL job with merge statement. It was working fine earlier. There was a node eviction on last week and due to that the DB was bounced. After bouncing the DB the procedure started failing with ORA-08006 error. Same error is appearing when we ...
Categories: DBA Blogs

Rollback to save-point rollbacks everything

Tom Kyte - Tue, 2018-03-13 13:26
Hi, In the below example, "rollback to b;" rollbacks everything instead of clearing only emp3 entries. ---------- <code>create table emp1(empno number,ename char(4),sal number); create table emp2(empno number,ename char(4),sal number); create ...
Categories: DBA Blogs

character conversion for numbers

Tom Kyte - Mon, 2018-03-12 19:06
Dear Sir, Is there any way to convert number from english to other language like Bangla. e.g = 1 will be show when convert ? is possible.............
Categories: DBA Blogs

Improve Data Clustering on Multiple Columns Concurrently (Two Suns in the Sunset)

Richard Foote - Mon, 2018-03-12 03:30
I’ve had a couple of recent discussions around clustering and how if you attempt to improve the clustering of a table based on a column, you thereby ruin the current clustering that might exist for a different column. The common wisdom being you can only order the data one way and if you change the […]
Categories: DBA Blogs

DB Cloning

Tom Kyte - Mon, 2018-03-12 00:46
Hi Team, Would you please share the steps for cloning 11.2.0.4 DB and making it as DR server2, we already have one DR server. now they are planning to make one more DR server. I want to understand what are the ways to clone DB with their in d...
Categories: DBA Blogs

Execute multiple ALTER statements inside EXECUTE IMMEDIATE

Tom Kyte - Mon, 2018-03-12 00:46
Hi Team My use case is to store table metadata - PK, Unique, FK constraints and Indexes DDLs in a temp table. Perform some operation on table, and at the end restore the stored metadata on the table. When executing the PL/SQL block, i receiv...
Categories: DBA Blogs

Database setup for DR

Tom Kyte - Mon, 2018-03-12 00:46
Hi Team, Our client are planning to create new DR server. Would you please give the step by step procedure to step new DR on IBM AIX server 6.1 64bit. Regards, Surendar Patel
Categories: DBA Blogs

Monitoring TRUNCAT TABLE - WHEN and WHAT SESSION ?

Tom Kyte - Mon, 2018-03-12 00:46
Hi We are facing some strange issues in our test environments where TRUNCATE table was happening and we could NOT find when it was truncated and what session is causing it ? Is there any way to monitor the truncate ? Such as Who ? When ? Sessi...
Categories: DBA Blogs

Shell function returning content of existing directory along with error code

Tom Kyte - Mon, 2018-03-12 00:46
Hi, We are using following code to find out ORA- errors. But not sure why it is returning directory contents on function error. db_scn() { OUTPUT=$(sqlplus -s /as sysdba << EOT whenever SQLERROR EXIT 11 select ccurrent_scn from v$database...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs