Delete an Oracle Database

$ 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

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

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/

Ssh connection without password

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

Export an Oracle database

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

Create an user on Oracle

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

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                                  |
|                                       |
+---------------------------------------+

Delete a connected user on Oracle

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

Export / Import PostgreSQL database

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