DBA Blogs

end loop commit; on the same line gives no error

Tom Kyte - Tue, 2017-10-17 13:06
Hello, I created a simple table. <code>create table test(t number);</code> I tried to insert values in it. I created o simple PLSQL block to insert values. <code>declare i integer; begin for me in 1..2 loop insert into test(t) va...
Categories: DBA Blogs

Parallel DML Update without any parallel hint or parallel degree

Tom Kyte - Mon, 2017-10-16 18:46
Hello All, In our production environment, one DML update is running in parallel and which is sporadic in nature. Please note the session is enabled for parallel dml. Find below one pseudo code of that update statement. --------------- <co...
Categories: DBA Blogs

Getting ORA-01861 literal does not match format string error in stored procedure

Tom Kyte - Mon, 2017-10-16 18:46
Hi, We have a requirement to get the date in 'yyyy-mm-dd' format but unable to do using below procedure, <code>create table emp_test (emp_id number, customer_due_date date) / create table dep_test (due_days number) / <Insert>, insert...
Categories: DBA Blogs

How to replace sqlloader control file with external table

Tom Kyte - Mon, 2017-10-16 18:46
Hello, I have a flat file structured like this: HH00001 0000000048JZFRV288SNGAA814 DD11111111 desc1 000000000000000000012 100002 DD02324234 desc2 000001200000000000000 00 300012 100002 DD5453...
Categories: DBA Blogs

Move lob index to a different tablespace

Tom Kyte - Mon, 2017-10-16 18:46
Hello, I have a table which contains one CLOB column and there is an Index on that CLOB Column , this table is on TEST tablespace. My question is: how to move CLOB Index only on TEST_IDX tablespace without moving the column along with the Index??? ...
Categories: DBA Blogs

dbms_ldap.bind_s vs dbms_ldap.simple_bind_s

Tom Kyte - Mon, 2017-10-16 18:46
Hello Tom, I will need your help in understanding the difference between DBMS_LDAP.BIND_S and DBMS_LDAP.SIMPLE_BIND_S ( DB version is 11.1.0.2 ). Thank you!
Categories: DBA Blogs

Memory_target or sga_target+pga_aggregate_target for dw databases.

Tom Kyte - Mon, 2017-10-16 18:46
Hi I don't know but I have been told, that "12c db creation wizard doesn't even allow AMM (MEMORY_* parameters) anymore for databases with more memory than... 4 G if memory serves me" So what is the current wisdom for big (?) dw databases ? S...
Categories: DBA Blogs

IN (vs) EXISTS and NOT IN (vs) NOT EXISTS

Tom Kyte - Mon, 2017-10-16 18:46
Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). Regard...
Categories: DBA Blogs

Setting up Optach environment variable

Learn DB Concepts with me... - Mon, 2017-10-16 17:22

Setting up Optach environment variable :

For Korn / Bourne shell:

% export PATH=$PATH:$ORACLE_HOME/OPatch
For C Shell:
% setenv PATH $PATH:$ORACLE_HOME/OPatch
Categories: DBA Blogs

When does RMAN validate archived redo logs are available during a duplicate?

Tom Kyte - Mon, 2017-10-16 00:26
Does RMAN validate all archived redo logs are available at the beginning of a duplication or once it starts restoring the logs after the backup data files have been restored?
Categories: DBA Blogs

Submitting Questions

Tom Kyte - Sat, 2017-10-14 11:46
Hi Tom, I've been consulting your site for years, and its almost always a source of answers to just about all Oracle issues I encounter. On rare occasions I might encounter an issue not previously discussed, but there's an uncertainty how long I w...
Categories: DBA Blogs

After install of 12,2 on Windows 10, I can only connect as SYS

Tom Kyte - Fri, 2017-10-13 17:26
I have installed Oracle 12c Release 2 twice as a non-container database. The issue I have run into both times is that I keep getting "ORA-01017: invalid username/password; logon denied" every time I try connecting as any user other than SYS. See exam...
Categories: DBA Blogs

How to Create backup table from one schema to another schema every time the live schema table is inserted or deleted or updated

Tom Kyte - Fri, 2017-10-13 17:26
Hi, I want to know How to create a backup table that will backup data from one schema to another schema table every time the live table is inserted or updated or deleted. Awaiting for your response. Thanks, Ershad
Categories: DBA Blogs

Hierarchical query with count of leave attributes

Tom Kyte - Fri, 2017-10-13 17:26
Hello Experts. I want to calculate the sum of the count of the leaves' attributes in a hierarchical query <code> create table hq_test (parent_id NUMBER, child_id NUMBER); INSERT INTO hq_test (parent_id, child_id) VALUES (25,26); INSERT INTO hq...
Categories: DBA Blogs

Execute immediate and passing clob as a parameter is not working.

Tom Kyte - Thu, 2017-10-12 23:06
I have the following procedure <code> create or replace PROCEDURE bscs_rateplan_sync ( tmcode_list IN CLOB, project_id IN VARCHAR2, requestname IN VARCHAR2, v_errm OUT VARCHAR2 ) ...
Categories: DBA Blogs

Differrent Character set and NLS

Tom Kyte - Thu, 2017-10-12 23:06
Hi TOM, Some question on Character set and NLS. 1) What is the different between US7ASCII and WE8ISO8859P1 character set and NLS? 2) Is there any problem for the DB with two diferrent character sets to interact, - trigger - dblink - e...
Categories: DBA Blogs

Auditing Operating system program name

Tom Kyte - Thu, 2017-10-12 23:06
Sir, i am developing a client/server program using VB & Oracle, it is in testing stage. i want to track whether the programmer is doing changes to the database using the application program(VB) or by logging into database(SQLPLUS) for this i us...
Categories: DBA Blogs

How you manage you're work-spaces!!

Tom Kyte - Thu, 2017-10-12 23:06
Greetings Team, I am very curious to know how you actually manage you're work spaces.I mean you always face different kind of questions all day along.How many laptops you use(of course you are using VM). But my question is if you guide someone fo...
Categories: DBA Blogs

SQL * Loader connection errors

Tom Kyte - Thu, 2017-10-12 23:06
To previous answer to MY SQL*Loader question I had asked, you said: Examples of SQL*Loader String <code>sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name con...
Categories: DBA Blogs

ORA-01722: invalid number error in decode

Tom Kyte - Thu, 2017-10-12 04:46
1. select decode('a',1,1,2) from dual --------> O/P Error saying Invalid Number 2. select decode(1,'a',1,2) from dual --------> O/P : 2 what is the difference in the above two cases and also please explain the execution process? Thanks I...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs