Row count for all tables in a schema

Row count of the tables in a schema can be fetched from USER_TABLES.

Query Output Method: Run the DBMS_STATS.gather_schema_stats package/procedure to gather statistics for all the objects in the schema and then query the USER_TABLES for the NUM_ROWS column to fetch the row count. This is one of the well known methods.

SQL> select table_name, nvl(num_rows,0) from user_tables order by 2 desc;

XML Output Method : In this method we use the DBMS_XMLGEN written by Mathias TITLE: Dynamic in-line queries.

SQL> select table_name
,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Notes:
The inner query calls the function dbms_xmlgen to generate the output in xml format, to return one row and one value for every row fetched by the outer query.
XMLTYPE: The XMLTYPE wrapper function converts the xml output to xmlobject so XPATH functions can be used. In this case xmltype will return xmls data as

<?xml version="1.0"?>
<ROWSET>
<ROW>
 <C>200</C>
</ROW>
</ROWSET>

EXTRACTVALUE The EXTRACTVALUE is an xml function which takes arguments of XMLType and an XPath expression and returns a scalar value. In this case xpath is '/ROWSET/ROW/C'.
TO_NUMBER: function to convert to number

Using dbms_xmlgen

Oracle's DBMS_XMLGEN is a cool package to generate xml data for a simple query(single-node) or complex query(with sub-nodes)


e.g.1:
SQL>select user_firstname, user_lastname
from arm$_user
where rownum <= 11;

