Insert timestamps with a loop

This sql command allow you to test data writting in your database. It can be useful to benchmark your disk performances or check the impact of a lock.


INSERT INTO table SELECT timeofday()::timestamp FROM generate_series(1,1000000) WHERE pg_sleep(1) IS NOT NULL;


How to drop oracle database

shutdown abort;
startup mount exclusive restrict;
drop database;
exit

Drop all tables in a tablespace

select ‘drop table ‘, table_name, ‘cascade constraints \;’ from user_tables;

Grant rights on many tables

-- pgx_grant(PRIVILEGE, TABLES, USER)
--  Grants PRIVILEGE to USER on objects like TABLES%
--  Grants to tables, views and sequences.
--
CREATE FUNCTION pgx_grant(text,text,text) returns int4 as '
DECLARE
  priv ALIAS FOR $1;
  patt ALIAS FOR $2;
  user ALIAS FOR $3;
  obj  record;
  num  integer;
BEGIN
  num:=0;
  FOR obj IN SELECT relname FROM pg_class
  WHERE relname LIKE patt || \'%\' AND relkind in (\'r\',\'v\',\'S\') LOOP
    EXECUTE \'GRANT \' || priv || \' ON \' || obj.relname || \' TO \' || user;
    num := num + 1;
  END LOOP;
  RETURN num;
END;
' language 'plpgsql';

My first plpgsql function

At first you need to declare language :

$ createlang plpgsql database

Then connect on postgres :

database=# CREATE FUNCTION a_function () RETURNS int4 AS '
database'# DECLARE
database'# an_integer int4;
database'# BEGIN
database'#    an_integer := 10 * 10;
database'#    return an_integer;
database'# END;
database'# ' LANGUAGE 'plpgsql';

Your first function is now created. You can use it like this :

SELECT a_function() AS output;

Here an exemple of a function :

CREATE OR REPLACE FUNCTION b_function() RETURNS SETOF TEXT AS '
DECLARE
    element_id int4;
    elmt_id0 int4;
    elmt_id1 int4;
    element record;
    output text;
    cur_element CURSOR for SELECT * FROM element;
BEGIN
    OPEN cur_element;
    LOOP
        FETCH cur_element INTO element_id, elmt_id0, elmt_id1;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        DELETE FROM alpha WHERE element_id=element_id;
        DELETE FROM country WHERE element_id=element_id;
        FOR element in SELECT * FROM element WHERE elmt_id=elmt_id0 OR elmt_id=elmt_id1 LOOP
            output := element.element_home_dir || element.element_uri;
            return next output;
        END LOOP;
    END LOOP;
    CLOSE cur_element;
    RETURN;
END;
' LANGUAGE 'plpgsql';
SELECT * from b_function() GROUP BY b_function;

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/

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