Updating a table rows whose rows are same except for a few columns. For example all the rows are same except for insert datetime or status like columns.
UPDATE schema1.table1 a
SET col4 = ‘ABC’
WHERE a.col3 <
ANY (SELECT b.col3
FROM schema1.table1 b
WHERE a.col1 = b.col1
and a.col2=b.col2);
You can see update results by:
SELECT col1,col2,col3,col4 from schema1.table1 where col1||col2 in (
SELECT col1||col2
FROM schema1.table1
group by col1,col2
having count(*)>1)
For log running batch operation even containing DDL operations, module deployments, import and for other stuff.
ALTER SESSION ENABLE RESUMABLE; enables the resumable property.
http://www.oracle-base.com/articles/9i/ResumableSpaceAllocation.php
NLS_LANG parameter in the registry causes this.
You could get your current environment settings from the sql*Plus by:
SQL> SELECT USERENV(‘language’) FROM DUAL;
USERENV(‘LANGUAGE’)
—————————————————-
TURKISH_TURKEY.TR8MSWIN1254
SQL> desc
Kullan²m: DESCRIBE [schema.]object[@db_link]
SQL>
You could change NLS_LANG parameter in the registry:
from
TURKISH_TURKEY.TR8MSWIN1254
to
AMERICAN_TURKEY.TR8MSWIN1254
You must relogin from sqlplus, in order to see the change.
SQL> SELECT USERENV(‘language’) FROM DUAL;
USERENV(‘LANGUAGE’)
—————————————————-
AMERICAN_TURKEY.TR8MSWIN1254
SQL> desc
Usage: DESCRIBE [schema.]object[@db_link]
SQL>
You need a quick look whether a db parameter needs bouncing the db.
issys_modifiable column in v$parameter view tells this. False dos not mean you cannot change it with ALTER SYSTEM, means a bouncing. I think this parameter name must be something like “issys_changeable_in_session” 
issys_modifiable column name is easier to understand, if it is TRUE you can change in session with ALTER SESSION , FALSE then you cannot.
For sample parameters, below SQL Statement which produced this data:
SELECT name, issys_modifiable
FROM v$parameter
WHERE name in (’session_cached_cursors’,'object_cache_optimal_size’,'db_cache_size’);
| NAME | ISSYS_MODIFIABLE | MY COMMENT |
| db_cache_size | IMMEDIATE | No bouncing needed. |
| object_cache_optimal_size | DEFERRED | A new connection needed. |
| session_cached_cursors | FALSE | Bouncing needed. |
For more detailed information:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#REFRN30176
Problem:
This strange error is occurred when I tried to execute a procedure.
ORA-20300: Exception in ABC_table when calling xxxproc. SQLERRM:
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at “myuser.myproc”, line 113
Cause:
No real cause.
Solution:
I execute it by making a new fresh connection and it worked successfully.
I had a space porblem on data disk and after analysing I found fragmentation on a 128 GB tablespace.
The database objects index, tables, even partitioned and unparititoned are occupying only 10 G of tablespace.
In order to gain space I used Quest Space Manager with Live Reorg.
First created a newer tablespace and move the objects.
It is also interesting that some of partitions of tables were on different tablespaces. In order to make th reorg to work, I need to move the partitions residing on other tablespaces to new tablespace.
Thus the granularity of moving is table by table not partitions. I filtered the objects by giving the OWNER and OBJECT_NAME (in my case it was table name) to Quest Space Manager.
It took around 10 hours and the result is impressive, now I have no objects in the tablespace.
Eventually, I dropped the former tablespace with INCLUDING CONTENTS and DATAFILES option.
Surprise! HPUX OS did not show my earned disk space.
[oracle@oppa08]:/home/oracle> bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg01/lvoradata01 568590336 528623358 37469105 93% /export/oradata01
After googling, I found that I am not alone:)
It was because Oracle was using those datafiles, and they are open, which OS can not give the space back. Bouncing the database worked.
[oracle@oppa08]:/home/oracle> bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg01/lvoradata01 568590336 405011080 153355613 73% /export/oradata01
Problem:
“ORA-22992: cannot use LOB locators selected from remote tables” error message
while selecting from table over database link having blob data type column.
Cause:
LOB columns referenced over dblink together with other columns.
From Oracle documentation:
“In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list”.
Solution:
In order to select into or CTAS from remote table containing LOB field (CLOB in my case), you must insert without selecting LOB column and then making an update only selecting LOB column.
update myschema.mytable t1
set lob_column=(select lob_column from myschema.mytable@REMOTE_LIVE t2
where t1.id=t2.id);
It is also strange that; below statement is not working:
select lob_column from myschema.mytable@REMOTE_LIVE
Thanks to
| user628981 |
http://forums.oracle.com/forums/thread.jspa?messageID=1252302
Problem:
Going to Preferences > Preferred Credentials > Host > Set Credentials and
entering the correct username and password also results in:
Error Connection to as user failed: ERROR: Wrong
password for user
Cause:
The Windows operating system user does not have the required ‘Log on as a
batch job’ system privilege.
The ‘Log on as a batch job’ system privilege is required by the user whose
credentials are used for the preferred credentials for a Host. The user
needs to be able to log on as a batch job in order to execute jobs such as
backup jobs in the background.
Solution:
To implement the solution, please execute the following steps:
1. Go to Start > Control Panel > Administrative Tools > Local Security Policy
2. Open Security Settings > Local Security Policies > User Rights Assignment
3. Double-click on Log on as a batch job from the list of privileges in
the right pane.
4. Click on Add User or Group … and enter the name of the OS user whose
credentials are being used as the preferred credentials for the host.
5. Click ‘OK’ twice.
6. Retry the preferred credentials test for the user at Preferences >
Preferred Credentials > Host > Set Credentials
Excerpt from: http://www.lazydba.com/oracle/0__122689.html
Thank you Juliano for the solution!
To enable all the roles that the user has GRANTed when s/he logs on.
“I do not want to see bug icon near my PL/SQL procedures.”
If this is the case, you must go to session menu and select “toggle compiling with debug”. This changes the default behaviour of compiling. And you will not see bug near your procedures in the LHS. Also note that following excerpts from TOAD help topics:
Debugger Overview
“Compiling with debug provides the information Toad needs to navigate the code using the Debugger.”
Minimum Oracle Database Requirements
“For all databases, you must have the Oracle Probe API v2.0 or later installed in order to debug PL/SQL using Toad.”
