IT Base of Knowledge’s Weblog

August 12, 2008

Delete an Oracle Database

Filed under: Database, Oracle — itbdc @ 10:59 am
$ sqlplus "/ as sysdba"

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

SQL> exit

August 8, 2008

List a column and use it to make an operation with TSQL

Filed under: Database, MsSql — itbdc @ 3:23 pm
DECLARE @tags varchar(200)
DECLARE @K int
SET @K=1

DECLARE MyCursor CURSOR
FOR
   SELECT columns
   FROM table

OPEN MyCursor

-- read the first record
FETCH MyCursor INTO @tags

-- loop
WHILE @@fetch_Status = 0
BEGIN
   INSERT INTO table2 (id, new_tags) VALUES
		(@K, @tags);
   SET @K = @K + 1;
-- read next record
   FETCH MyCursor INTO @tags
END

CLOSE myCursor

DEALLOCATE myCursor

Source : http://sqlpro.developpez.com/cours/sqlserver/transactsql/

July 10, 2008

Ssh connection without password

Filed under: Linux — itbdc @ 1:31 pm

on client side:

ssh-keygen -t rsa

on server side:
copy the public key from client to home directory that you want to be identified
then

cat id_rsa.pub >> ~/.ssh/authorized_keys

options :
If strict_mode, chmod 600 ~/.ssh/authorized_keys
If Authentication refused : PreferredAuthentications publickey

July 9, 2008

Export an Oracle database

Filed under: Database, Oracle — itbdc @ 11:10 am

export ORACLE_SID=INSTANCE_NAME

vi /tmp/export.par

USERID=login_db/pwd_db
LOG=export.log

/oracle/product/10g/bin/exp parfile=/tmp/export.par file=/dump/export.dmp

July 8, 2008

Create an user on Oracle

Filed under: Database, Oracle — itbdc @ 2:49 pm

launch sqlplus user@INSTANCE

create user $user IDENTIFIED BY $pwd;
grant dba to $user;
create tablespace $user datafile '$dir/${user}.dbf' size 100M autoextend on next 100M;
CREATE TEMPORARY TABLESPACE ${user}TMP TEMPFILE '$dir/${user}tmp.dbf' size 512M autoextend on next 10M;
ALTER USER $user DEFAULT TABLESPACE $user TEMPORARY TABLESPACE ${user}TMP;

How to install Oracle ODBC Client for Cent OS, Fedora or other Redhat products

Filed under: Database, Oracle — itbdc @ 11:49 am

yum install unixODBC
rpm -ivh oracle-instantclient-basic-10.2.0.3-1.i386.rpm #downloaded on http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
unzip instantclient-odbc-linux32-10.2.0.3-20061115.zip #downloaded on http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
cp ./instantclient_10_2/libsqora.so.10.1 /usr/lib/oracle/10.2.0.3/client/lib/
export ORACLE_HOME=/usr/lib/oracle/10.2.0.3/client
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
mkdir /etc/oracle
vi /etc/oracle/tnsnames.ora

MY_SID =
 ( DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS =
  (PROTOCOL = TCP)
  (Host = 127.0.0.1)
  (Port = 1521)
  )
 )
 (CONNECT_DATA = (SID = MY_SID)
 )
)

export TNS_ADMIN=/etc/oracle

vi /etc/odbcinst.ini

[OracleODBC-10g]
Description = Oracle ODBC driver for Oracle 10g
Driver = /usr/lib/oracle/10.2.0.3/client/lib/libsqora.so.10.1
FileUsage = 1
Driver Logging = 7

vi /etc/odbc.ini

[simple]
Driver = OracleODBC-10g
DSN = OracleODBC-10g
ServerName = MY_SID
UserID = USER
Password = PASSWORD

isql -v simple

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

July 1, 2008

Delete a connected user on Oracle

Filed under: Database, Oracle — itbdc @ 3:30 pm

drop user GOAWAY cascade;
return “cannot drop a user that is currently connected”

2 solutions :

  • List the session of your user :
    select username, sid, serial# from v$session where lower(username)='GOAWAY';
    alter system kill session '143, 148';
  • Restart your instance :
    startup force restrict

June 23, 2008

Export / Import PostgreSQL database

Filed under: Database, PostgreSQL — itbdc @ 12:55 pm

Export a database

su - postgres
pg_dump <database_name> > database_name.pgdump

Import a database

cat database_name.pgdump | psql -d database_name

For all database

pg_dumpall -o > db_all.out
psql -e template1 < db_all.out

Blog at WordPress.com.