oracle database find version

When you open an SR with Oracle Support you must answer a lot of embarassing questions, like "Database/Version".
Open a connection with SQLDeveloper and run "SELECT * FROM V$VERSION;"
This is what you get:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

DB: Clustered Indexes or Index-Organized Tables

Reading
http://www.javacodegeeks.com/2012/02/20-database-design-best-practices.html

I stumbled on this statement:

"Use indexes for frequently used queries on big tables. Analyser tools can be used to determine where indexes will be defined. For queries retrieving a range of rows, clustered indexes are usually better. For point queries, non-clustered indexes are usually better."

What is a "clustered index"?

http://en.wikipedia.org/wiki/Database_index#Clustered
"They are known as "index organized tables" under Oracle database."

Pretty good description here:

http://www.orafaq.com/wiki/Index-organized_table

More here:

http://stackoverflow.com/questions/3382939/when-should-i-use-oracles-index-organized-table-or-when-shouldnt-i

"In practice, index organized tables are most likely to be reference data, code look-up affairs."

Oracle ACE

It's a long way to become a Oracle ACE. See here the program:
http://www.oracle.com/technetwork/community/oracle-ace/index.html


A good way to become Oracle ACE is to accumulate plenty of points on the Oracle Forums, as indicated here:

whenever you help someone on the forum, he may grant you 5 or 10 points (many don't :o( ).

I am celebrating today my JOURNEYMANship.... a first step hopefully towards Oracle ACEship, perhaps in 5 years time!

WebLogic Datasource: ServiceName vs SID

if your DB is setup to use a Service Name rather than a SID, the URL syntax is slightly different:


jdbc:oracle:thin:@1.2.3.4:1521/SERVICENAME

instead of the usual

jdbc:oracle:thin:@1.2.3.4:1521:SERVICENAME


If you make a mistake, you will get the dreaded

Connection test failed.
Message icon - Error Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor 
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:135)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:203)
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:489)
oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:439)
oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:640)
oracle.jdbc.driver.T4CConnection.(T4CConnection.java:205)
oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:554)
oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:388)
oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:645)
oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:265)
oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:141)
com.bea.console.utils.jdbc.JDBCUtils.testConnection(JDBCUtils.java:550)
com.bea.console.actions.jdbc.datasources.createjdbcdatasource.CreateJDBCDataSource.testConnectionConfiguration(CreateJDBCDataSource.java:458)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.apache.beehive.netui.pageflow.FlowController.invokeActionMethod(FlowController.java:870)
...

Getting started with Oracle XE (oracle database express edition)

Once you install the product, you have a database created with name XE.


Go to C:\oraclexe\app\oracle\product\10.2.0\server\BIN and run sqlplus
connect as system (pw admin presumably)

You can do: connect system as sysdba
and startup and shutdown the DB.

these commands will give you the installed DB:

select * from global_name;
select name from v$database;

Oracle create database in Linux

so you have installed your oracle 10g in $ORACLE_HOME, as a sysdba Unix user.

Next steps:

edit ~/.bash_profile and add:
export ORACLE_HOME=/opt/oracle/product/10g
export ORACLE_SID=MYSID


start listener:
cd $ORACLE_HOME/bin
lsnrctl start

cp $ORACLE_HOME/dbs/init.ora $ORACLE_HOME/dbs/initMYSID.ora

edit initMYSID.ora and set db_name=MYSID


and set


start database

$ORACLE_HOME/bin/sqlplus / as sysdba

startup nomount

and create database:

create database MYSID;

alter user sys identified by oracle;

alter user system identified by oracle;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

if the second fails, then drop the database, create it again,
run
select * from V$DATAFILE
see which one is the .dbf file for SYSTEM,
then run
ALTER DATABASE DATATILE 'full.path.to/somefile.dbf' resize 500M;

then do also
create user YOURAPPLICATIONUSER;
grant create session to YOURAPPLICATIONUSER;
GRANT ALL PRIVILEGES to YOURAPPLICATIONUSER;

also run /opt/oracle/product/10g/sqlplus/admin/pupbld.sql as system (pw manager)


if anything goes wrong, use "startup UPGRADE" and rerun the scripts

Remember also how to drop a database:

shutdown abort;
startup mount exclusive restrict;
drop database;


For ALSB, sqlplus and login as your application user and run
@/opt/bea/alsb/3.0/alsb_3.0/dbscripts/oracle/reporting_runtime.sql


In the morning, to startup your stuff:
cd $ORACLE_HOME/bin
./lsnrctl start
./sqlplus / as sysdba
startup
exit

then do
./lsnrctl status
to verify that your instance has been dynamically registered (no need to edit listener.ora)




see also:
http://www.tek-tips.com/faqs.cfm?fid=6435

http://www.dba-oracle.com/oracle_create_database.htm