INDEX creation on versioned tables.

In the below example table name is "TEST"


EXEC DBMS_WM.enableVersioning('TEST');


exec dbms_wm.beginddl('TEST');

create index index_name on test_lts(ID);

you can drop index on same condition.

drop index index_name;

exec dbms_wm.commitddl('TEST');


Cheers!!!

ORACLE WORKSPACE INTEGRATION-IMPORTANT DBMS PACKAGES

ORACLE WORKSPACE INTEGRATION

We can create a workspace simply

SQL>exec dbms_wm.createworkspace('WORKSPACE_NAME');

grant workspace privileges to a particular user:
SQL>exec dbms_wm.grantworkspacepriv('ACCESS_WORKSPACE','WORKSPACE_NAME','username');

We can find out current workspaces associated with users

wmsys.wm$workspace_sessions_view

We can resolve conflit of a particular workspace

SQL>exec dbms_wm.SetConflictWorkspace('WORKSPACE_NAME');

After that conflit we need to commit
by

SQL>exec dbms_wm.commitresolve('WORKSPACE_NAME');

We can find out Current workspace

SQL>SELECT DBMS_WM.getworkspace FROM DUAL;

We can connect to a particular workspace

SQL>EXEC dbms_wm.gotoworkspace('WORKSPACE_NAME');

We can merge that particular workspace to parent

SQL>EXEC dbms_wm.mergeworkspace('WORKSPACE_NAME');

We can freeze a workspace to disallow changes to rows in the version-enabled tables

SQL>dbms_wm.freezeworkspace('WORKSPACE_NAME');

reverse this process
by

SQL>exec dbms_wm.unfreezeworkspace('WORKSPACE_NAME');

we can refresh workspace..if we have any conflint,we can find out after refreshing workspace:

SQL> exec dbms_wm.refreshworkspace(' WORKSPACE_NAME');

We can remove a workspace

by

SQL> exec dbms_wm.removeworkspace(' WORKSPACE_NAME');

enable_ddl_logging in oracle 11g

enables or disables the writing of DDL statements to the alert log


alter system set enable_ddl_logging=true scope=both;

Enable version & Disable Version

Enable version:

Table name is test

EXEC DBMS_WM.EnableVersioning('TEST');

Disable version:

EXEC DBMS_WM.DisableVersioning('TEST');

We can see versioned tables from
WM$VERSIONED_TABLES in WMSYS user.

We can see version errors in WM$VT_ERRORS_TABLE in WMSYS user.

DB LINK CREATION.

Creating database link is an easy task.

create public database link link_name

connect to
username
identified by
password

using 'service_name';

DB LINK VIEWS:

DBA_DB_LINKS
ALL_DB_LINKS
USER_DB_LINKS

Creating spatial index.

First of all , We need to insert a value in MDSYS.USER_SDO_GEOM_METADATA


Creating spatial index syntax.


CREATE INDEX ADDRESSES_SIDX ON ADDRESS
(OGC_GEOMETRY)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('layer_gtype=point')
NOPARALLEL;


give gtype as your spatial value


In the above example gtype is point.Means 2001 in table.

Installaing ORACLE 11G R2 in Centos 5 (Minimum requirement)

*****************************

Pre-Instalation Tasks
1. Create oracle User Account

Login as root and create te user oracle which belongs to dba group.

su -
# groupadd dba
# useradd -g dba oracle
# passwd oracle - give password

2. Setting System parameters -for 4+ GB RAM
********************************************


Edit the /etc/sysctl.conf and add following lines:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6553600
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.aio-max-nr = 1048576

2. Setting System parameters -for 2 GB RAM
********************************************
kernel.shmall = 2097152
kernel.shmmax = 1258291200
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 8388608
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max =4194304
net.core.wmem_default = 262144
net.core.wmem_max = 2097152
fs.aio-max-nr = 1048576

Note: You need reboot system or execute "sysctl -p" command to apply above settings.

For RedHat (OEL, Centos) 5 version: Edit the /etc/pam.d/login file and add following line:
session required pam_limits.so

Edit the /etc/security/limits.conf file and add following lines:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

3. Creating oracle directories
# mkdir /opt/oracle
# mkdir /opt/oracle/db_1
# chown -R oracle:dba /opt/oracle


Must Log in as oracle user
*************************

4. Setting Oracle Enviroment
Edit the /home/oracle/.bash_profile file and add following lines:
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/db_1
ORACLE_SID=ORCL
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH


Save the .bash_profile and execute following commands for load new enviroment:
cd /home/oracle
. .bash_profile

1. Download and install required .rpm packages

Login as Root user in terminal
******************************

Some additional packages are required for succesful instalation of Oracle software. To check wheter required packages are installed on your operating system use following command:

rpm -q binutils elfutils elfutils-libelf gcc gcc-c++ glibc glibc-common glibc-devel compat-libstdc++-33 cpp make compat-db sysstat libaio libaio-devel unixODBC unixODBC-devel|sort

We get the list of not installed packages
*****************************************

package compat-db is not installed
package compat-libstdc++-33 is not installed
package elfutils is not installed
package gcc-c++ is not installed
package gcc is not installed
package glibc-devel is not installed
package libaio-devel is not installed
package sysstat is not installed
package unixODBC-devel is not installed
package unixODBC is not installed

Give the following command to install each of them

# yum install compat-db
# yum install elfutils

giving permission to oracle installation folder and the home folder at time of installation.

# chmod -R 777 /opt/oracle11g/

# chmod -R 777 /opt/

Logout and Login in GUI as Oracle User
*************************************

Go to the Instalation setup file path and execute the following command

./runInstaller

Post-Instalation Tasks
1. (Optional) Auto Startup and Shutdown of Database and Listener

Login as root and modify /etc/oratab file and change last character to Y for apropriate database.
ORCL:/opt/oracle/db_1:Y

As root user create new file "oracle" (init script for startup and shutdown the database) in /etc/init.d/ directory with following content:

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

Execute (as root) following commands (First script change the permissions, second script is configuring execution for specific runlevels):

chmod 750 /etc/init.d/oracle
chkconfig --add oracle --level 0356

ORA-12520: TNS:listener could not find available handler for requested type of server

Got this error.


Try to connect your database server directly.Definitely you will get this error.

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-00020: maximum number of processes (250) exceeded
you need to down your listener

Try to login directly using sqlplus

If that not possible

Then ... shutdown your db service

After that increase your number of processes
by

SQL>ALTER SYSTEM SET PROCESSES=350 SCOPE=SPFILE;

you must shutdown after this commands for changing this parameter.

kewastUnPackStats(): bad magic 1 (0x2b2b8f4652ad, 0)

Found the error in rdbms alert log...?

It generates thousands of lines per day which will expand the alert log size.

We can override this error by

ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=MEMORY;

Otherwise we can download new patches from Metalink.

Cheers...!!!