Postgres grant select access on all tables in a database

Content moved, please visit my wordpress blog to view this content :)

Postgres processlist and locks

Processlist
select * from pg_stat_activity;

Exclude IDLE processes
select * from pg_stat_activity WHERE current_query not like '<IDLE>%';

Current running queries
select current_query,count(*) from pg_stat_activity WHERE current_query not like '<IDLE>%' group by current_query order by count(*) desc;

Queries holding Exclusive locks
select * from pg_stat_activity where procpid in(select pid from pg_locks where mode='ExclusiveLock');
select pid,count(*) from pg_locks where mode='ExclusiveLock' group by pid order by count(*) desc;

Kill a running query
SELECT pg_cancel_backend(procpid); 

Connecting to a oracle database without tnsnames

Here are the examples to connect using sqlplus to an oracle database without adding any tnsnames entry.


sqlplus scott/tiger@//10.103.18.99:1560/practicedb

If it does not work try this too

connect scott/tiger@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.18.99)(PORT = 1560))) (CONNECT_DATA = (SID = practicedb)))

Basic User Administration commands in NETEZZA

Visit my new wordpress blog to view this content :)

Mysql Secure Installation for non default socket.

Visit my new wordpress blog to view this content :)

Mysql change/recover root password

If you already know the root password you can just update it using
# mysqladmin -u root -p oldpassword newpassword


If you forgot root password and need to restore, it can be done easily with following steps

Step # 1: Stop the MySQL server process.

# /etc/init.d/mysql stop or Kill mysqld and if exists mysqld_safe processes.

Putting Database in ARCHIVELOG mode

Archiving the filled up redo logs is important and enables us to perform a point in time recovery (PITR).

1) Setting initialization parameters:

First we set the location and format for archive logs.

SQL> alter system set log_archive_dest_1 = 'location=/home/oracle/archive';

Creating a database in oracle

Visit my new wordpress blog to view this content :)

Renaming or Moving a Datafile

Steps to move or rename a datafile:

1) Making the tablespace read only in which datafile lies. 


SQL> alter tablespace test read only;
Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces            
       where tablespace_name='TEST';

TABLESPACE_NAME       STATUS
------------------------------ ---------
TEST       READ ONLY

Difference between undo segment and redo logs

Content moved, please visit my wordpress blog to view this content :)