With DBMS_XMLGEN
SQL>select dbms_xmlgen.getxml('
select user_firstname, user_lastname
from arm$_user
where rownum <= 11') user_node
from dual;

e.g.2:
SQL>select product_id, product_name, product_createdate from product where rownum <>select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11;

With DBMS_XMLGEN with sub-nodes
SQL>select dbms_xmlgen.getxml('
select department_id, department_name,
cursor(select user_firstname, user_lastname
from arm$_user u
where u.department_id = d.department_id) userrow
from arm$_department d
where rownum <= 11') with_snode
from dual;

Delete oracle logs older by x days or minutes

Note: Assuming logs,trace,aduit & etc files are at the default location.

Delete oracle logs older by x days.
$ find /$ORACLE_BASE/admin/$ORACLE_SID/bdump/*. -mtime +1 -exec rm -f {} \;

Delete oracle logs older by x minutes.
$ find /$ORACLE_BASE/admin/$ORACLE_SID/bdump/*. -mmin +120 -exec rm -f {} \;

Switch "-mtime n" Files data was last modified n*24 hours ago.
Switch "-mmin n" Files data was last modified n minutes ago.

Silent installation of Oracle XE Server

Steps to create silent installation of Oracle XE Server

To perform a silent installation of Oracle Database XE Server, you need to create a response file and a wrapper shell script to perform the silent installation. The silent installation response file handles the configuration information

To perform a silent installation:

1. After downloading the installation executable

Create a response file that contains settings for the following values:

· A valid HTTP port numeric value, so that you can connect to the Oracle Database XE graphical user interface

· A valid listener numeric port value, so that you can connect to the Oracle Database XE database

· A password value for the SYS and SYSTEM administrative user accounts

· The SYS and SYSTEM password value again, to confirm it

· Yes (y) or no (n), to specify whether you want to start Oracle Database XE automatically when the computer starts

List only the values themselves, in the order shown as follows. For example:

8080

1521

44gh992

44gh992

y

2. Create a wrapper shell script to perform the silent installation.

It should contain commands similar to the following:

#!/bin/bash

rpm -ivh  /downloads/oracle-xe-univ-10.2.0.1-1.0.i386 > /xe_logs/XEsilentinstall.log

/etc/init.d/oracle-xe configure <  /downloads/responsefile >> /xe_logs/XEsilentinstall.log

3. Run the wrapper script.

For details of the installation, see the XEsilentinstall.log file.

Link to otn.oracle.com

http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm#BABCCGCF )

Quick install of oracle 64-bit on a Rhel4 64-bit

Oracle10g R2 64-bit on a Rhel4 64bit,
Note: Oracle10g R2 64-bit is not supported by Oracle on Rhel5.

Checking the Hardware Requirements

1. SWAP Space

Set SWAP to 0.75 times the size of RAM if the RAM is more than 8192 MB on the server.

2. /tmp directory

400 MB of disk space in the /tmp directory

Set the TEMP and TMPDIR environment variables when setting the oracle user’s environment.

3. Find system architecture

To determine whether the system architecture can run the software, enter the following command: # grep "model name" /proc/cpuinfo.

4. Checking the Software Requirements

The system must be running the following (or later) kernel

version: Red Hat Enterprise Linux 4.0 2.6.9-11.EL

The following (or later versions) should be the list of packages for Red Hat Enterprise Linux 4.0:

binutils-2.15.92.0.2-10.EL4

compat-db-4.1.25-9

compat-libstdc++-33-3.2.3-47.3

compat-libstdc++-33-3.2.3-47.3(i386)

control-center-2.8.0-12

gcc-3.4.3-22.1

gcc-c++-3.4.3-22.1

glibc-2.3.4-2

glibc-2.3.4-2(i386)

glibc-common-2.3.4-2

glibc-devel-2.3.4-2

glibc-devel-2.3.4-2(i386)

gnome-libs-1.4.1.2.90-44.1

libaio-0.3.96-3

libgcc-3.4.3-9.EL4

libstdc++-3.4.3-9.EL4

libstdc++-devel-3.4.3-9.EL4

make-3.80-5

pdksh-5.2.14-30

sysstat-5.0.5-1

xorg-x11-deprecated-libs-6.8.1-23.EL

binutils-2.15.92.0.2-13.0.0.0.2.x86_64

libxml2-2.5.10-7.i386.rpm

5. To check the RPMs:

rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio

6. Oracle JDBC/OCI Drivers

You can use the following version of JDK with the Oracle

JDBC/OCI drivers:

Sun JDK 1.5.0 (64-bit)

7. Creating Required Operating System Groups and User

Create the oinstall and dba groups:

# /usr/sbin/groupadd oinstall

# /usr/sbin/groupadd dba

Create oracle user, command to create it:

# /usr/sbin/useradd -g oinstall -G dba oracle

Set the password of the oracle user:

# passwd oracle

8. Configuring Kernel Parameters

Using any text editor, create or edit the /etc/sysctl.conf

file and add or edit lines similar to the following:

Parameter Value File

semmsl 250 /proc/sys/kernel/sem

semmns 32000

semopm 100

semmni 128

shmall 2097152 /proc/sys/kernel/shmall

shmmax (RAM/2) /proc/sys/kernel/shmmax

Note: Half the size of physical memory (in bytes)

shmmni 4096 /proc/sys/kernel/shmmni

file-max 65536 /proc/sys/fs/file-max

ip_local_port_range Minimum(1024): Maximum(65000): /proc/sys/net/ipv4/ip_local_port_range

rmem_default 262144 /proc/sys/net/core/rmem_default

rmem_max 262144 /proc/sys/net/core/rmem_max

wmem_default 262144 /proc/sys/net/core/wmem_default

wmem_max 262144 /proc/sys/net/core/wmem_max

9. Set Shell Limits for the oracle user

To increase the shell limits:

i. Add the following lines in the

/etc/security/limits.conf file:

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

ii. Add the following line to the /etc/pam.d/login file, if it

does not already exist:

session required pam_limits.so

iii. Depending on the oracle user's default shell, make the

following changes to the default shell startup file:

■ For the Bourne, Bash, or Korn shell, add the following

lines in the /etc/profile file (or the

/etc/profile.local file on SUSE systems):

if [ $USER = "oracle" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

10. Creating Required Directories

Create the Oracle base directory

#mkdir –p /u01/app/oracle

#chown –R oracle:dba /u01/app/oracle

#chmod –R 765 /u01/app/oracle

11. Install libaio before Installing or Upgrading

Before upgrading to or installing Oracle Database 10g release 2, install the

libaio package on Red Hat Enterprise Linux 4.0.

12. Install binutils on Oracle EnterpriseEnterprise Linux 4.0 Linux 4.0 and Red Hat

Before installing Oracle Database 10g release 2 on Red Hat Enterprise Linux 4.0 Update 1 or above, install the following package:

binutils-2.15.92.0.2-13.0.0.0.2.x86_64

This package can be downloaded from the following link:

http://oss.oracle.com/projects/compat-oracle/dist/files/RedHat/binutils-2.15.92.0.2-13.0.0.0.2.x86_64.rpm

13. Install libxm before Installing Oracle Lite

Before installing Oracle Lite, ensure that the following package is installed:

libxml2-2.5.10-7.i386.rpm

14. Check Linux version redhat-release package is installed

Without this RPM, Oracle 10g OUI won't be able to recognize it as a supported OS.

#rpm –q redhat-release

Output for the command

redhat-release-4AS-6.1