Oracle date related SQL queries

select TO_DATE(sysdate, 'dd/mm/yyyy') from dual

select TO_CHAR(sysdate, 'MM/DD/YYYY') from dual;

select sysdate from dual;

select * from myTable where DTTMCREATED BETWEEN TO_DATE('09/20/2012','mm/dd/yyyy') AND TO_DATE('09/21/2012','mm/dd/yyyy') 

/* as it's case insensitive in sql, so MM==mm
Oracle use mi to replace mm, and hh24 to represent use 24 hours format. */

Oracle: managing password

After a while, oracle user password will expire:

login in as system

select username, account_status from dba_users;
alter user HAIBO_JPMC identified by my_pass;

Uninstalling Oracle 10g Manually from Windows XP

From Link

Oracle's installation utility has a nasty habit of leaving a lot of items behind. I wrote this up to document what I've found you need to do to uninstall Oracle 10g manually. This is specific to 10g and Windows XP. Removing 9i is very similar if I remember right, but who's using 9i anymore? ;-)

The first thing you should do, is go ahead and run the installation tool to do an uninstall. It probably will leave some things behind, but it's worth running. Then go through this list and remove anything it missed. This list is detailed enough though, that I believe even if you did not run the uninstallation tool, this would fully uninstall Oracle.

The most commonly missed item is to make sure you remove everything from the GAC because the uninstaller doesn't appear to. I get to that later, but if you wanted to know the largest source of uninstallation problems, it's libraries remaining in the GAC.

After running the supplied Oracle uninstallation utility (which may or may not do some or all of the following):

Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with 'Oracle' and stop them.

Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService

Note that the services control panel will still show the old services until you reboot.

Delete the Oracle home directory
C:\Oracle

Delete the Oracle Program Files directory:
C:\Program Files\Oracle

Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.

Remove Oracle refereces from the path. To edit your path go to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.

Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\

There, now your system is Oracle free. If you are installing a new instance of Oracle (and not just an additional DB) I recommend you do this before any new Oracle installation.
Note: I used several other websites and searches as references when developing these steps, but I was unable to find any steps that covered all of the items for Oracle 10g so I assembled them here.

Oracle Tablesapce

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.

There are three types of tablespaces in Oracle:
Permanent tablespaces
Undo tablespaces
temporary tablespaces

Oracle XE "Database" vs "Scheme"

This is from: link

In Oracle, a database means a collection of data files and control files stored on disk. You can have only one database with Oracle XE. A database is mounted by a Oracle instance, which means all the background daemons and programs that you can see with ps.

You probably want a schema, which is what other DBMS products (e.g. MySQL) often refer to as a "database". IIRC there was no limit on the number of schemas in Oracle XE. You can create those from the APEX UI that comes with XE, or from command line (with CREATE USER -- again, confusingly, a userand schema are largely synonymous in this case).

I recommend reading the Concepts guide from Oracle documentation, most of this basic stuff (e.g. how things are called in Oracle-lingo) is covered there.

Oracle XE 10g problem

I used to use Oracle 10g enterprise version as my database, but I found it takes too much memory, so I switched to XE, it's working great until tonight.

When I start my weblogic server, I keep getting such error:

ORA-12519: TNS:no appropriate servicehandler found.

I have absolutely no idea what's this about, so google it, and found this: link

The fix is easy, and it works:

alter system set processes=150 scope=spfile;

Then restart your database.

If you run into an error like this: "ORA-01653: unable to extend table HAIBO_COMR.FORM by 8 in tablespace SYSTEM"

alter database datafile 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF' RESIZE 1000M;
Make sure the highlighted part is the one in your system.

Other useful queries:

SELECT * FROM dba_data_files
purge recyclebin;
SELECT  tablespace_name,  SUM(bytes/1024/1024) FROM  dba_segments GROUP BY  tablespace_name;
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;