Search This Blog

Total Pageviews

Monday, 4 April 2011

Install Oracle Instant Client for unix / Linux x86-64

Oracle Instant Client installation on unix 64 bit

Install Oracle thin client



Download following rpm from oracle website

oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm
oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm


Instant Client Downloads site

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html



apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]


apt-amd-02:/usr # rpm -ql oracle-instantclient11.2-basic
/usr/lib/oracle/11.2/client64/bin/adrci
/usr/lib/oracle/11.2/client64/bin/genezi
/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
/usr/lib/oracle/11.2/client64/lib/ojdbc6.jar
/usr/lib/oracle/11.2/client64/lib/xstreams.jar



apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
apt-amd-02:/home/anujs/Downloads # rpm -ql oracle-instantclient11.2-sqlplus
/usr/bin/sqlplus64
/usr/lib/oracle/11.2/client64/bin/sqlplus
/usr/lib/oracle/11.2/client64/lib/glogin.sql
/usr/lib/oracle/11.2/client64/lib/libsqlplus.so
/usr/lib/oracle/11.2/client64/lib/libsqlplusic.so


when you install following rpm you will get following error ....

apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient-devel-10.2.0.4-1.x86_64.rpm
error: Failed dependencies:
oracle-instantclient-basic >= 10.2.0.4 is needed by oracle-instantclient-devel-10.2.0.4-1.x86_64




So use --nodeps for this rpm


apt-amd-02:/home/anujs/Downloads # rpm -ivh oracle-instantclient-devel-11.1.0.1-1.x86_64.rpm --nodeps
Preparing... ########################################### [100%]
1:oracle-instantclient-de########################################### [100%]



Create tnsnames.ora file in /etc


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # cat /etc/tnsnames.ora
anujc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(sid = orcl)
)
)


set following variable or add into .bash_profile file

export TNS_ADMIN=/etc
export ORACLE_HOME=/usr/lib/oracle/11.2
export LD_LIBRARY_PATH=$ORACLE_HOME/client64/lib
PATH=$PATH:$HOME/bin:/sbin:$ORACLE_HOME/client64/bin


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # pwd
/usr/lib/oracle/11.2/client64/bin


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # sqlplus scott/tiger@anujc

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 4 15:39:31 2011

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


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

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T TABLE



Now connect to different database .


apt-amd-02:/usr/lib/oracle/11.2/client64/bin # sqlplus scott/tiger@anuj

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 4 16:45:38 2011

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
apt-amd-02:/usr/lib/oracle/11.2/client64/bin # cat /etc/tnsnames.ora
anujc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(sid = orcl)
)
)


anuj =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SID = anujdb)
)
)
Posted by at No comments:

How to set CONNECT_DATA with SID in oracle for tnsnames.ora file

tnsnames.ora CONNECT_DATA SID



anuj2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
)
(CONNECT_DATA =
(SID = anujdb)
)
)



anuj3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
)
(CONNECT_DATA =
(ORACLE_SID = anujdb)
)
)


oracle@novagenesis$ tnsping anuj2

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-APR-2011 11:31:19

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/aptus/oracle/product/10g/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))) (CONNECT_DATA = (SID = anujdb)))
OK (0 msec)


SQL> connect scott/tiger@anuj2
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Posted by at No comments:

Saturday, 2 April 2011

Install squirrel thin client on unix for Oracle

Install squirrel client for oracle
squirrel client for oracle on unix
Thin client (squirrel) for Oracle


First download the squirrel client in this site http://www.squirrelsql.org/


squirrel-sql-3.2.1-install.jar




apt-amd-02:/home/anujs/Downloads # which java
/usr/bin/java

To Install jar file

apt-amd-02:/home/anujs/Downloads # java -jar squirrel-sql-3.2.1-install.jar

then go to this dir , you will able to see this

apt-amd-02:/home/anujs/Downloads # cd /usr/local/squirrel-sql-3.2.1
apt-amd-02:/usr/local/squirrel-sql-3.2.1 # ls -ltr
total 1924
-rw-r--r-- 1 root root 400 2010-12-29 21:56 update-log4j.properties
-rw-r--r-- 1 root root 308 2010-12-29 21:56 log4j.properties
-rw-r--r-- 1 root root 23 2010-12-29 21:56 addpath.bat
-rw-r--r-- 1 root root 1922111 2010-12-29 23:59 squirrel-sql.jar
drwxr-xr-x 2 root root 24 2011-04-01 11:26 update
drwxr-xr-x 2 root root 71 2011-04-01 11:26 icons
drwxr-xr-x 5 root root 4096 2011-04-01 11:26 doc
drwxr-xr-x 2 root root 4096 2011-04-01 11:26 lib
drwxr-xr-x 10 root root 4096 2011-04-01 11:26 plugins
-rw-r--r-- 1 root root 3244 2011-04-01 11:26 squirrel-sql.bat
-rwxr-xr-x 1 root root 4529 2011-04-01 11:26 squirrel-sql.sh
drwxr-xr-x 2 root root 53 2011-04-01 11:26 Uninstaller
-rw-r--r-- 1 root root 4587 2011-04-01 11:26 .installationinformation


then download this jar file from oracle site

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-90769.html

-rw-r--r-- 1 anujs users 1988051 2011-04-01 12:38 ojdbc6.jar


copy this file to in this dir

cp ojdbc6.jar /usr/local/squirrel-sql-3.2.1/lib/


To run squirrel client

