Oracle RAC listener and tnsnames file node wise

host file


[oracle@rac2 admin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.



127.0.0.1 localhost.localdomain localhost

# Public
192.168.2.50 rac1.localdomain rac1
192.168.2.51 rac2.localdomain rac2 rac2

#Private

192.168.7.50 rac1-priv.localdomain rac1-priv
192.168.7.51 rac2-priv.localdomain rac2-priv

#Virtual

192.168.2.53 rac1-vip.localdomain rac1-vip
192.168.2.54 rac2-vip.localdomain rac2-vip





on node one

# Generated by Oracle configuration tools.

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.50)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)


====

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


[oracle@rac1 bin]$ ps -ef|grep LIST
oracle 26404 781 0 09:08 pts/3 00:00:00 grep LIST
oracle 30665 1 0 Oct28 ? 00:00:11 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit


====
on node 2


[oracle@rac2 admin]$ cat listener.ora
# listener.ora.rac2 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.51)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_RAC2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

===

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora.rac2 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora.rac2
# Generated by Oracle configuration tools.

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac.world)
(INSTANCE_NAME = rac1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



[oracle@rac2 admin]$ ps -ef|grep LIS
oracle 16119 5159 0 09:06 pts/3 00:00:00 grep LIS
oracle 20876 1 0 Oct28 ? 00:00:08 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC2 -inherit


to start Listener on RAC

[oracle@rac1 bin]$ ps -ef|grep LIST

oracle 26404 781 0 09:08 pts/3 00:00:00 grep LIST
oracle 30665 1 0 Oct28 ? 00:00:11 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_RAC1 -inherit

[oracle@rac1 bin]$ srvctl stop listener -n rac1 -l LISTENER_RAC1

[oracle@rac1 bin]$ ps -ef|grep LIST
oracle 959 781 0 09:13 pts/3 00:00:00 grep LIST
[oracle@rac1 bin]$ srvctl start listener -n rac1 -l LISTENER_RAC1



lsnrctl status LISTENER_RAC1

Oracle ASM views info and command

v$asm_alias :Lists all aliases in all currently mounted diskgroups
v$asm_client :Lists all the databases currently accessing the diskgroups
v$asm_disk :Lists all the disks discovered by the ASM instance
v$asm_diskgroup :Lists all the diskgroups discovered by the ASM instance
v$asm_file :Lists all files that belong to diskgroups mounted by the ASM instance
v$asm_operation :Reports information about current active operations. Rebalance activity is reported in this view
v$asm_template :Lists all the templates currently mounted by the ASM instance
v$asm_diskgroup_stat :same as v$asm_diskgroup but does discover new diskgroups. Use this view instead of v$asm_diskgroup
v$asm_disk_stat :same as v$asm_disk but does not discover new disks. Use this view instead of v$asm_disk



break on inst_id skip 1

column inst_id format 9999999 heading "Instance ID" justify left
column name format a15 heading "Disk Group" justify left
column total_mb format 999,999,999 heading "Total (MB)" justify right
column free_mb format 999,999,999 heading "Free (MB)" justify right
column pct_free format 999.99 heading "% Free" justify right

select inst_id, name, total_mb, free_mb, round((free_mb/total_mb)*100,2) pct_free
from gv$asm_diskgroup
where total_mb != 0
order by inst_id, name;





/etc/init.d/oracleasm start
/etc/init.d/oracleasm stop
/etc/init.d/oracleasm restart
/etc/init.d/oracleasm configure
/etc/init.d/oracleasm status
/etc/init.d/oracleasm enable
/etc/init.d/oracleasm disable
/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm deletedisk
/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm querydisk /dev/sdd1
/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
/etc/init.d/oracleasm renamedisk /dev/sdb1 VOL1



ASM Specific Init.ora Parameters

.cluster_database= true
.asm_diskstring = ‘/dev/sd*1’
.instance_type=asm
.shared_pool_size=100M
.large_pool_size = 80M
.db_cache_size=60M
.asm_diskgroups = 'DATA','FRA'
.processes=128



$ ls -l /dev/asm
total 0
brwxrwx--- 1 root asmadmin 252, 153601 Nov 28 13:49 docsvol1-300
brwxrwx--- 1 root asmadmin 252, 153602 Nov 28 13:49 docsvol2-300
brwxrwx--- 1 root asmadmin 252, 153603 Nov 28 13:56 docsvol3-300

$ /sbin/mkfs -t acfs -b 4k /dev/asm/docsvol3-300 -n "DOCSVOL3"


/etc/init.d/oracleasm querydisk /dev/multipath/sdb2

srvctl status asm -n linux1


oracle@raclinux1 ~# srvctl status asm -a

Oracle Copy or move old archive log file

ctime last time the inode changed - ie., chmod, chown, etc.
atime last time the file was read (Example: cat, grep, sort, vim, file_get_contents..)
mtime last time the file was written, or open append and closed without writing.



-mtime -2 means files that are less than 2 days old, such as a file that is 0 or 1 days old.

-mtime +2 means files that are more than 2 days old... {3, 4, 5, ...}


find /opt/oracle/admin/anuj/arch -type f -mtime +2|xargs cp -p {} /mnt/backup/Archive_Before_16102010/ {} \;

find /opt/oracle/admin/anuj/arch/*.arc -mtime +2 -exec cp -p {} /mnt/backup/Archive_Before_16102010/ {} \;


find /opt/oracle/admin/anuj/arch -type f -mtime +2|xargs mv {} /mnt/backup/Archive_Before_16102010/ {} \;


shell script


days=10
find '/opt/oracle/admin/anuj/arch/' -type f -mtime +$days | \
# find '/tmp/' -type f -mtime +$days | \
while read file
do
cp $file /mnt/backup/Archive_Before_16102010/
# cp $file /tmp/XXX
done

dbms_metadata package to extract the schema ddl

Oracle schema / user ddl  via dbms_metadata

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);

SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';

dbms_metadata package to extract the schema ddl

Oracle dbms_metadata package to extract the schema ddl ....

SET LONG 10000  TRIMSPOOL ON  LINES 180 HEADING OFF  FEEDBACK OFF PAGES 0  VERIFY OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SPECIFICATION',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'BODY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE);


SPOOL ANUJ_DDL.SQL
CONNECT SCOTT/TIGER;
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) FROM USER_OBJECTS ;

from sys account 

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, owner) FROM dba_OBJECTS
where owner='SCOTT';



set pages 800 lines 300  long 99999
select DBMS_METADATA.GET_DDL('USER','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&USER') ddl from dual union all
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&USER') ddl from dual ;


set autoprint on long 100000
variable Y clob
variable x clob

declare
            no_grant exception;
            pragma exception_init( no_grant, -31608 );
begin
   
  
begin 
            :Y := dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '&&USER' );
         
exception
           when no_grant then :Y := '-- no system grants';
end ;
 end;
/




SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT','RESOURCE') from dual;

What is a Clustering Factor and how to improve?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,b.num_rows,b.blocks,a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
and a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;


SELECT constraint_name,table_name,r_constraint_name,status FROM dba_constraints 
WHERE constraint_type='P' and table_name='USER_SITES' 
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED



SQL> create table anuj.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "ANUJ"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ"; 

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where 1=1
-- and index_name in ('USER_SITES_USPK1')
and a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') 
/


INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792 <<<<<--- 
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<<--new Clustering Factor 


now drop the old table. rename new table to old table name


to modify clustering factor >12c

TABLE_CACHED_BLOCKS statistics preference allows us to modify how the CF is 
calculated by not incrementing the CF value if an index rowid points to a block that was visited 
just TABLE_CACHED_BLOCKS




set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual 





PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             1




to set value !!!

set linesize 100 pagesize 300

define tabname='EMP'
define owner='ANUJ'
exec dbms_stats.set_table_prefs(ownname=>'&&owner', tabname=>'&&TABLE', pname=>'TABLE_CACHED_BLOCKS', pvalue=>10);
 



define tabname='EMP'
define owner='ANUJ'
select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual

SQL> /
old   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'&&TABLE',ownname => '&&owner') prefs_for__&&TABLE FROM dual
new   1: select rpad('TABLE_CACHED_BLOCKS : ',50)||dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS', tabname=>'EMP',ownname => 'ANUJ') prefs_for__EMP FROM dual

PREFS_FOR__EMP
----------------------------------------------------------------------------------------------------
TABLE_CACHED_BLOCKS :                             10


EXEC dbms_stats.gather_table_stats(ownname=>&&owner, tabname=>'&&TABLE', estimate_percent=> null, cascade=> true,   method_opt=>'FOR ALL COLUMNS SIZE 1');


gather stats ... 

define tabname='EMP'
define owner='ANUJ'

set pages 100 lines 250
--set echo off feedback off heading on 
col gather for a200
spool gather.sql
select 'SET ECHO ON FEEDBACK ON TIMING ON' FROM DUAL;
select 'exec dbms_stats.gather_table_stats (ownname => ''' || owner ||''', tabname => '''||table_name||''' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => '''||' FOR ALL COLUMNS SIZE AUTO'''||' , degree => 8);' gather  from   dba_tables
where table_name in ('&&tabname')
and OWNER='&&owner'
;



exec dbms_stats.gather_table_stats (ownname => 'ANUJ', tabname => 'EMP' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

What is a Clustering Factor and how to improve ?

Clustering Factor is a value that tells Oracle how the rows in a table are randomly distributed with respect to
index key values. good Clustering Factor value would be

dba_indexes.CLUSTERING_FACTOR=dba_tables.blocks


if difference is very high than cost of using index will be high

how to improve Clustering Factor ?

set linesize 200
select a.index_name,b.table_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where
a.table_name = b.table_name
and b.owner='ANUJ'
and b.table_name='USER_SITES'

INDEX_NAME TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 USER_SITES 1038392 6544 570792


in this case Clustering Factor is very bad .


get index info from following query

set long 50000
select dbms_metadata.get_ddl('INDEX','USER_SITES_USPK1','ANUJ') from dual ;




SELECT constraint_name,table_name,r_constraint_name,status
FROM dba_constraints WHERE constraint_type='P' and table_name='USER_SITES' 2
/

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
USER_SITES_USPK1 USER_SITES ENABLED




select dbms_metadata.get_ddl('TABLE','USER_SITES','ANUJ') from dual;

DBMS_METADATA.GET_DDL('TABLE','USER_SITES','ANUJ')
--------------------------------------------------------------------------------

CREATE TABLE "ANUJ"."USER_SITES"
( "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
"SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
"SITE_DATE_ADDED" DATE NOT NULL ENABLE,
"TANDC_DATE_ACCEPTED" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "ANUJ" ;
ALTER TABLE "ANUJ"."USER_SITES" ADD CONSTRAINT "USER_SITES_USPK1" PRIMARY
KEY ("USER_OBJECT_ID", "SITE_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "ANUJ" ENABLE;






SQL> create table cccprod1.user_sites_new as select * from anuj.user_sites order by USER_OBJECT_ID,SITE_NO ;

Table created.

SQL> select count(*) from anuj.user_sites;

COUNT(*)
----------
1043588

SQL> select count(*) from anuj.user_sites_NEW;

COUNT(*)
----------
1043588

SQL> CREATE UNIQUE INDEX "ANUJ"."USER_SITES_USPK_NEW" ON "CCCPROD1"."USER_SITES_NEW" ("USER_OBJECT_ID", "SITE_NO")
TABLESPACE "ANUJ";


Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'ANUJ',-
tabname=>'USER_SITES_NEW',-
estimate_percent => 100,-
cascade=>true,-
degree => DBMS_STATS.AUTO_DEGREE,-
method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from dba_indexes a, dba_tables b
where
-- index_name in ('USER_SITES_USPK1')
a.table_name = b.table_name
and b.table_name in ('USER_SITES','USER_SITES_NEW') /

INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
USER_SITES_USPK1 1038392 6544 570792
USER_SITES_USPK_NEW 1043588 6596 6586 <<<<---- good Clustering Factor

now drop the old table. rename new table to old table name

ORA-16038: log 1 sequence# cannot be archived

SQL> startup; ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 322961600 bytes
Database Buffers 205520896 bytes
Redo Buffers 3833856 bytes
Database mounted.
ORA-16038: log 1 sequence# 187 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: '/opt/app/oracle/datafile/vihaan/redo01.log'


clear some space in mount point

like find . -atime +2 -exec rm {} \;

Quotes in Strings ----- Oracle Q-quote

select q'[dfdsfhgdaskghfkdskhdasfhfkjhasfsaf]' from dual;


Q'[DFDSFHGDASKGHFKDSKHDASFHFKJHASF
----------------------------------
dfdsfhgdaskghfkdskhdasfhfkjhasfsaf




SQL> select q'[What ever stuff you'd like to see, just as you'd like to see it.]' string from dual;

STRING
----------------------------------------------------------------
What ever stuff you'd like to see, just as you'd like to see it.



select q'[dfdsfhgda'fdhf'hdjasdkjhsakd'klsadjlsd'''ksjadljd''']' from dual
2 /

Q'[DFDSFHGDA'FDHF'HDJASDKJHSAKD'KLSADJLSD'''KSJADLJD
----------------------------------------------------
dfdsfhgda'fdhf'hdjasdkjhsakd'klsadjlsd'''ksjadljd'''

ASM diagnostics Report

ASM diagnostics Report
# From Metalink Note 470211.1

SPOOL ASM_FIRST.HTML
SET MARKUP HTML ON
SET ECHO ON
SET PAGESIZE 200
SELECT * FROM V$ASM_DISKGROUP;
SELECT * FROM V$ASM_DISK ORDER BY GROUP_NUMBER,DISK_NUMBER;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM V$VERSION;
SHOW PARAMETER ASM
SHOW PARAMETER CLUSTER
SHOW PARAMETER INSTANCE_TYPE
SHOW PARAMETER INSTANCE_NAME
SHOW PARAMETER SPFILE
SPOOL OFF

User has no SELECT privilege on V$SESSION v_$sql_plan v_$sql

if you are getting this error from scott



SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN


then grant following permission to scott



SQL> GRANT SELECT ON v_$session TO scott;

Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

SQL> grant select on v_$sql_plan to scott;

Grant succeeded.

SQL> grant select on v_$sql to scott;

Grant succeeded.