Friday, February 24, 2012

Oracle Database Fun and Games

I've been working with a number of Oracle databases lately and decide to post some of the tips and tricks that I found:

SQL Developer
Error when you connect to database:
"Status : Failure -Test failed: ORA-00604: error occurred at recursive SQL level 1ORA-01882: timezone region not found"
Solution: Edit the sqldeveloper.conf file and add the following line:
AddVMOption -Duser.timezone=GMT

Error, running out of RAM
Solution: Edit the sqldeveloper.bat file and change the following parameters as required:
-Xmx640M -Xms128M
You can also set the -XX:MaxPermSize setting in the sqldeveloper.conf file, but I have not had to increase this yet.

Start Database Script
Old but good article here -> http://tldp.org/HOWTO/Oracle-7-HOWTO-6.html
Basically you need to do this:
Start the listner: lsnrctl start listener
Start Enterprise Manager: emctl start dbconsole
Set the SID of the database: export ORACLE_SID=report
Connect to sqlplus: sqlsplus "sys as sysdba"
Run the startup command: startup

Get list of tables
select tname from tab

Delete all tables
select 'drop table '||table_name||' cascade constraints;' from user_tables; ref

Stop it from prompting you for input values due to the & character
set define off

Cool tool to create new database
dbca (ssh -X into the server and run this command)

No comments:

Followers