apt-amd-02:/usr/local/squirrel-sql-3.2.1 # ./squirrel-sql.sh


jdbc:oracle:thin:@server:1521:database_name

jdbc:oracle:thin:@192.168.2.251:1521:orcl








Posted by at 3 comments:

Friday, 1 April 2011

How to set SERVICE_NAME in oracle for tnsnames.ora

How to set SERVICE_NAME in oracle for tnsnames.ora file


col NAME format a30
col VALUE format a30
SQL> select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name' ;

NAME VALUE
------------------------------ ------------------------------
db_domain
service_names aptdb
db_unique_name aptdb



Default value DB_UNIQUE_NAME.DB_DOMAIN if defined




ANUJ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APTDB) <<<<<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""> )
)



=======================================================================




col NAME format a30
col VALUE format a30
select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name'

NAME VALUE
------------------------------ ------------------------------
db_domain anuj.co.uk
service_names orcl.anuj.co.uk
db_unique_name orcl



default value DB_UNIQUE_NAME.DB_DOMAIN



ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apt-amd-02.anuj.co.uk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<------default be="" br="" db_unique_name.db_domain="" value="" will=""> )
)



anuj1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<<--default be="" br="" db_unique_name.db_domain="" value="" will=""> )
)


===================================

Example file for listener.ora

oracle@novagenesis$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.



LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210 )(PORT = 1521))
)
)

#APTDB =
# (DESCRIPTION_LIST =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.4 )(PORT = 1521))
# )
# )


# to enable trace file
#TRACE_LEVEL_CLIENT = SUPPORT
#TRACE_TIMESTAMP_CLIENT = ON
#TRACE_LEVEL_LISTENER=16
#TRACE_TIMESTAMP_LISTENER=TRUE
#TRACE_DIRECTORY_LISTENER=/opt/oracle/product/10g/network/admin
#TRACE_FILELEN_LISTENER=500000
#TRACE_FILENO_LISTENER=10

======================================================


you can find out variable name following way as well


SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SQL> select sys_context('userenv','DB_NAME') from dual;




SQL> alter user sys identified by sys ;

User altered.




You have to connect sys user through tnsnames other wise you will get wrong result .


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:56:48 2011

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


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

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
SYS$USERS




so connect this way .

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

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


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

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk

or


SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK





oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

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


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

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk



SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK




SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl.ptus.co.uk


SQL> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string ptus.co.uk





service_name = db_unique_name.db_domain
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">***************************************************************************
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">in Oracle 21c
u01/app/oracle/product/21.0.0/dbhome1
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">
<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">
 locate OraDB21Home1
locate -i  oradb21


https://docs.oracle.com/en/database/oracle/oracle-database/21/netrf/local-naming-parameters-in-tns-ora-file.html#GUID-12C94B15-2CE1-4B98-9D0C-8226A9DDF4CB





[grid@oragrid ~]$ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdborcl)
    )
  )

LISTENER_CDBORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))


# start-delta orcl
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.lan)
    )
  )

# end-delta orcl
# start-delta v14mpc
LISTENER_V14MPC =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))

V14MPC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = v14mpc)
    )
  )

PDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=192.168.1.120) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1.localdomain)))
dbfs=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=192.168.1.106) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
# end-delta v14mpc








Posted by at 2 comments:

Friday, 18 March 2011

Oracle dbv database verify Utility

15:57:07 SQL> set linesize 100
15:57:13 SQL> show parameter block
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_C
-------------------------------------------------------------------------------- ----------- -------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
db_file_multiblock_read_count integer 16




15:58:09 SQL> !dbv file=/opt/oracle/oradata/orcl/dmdocbase01.dbf blocksize=8192 feedback=3200

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Mar 18 15:58:12 2011

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

DBVERIFY - Verification starting : FILE = /opt/oracle/oradata/orcl/dmdocbase01.dbf
................................................................................
........................

DBVERIFY - Verification complete

Total Pages Examined : 331776
Total Pages Processed (Data) : 184373
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 121327
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8360
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 17716
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2909064 (0.2909064)
Posted by at 1 comment:

Oracle Goldengate download 32bit etc

go to this site
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
if you don't find the product



Oracle GoldenGate v10.4.0.x for Oracle 10g 32bit on Redhat 4.0
Oracle GoldenGate v10.4.0.x for Oracle 10g 32bit on Redhat 5.0

Oracle GoldenGate on Oracle Media Pack v3 for Linux x86

then

go to https://edelivery.oracle.com/

then fill all the field

then select

select a product Pack: Oracle Fusion Middleware
platform Linux x86

then select this

Oracle GoldenGate on Oracle Media Pack for Linux x86 n/a B56872-03 JUL-19-2010 6 /

Then download for 32 bit

Oracle 10g redhat5.0 v18428-01 26m
Oracle 11g redhat5.0 v18429-01 26m
Posted by at No comments:

Thursday, 17 March 2011

Redo and Number of Checkpoints per hour

set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_logswitches.lst

ttitle left 'Redolog File Status from V$LOG' skip 2

select group#, sequence#,
Members, archived, status, first_time
from v$log;

ttitle left 'Number of Logswitches per Hour' skip 2

select to_char(first_time,'YYYY.MM.DD') day,
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(to_char(first_time,'DDMMYYYY:HH24:MI'),10,2),'23',1,0)),'99') "23"
from v$log_history
group by to_char(first_time,'YYYY.MM.DD')
/
spool off;
Posted by at 2 comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive