Category Archives: DB

Inserting data into table from CSV file in MySql

I wanted to have my data persisted in database for analysis, i had the data in a CSV file and i wanted a quick way to get the data into a table in MySql table. I found this simple but effective solution in StackOverflow

I have a sample CSV file below for reference.

The command below can be used to insert data into Mysql table from command line client.

load data local infile ‘~/tmp/example.csv’ into table rental fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\n’ (col1,col2,col3,col4,col5,col6,col7,col8,col9

—– CSV File Format —–

“data in quotes”,935,78750,1,1,128,data,708,http://www.bitsbusiness.com/

Advertisements

Leave a comment

Filed under DB

Installing MySql Server on Windows

If you havn’t done so already, download the free MySQL Server Community Edition — you can download the software from the MySQL website. Be sure to download the Windows (x86) version (way at the bottom of the page), which includes a Windows Installer. Save the file on your Windows Desktop.

For next steps click here

Leave a comment

Filed under JDBC

Installing MySql on Solaris

http://sunsolarisadmin.blogspot.com/2007/03/install-mysql-in-solaris.html

Leave a comment

Filed under DB

ORACLE : who is connected to My Oracle Instance

spy You have a DB you call your own, but you cant seem to drop some users eventhough you have stopped all services you were running which might have connected to your Oracle Instnace,

Well they (Apps or users) can’t hide any longer … The Oracle’s best Spy is on your side – here is the SQL which can bring them to the open. ( you need to be the SysDBA to do this though )

1. set linesize 30000 – to make sure everything appears in a single line

2. SQL> select username, sid, serial#, status, osuser, process, machine, terminal, program, logon_time from v$session where username is not null and username not in (‘SYSTEM’,’SYS’,’DBSNMP’);

– All the agents are displayed in a table with their USERNAME, SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM
you have to decide if you will shoot (force kill ) or negotiate ( stop the services in the machines they are running in )

1 Comment

Filed under DB, ORACLE

ORACLE : How to DROP a User who is connected – forcefully

cannon The ideal way for all development shops to work is to have each developer have his own database, but as you all know life is not that easy on us!.

So you want to update the schema, which requires to drop all users and you see that your script is complaining that some of the users (in oracle users are the schemas) are still conneted and cannot be dropped. This   would normally mean some one is running an application/service/App server, which is holding some connections to your database.

You have been nice to all, mailed the group, pinged some of your fellow developers, but there is a rogue application holding on to a user/schema. What would you do.  wait for ever or take matter into your hands

Here is some Mojo and fire power for you to boot/kill those users force fully ( ofcourse you need to have SysDba access to the database, otherwise you are out of luck )

  • find the sessions for the users/schema , as a DBA use : select sid,serial# from v$session where username = '<user_schema>'
  • Then kill them with : alter system kill session '<sid>,<serial#>'

A query that produces ready-built kill-statements could be

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'

This will return one kill statement per session for that user – something like:

alter system kill session '375,64855';

alter system kill session '346,53146';

  • Now Drop the user : drop user <user_schema_name> cascade;

That’s it – The User is vaporized !



8 Comments

Filed under DB, ORACLE

Memcache – Notes

1. Memcache is a in memory cache used by many web sites to boost performance.

2. memcache url –

3. memcache java clinet – http://dustin.github.com/java-memcached-client/

4. How to start memcache : ~ $ /perflab/memcached-1.4.1/memcached -p <portnumber> -u <username> -m <memory capacity in GB>

5. To connect to memcache and issue commands :

a. $ telnet 127.0.0.1 <port number> ( to connect to memcache from the host machine )

b. stats ( gives stats )/ flush_all ( flushes all data in memcache )

6. Simple Memcache Client code snippet  : This creates a client which can be used to call memcache commands and get and set info

MemcachedClient c= new MemcachedClient(new InetSocketAddress(“perftest10.qa.linkedin.com”, 10151));

references : http://lzone.de/articles/memcached.htm

Leave a comment

Filed under DB, j2ee, Java

Oracle – Notes

Issue :  message ora -01109 database not open.

Soln :
C:\> set ORACLE_SID=<YOUR SID>
C:\> sqlplus “/ as sysdba”
SQL> select status from v$instance;

the result should be MOUNTED. If so, try

SQL> alter database open;

if this works the DB will be available, otherwise you’ll get an error, from which we can try to understand something….


Issue :
Starting a Oracle Database – Troubleshooting

1. startup mount  – this command will start the db server and also mount the database, but you will see an ERROR ORA-01102: cannot mount database in exclusive mode
if there is another instance in the same server which has been started in EXCLUSIVE mode and not PARALLEL mode.

to fix this

1. find the instances which are cerated ( may not be running ) in the machine on which oracle is running ( ps -aef | grep pmon )
2. su -oracle and kill the instances
3. Start the Oracle Server again, by command – startup mount.
4. At this point the database should be open, if not use the cmd – SQL> alter database open;

Reference :
http://www.dba-oracle.com/t_ora_01102_cannot_mount_database_in_exclusive_mode.htm
http://www.dba-oracle.com/concepts/starting_database.htm

Issue : Not able to drop a user  ( in this case username is fuse ) – ORA-01940

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘fuse’ and p.addr (+) = s.paddr;
select job from dba_jobs where log_user=’fuse’
select queue_table, qid from dba_queues where owner=’fuse’;
select  apply_name from  dba_apply where queue_owner=’fuse’;
select  propagation_name from  dba_propagation where  destination_queue_owner=’fuse’;
or
destination_queue_owner=’fuse’;

Issue : Started Oracle DB and checked DB status is Mounted/OPEN – But when i “telnet 127.0.0.01 1521” I still can’t connect.

The issues is the DB is up but the Oracle LISTENER is not up. Follow the steps below to Troubleshoot

1. Make sure the DB is up and OPEN ( A. select status from v$instance  – make sure its up and OPEN  B. ps -aef | grep pmon – Make sure there is one process running )

2. check the tnsnames.ora under /export/home/oracle/products/10.2.0/network/admin and make sure DB and LISTENER entry is pointing to the correct DB host, in this case the DB server /host is on the same machine.

3. Now Start the TNS LINTENER by using the command as a Super user – lsnrctl start

Issue : How to find the schemas in a database

Soln : SELECT username FROM all_users ORDER BY username;

Issue : How To find the current databse
Soln : SELECT * FROM global_name;

Issue : How to run a SQL command on Oracle and exit out of sqlplus after the command is executed ( useflu for automation )

Soln :

echo exit | sqlplus user/pass@connect @scriptname   - more here

Issue : How to Shutdown Oracle DB 
Soln : 
sql> shutdown NORMAL|IMMEDIATE|ABORT ( Abort kills everything immediately , the fastest but not the safest way - more here

Leave a comment

Filed under DB, ORACLE