Check the Oracle edition

Blatently copied from http://vicker313.wordpress.com/2009/05/16/how-to-check-oracle-10g-is-standard-or-enterprise-edition/

In normal situation, we can check the edition of Oracle 10g using SQLPlus. Just login using SQLPlus, and there will be a connention message on top of the SQL prompt, stating that the Oracle is either Standard or Enterprise.

Or you can also issue the following select statement to get the edition information:
select * from v$version

However if the database is not created using Database Configuration Assistant (DBCA), but using script or SQL statement, the 2 methods stated above might not work. It still will show out the edition detail but not stating that it is either Standard or Enterprise.

In such scenario, we will need to digg into the files. First look for this file (applicable to both linux and windows):
$ORACLE_HOME/inventory/Components21/oracle.server/*/context.xml
Inside this context.xml file, look for parameter s_serverInstallType and check out its value (VAL). If it is EE, means it is Enterprise Edition. While SE means Standard Edition.

Corrupt temporary tablespace symptoms (10g)

When attempting to use TOAD to look at tablespaces as user SYSTEM, I got the following error:
SELECT privileges on the following are required: DBA_DATA_FILES, DBA_FREE_SPACE, V$TEMP_SPACE_HEADER, V$TEMP_EXTENT_POOL, DBA_TEMP_FILES.

Connect as SYS resulted in the same errors.

Alert log show ORA-01187: cannot read from file xxx because it failed verification tests, where file xxx was the temporary tablespace tempfile.

Alert log also showed: ORA-1187 signalled during: alter database backup controlfile to trace ...

Solution turned out to be dropping and recreating the temporary tablespace(s). Obviously, if there is only one temporary tablespace, you'll have to create a interim temporary tablespace as you cannot drop the default tablespace.

Oracle: InstantClient, TNSPING, and PL/SQL Developer

Check out this entry on Jason's Rant about how to get 11gR2 instant client working with TNSPING, and PL/SQL Developer.

http://jasonvogel.blogspot.com/2007/10/oracle-instantclient-tnsping-and-plsql.html

Materialized View owner privilege requirements

To create a materialized view, schema owner needs create table privilege explicitly granted (not through a role) in addition to the create materialized view privilege.

Linux CPIO command for Extracting files

$ cpio -idmv <Filename (No space after the redirection "<" sign)

The "-i" flag is for extract,
the "-d" flag creates directories ahead of time if necessary,
the "-m" flag is to preserve modification times on the restored files, and
the "-v" flag performs the whole operation in verbose mode in the end giving you the total extracted blocks.

Oracle 10g Restricted mode

Before 10g, startup restrict would allow only users with restricted session to connect to the database. With 10g, the listener 'knows' if the database is in restricted mode and will disallow any TNS connects. Even the dba would have to be on the database host and log in as sysdba.

There is a work-around, however. If you add "(UR=A)" to the CONNECT_DATA portion of your connect string definition (MetaLink Doc ID 44120.1).

SQL Server Agent Autostart

SQL Server Agent Service won't Autostart, eventhough when you right click properties and select => General page, the "Auto restart SQL Server if it stops unexpectedly" and "Auto restart SQL Server Agent if it stops unexpectedly" boxes are checked.

Solution - Navigate - Start -> All Programs -> Microsoft SQL Server 2xxx -> Configuration Tools -> SQL Server Configuration Manager

Select "SQL Server Services" Node in the left pane to see the related SQL Server services in the right pane. Right Click on the "SQL Server Agent (INSTANCE)" under "Name" in Right pane and select "Properties". Go to "Service" tab, check and edit the setting against "Start Mode" parameter. This should fix the issue.