How to change Ubuntu server 9.10 root password

By default there is no root password. By issuing the below command:
derya@ubuntu:~$ sudo passwd root
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated succesfully
derya@ubuntu:~$

Self update a table which have almost same column values

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)

Enable Resumable

For long 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

Getting non-english messages in your Sql*Plus

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>

Does a database parameter value change need bouncing?

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

ORA-06508: PL/SQL: could not find program unit being called

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.

Tablespace defragmentation by reorg

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

ORA-22992 How to deal with LOB locators in remote tables

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

Host credentials when using DBControl 10gR2 on WINDOWS

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!

What is the purpose for DEFAULT ROLE ALL

To enable all the roles that the user has GRANTed when s/he logs on.

Toad compile with debug

"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."

When did Optimizer use index FFS in queries having Max(datetime)?

Assume a Table MYTABLEA1, having date type column for example datetime:

select max(datetime) from MYCHEMA.MYTABLEA1;

Row count: 1384

 

Plan
SELECT STATEMENT CHOOSE
	2 SORT AGGREGATE
		1 TABLE ACCESS FULL MYCHEMA.MYTABLEA1

 

select max(datetime) from MYCHEMA.MYTABLEA2;

Row count: 8139221

Plan
SELECT STATEMENT CHOOSE Cost: 442,815 Bytes: 8 Cardinality: 1
	3 SORT AGGREGATE Bytes: 8 Cardinality: 1
		2 PARTITION RANGE ALL Partition #: 2 Partitions accessed #1 - #26
			1 INDEX FULL SCAN (MIN/MAX) NON-UNIQUE MYCHEMA.MYTABLEA2 Cost: 442,815 Bytes: 2.491.053.600 Cardinality: 311.381.700 Partition #: 2 Partitions accessed #1 - #26

 

I quoted folowing from Julian.

The indexed columns must have a NOT NULL constraint

The table does not need to be analysed

http://www.juliandyke.com/Optimisation/Operations/IndexFullScanMinMax.html