Archive for the 'Database' Category

 

postgres notes 2 ..

Jan 28, 2016 in Database, PostgreSQL

. . .

$ createdb dbname –owner ownername

$ psql

postgres=# \set ON_ERROR_STOP on

. . .

Oracle connect to schema sqlplus commands

Jan 23, 2014 in Oracle, Uncategorized


$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 24 00:05:54 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant create session to schema;

Grant succeeded.

SQL> connect schema/password;
Connected.
SQL> ALTER TABLE sometable CONSTRAINT sometable_user_c1;

Table altered.

SQL> ALTER TABLE sometable add CONSTRAINT sometable_user_c1 CHECK (credential_store IN ('east', 'west', 'north', 'south' ));

Table altered.

SQL> commit;

Commit complete.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ....

$

Finding Oracle locks ..

Oct 08, 2012 in Database, Oracle

From: http://www.orafaq.com/node/854


SQL> select * from v$lock ;

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from v$lock l1, v$lock l2
  3  where l1.block =1 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2
SQL> /

Resolving ORACLE ERROR:ORA-28000: the account is locked

Sep 28, 2012 in Database, Oracle

FromĀ 
http://blogs.oracle.com/rajeshthekkadath/entry/resolving_oracle_error_ora_28000


$ sqlplus "/ as sysdba"

SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;

.

PostgreSQL list of triggers ..

Nov 21, 2011 in Database, PostgreSQL

SELECT relname, tgname, usename 
 FROM ( 
     pg_trigger JOIN pg_class ON (tgrelid = pg_class.oid) 
               JOIN pg_proc ON (tgfoid = pg_proc.oid) 
               JOIN pg_user ON (relowner = pg_user.usesysid) 
) 
WHERE tgisinternal = FALSE [ and usename = 'user' ]

Postgresql (Postgres) Quick Notes 2..

Sep 30, 2011 in Database, PostgreSQL

— Set superuser :


ALTER ROLE username WITH SUPERUSER;

— Export / extract / dump database :


pg_dump --username=pgadmin --format=p mydb > mydb_pg_dump.sql

Database model ACID rules..

Jun 30, 2010 in Database

From http://databases.about.com/od/specificproducts/a/acid.htm

  • Atomicity states that database modifications must follow an ‘all or nothing’ rule.
  • Consistency states that only valid data will be written to the database.
  • Isolation requires that multiple transactions occurring at the same time not impact each other’s execution.
  • Durability ensures that any transaction committed to the database will not be lost.

Oracle create table with partitioning ..

Jun 09, 2009 in Database, Oracle, Uncategorized

Example of range partitioning:

CREATE TABLE sales (year NUMBER(4),
                    product VARCHAR2(10),
                    amt NUMBER(10,2))
   PARTITION BY RANGE (year)
   PARTITION p1 VALUES LESS THAN (1992) TABLESPACE u1,
   PARTITION p2 VALUES LESS THAN (1993) TABLESPACE u2,
   PARTITION p3 VALUES LESS THAN (1994) TABLESPACE u3,
   PARTITION p4 VALUES LESS THAN (1995) TABLESPACE u4,
   PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE u5;

http://www.oracle-dba-online.com/sql/oracle_table_partition.htm

Mar 31, 2009 in Database, SQLServer

  • — Run ‘SQL Server Configuration Manager’
    — click on
    ‘… Network Configuration’
    ‘Protocols’
    TCP/IP,
    Properties
    ‘IP Addresses’ tab.
  • — SQL service info:

    net start “SQL Server (SQLEXPRESS)”

    net start “SQL Server Browser”

  • — sqlcmd info:

    http://msdn.microsoft.com/en-us/library/ms162773.aspx

    sqlcmd -S tcp:localhost,2248 -l 60

    sqlcmd -S tcp:localhost,2248 -i script.sql

PostgreSQL installation notes..

Jun 26, 2008 in Database, PostgreSQL

On Linux:

1. $ [/sbin/]service postgresql initdb

2. $ [/sbin/]service postgresql start

3. The “initdb” procedure will create user “postgres”.

4. Change /var/lib/pgsql/data/pg_hba.conf

from this:

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident sameuser
# IPv6 local connections:
host    all         all         ::1/128               ident sameuser

to this:

# "local" is for Unix domain socket connections only
#local   all         all                               ident sameuser
local   all         all   trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident sameuser
host    all         all         127.0.0.1/32  trust
# IPv6 local connections:
#host    all         all         ::1/128               ident sameuser
host    all         all         ::1/128  trust

5. Restart postgres

$ [/sbin/]service postgresql restart

6. Test access:

psql -U postgres (no password)