All posts by San Maharjan

Exporting Query Output into File

For reporting purpose you might need to dumps lots of query into file.
yes yes, most of the client tools allows to dump directly into xls, html, xml file which take a transfer bandwith to transfer data from server to client. so for big data this system might not be so worth it.you can find lots of way to dump text into file.  here is one of the way i have been doing since 2 yrs.

create or replace procedure dump_table_to_csv(l_query in varchar2,
p_dir   in varchar2,
p_filename in varchar2,
v_separator     VARCHAR2 DEFAULT ‘|’,
DateFORMAT VARCHAR2 DEFAULT ‘DD-MON-YYYY’ )
AS
l_output        utl_file.file_type;
l_theCursor     integer default dbms_sql.open_cursor;
l_columnValue   varchar2(4000);
l_status        integer;
l_colCnt        number := 0;
l_separator     varchar2(1);
l_descTbl       dbms_sql.desc_tab;
BEGIN
l_output := utl_file.fopen( p_dir, p_filename, ‘w’,’10000′ );

EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=”DD-MON-YYYY”’;
dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator ||  l_descTbl(i).col_name );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := v_separator;
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := ”;
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := v_separator;
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
EXCEPTION
WHEN OTHERS THEN
raise;
END;
/

and where first argument is your query which generates the data.
2nd one is name of the directory alias
3rd filename you want to make it.
4th seperator, by which you want to separate data from each other. default is ‘|’
and 5th is Date format, in different country way timeformat is different.

e.g.

exec dump_table_to_csv (‘select * from tabs’, ‘DATA_PUMP_DIR’,’test.txt’,’,’,’YYYY-MM-DD’);

Installing Oracle 11g in RHEL 5


  1. Checking the network setup:
a.       Verify the hostname (Issue command hostname)
b.      Change the current hostname in kernel
sysctl kernel.hostname=NEW_HOSTNAME
2.      Editing Hostname /etc/hosts
                127.0.0.1      localhost.localdomain localhost
      ::1            localhost6.localdomain6 localhost6
      192.168.x.x   fullhostname       hostname
               
3.      All necessary prerequisites will be performed automatically.
If you plan to use the “oracle-rdbms-server-11gR2-preinstall” package to perform all your prerequisite setup, follow the instructions at https://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.
        # cd /etc/yum.repos.d
        # mv Oracle-Base.repo Oracle-Base.repo.disabled
        # wget https://public-yum.oracle.com/public-yum-el5.repo
        # yum list
  # yum install oracle-rdbms-server-11gR2-preinstall
        Or 
        # yum install oracle-validated
4.       Manual Configuration.
4.1   Add the following lines to the /etc/security/limits.conf file:
      oracle              soft    nproc   2047
      oracle              hard    nproc   16384
      oracle              soft    nofile  4096
      oracle              hard    nofile  65536
      oracle              soft    stack   10240
4.2   Edit /etc/selinux/config file and disable SELINUX
                SELINUX=disabled
5         Create the new groups and users:
      groupadd -g 501 oinstall
      groupadd -g 502 dba
      groupadd -g 503 oper
      useradd –u 502 -g oinstall -G dba oracle
      passwd oracle
6         Create the directories in which the Oracle software will be installed:
      mkdir -p /u01/app/oracle
      chown -R oracle.oinstall /u01
7         Login as root and issue the following command:
      xhost +
8         Edit /home/oracle/.bash_profile from oracle user as:
export ORACLE_HOSTNAME=hostname;
export ORACLE_UNQNAME=orclunq;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_SID=orcl;
export PATH==$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
9         Unzip Installer of Oracle Database
                # unzip oracle_Linux-x86-64_1of2.zip
      # unzip oracle_Linux-x86-64_1of2.zip
10     Start the Oracle Universal Installer (OUI) by issuing the following command in the database  directory:
      # ./runInstaller
11     Fix the RPM issues and Kernel Parameters as suggested
12     Follow the GUI generated by OUI to complete the installation according to requirement.
13     Install Only Software, By choosing Setup Wizard.
14     After installation of software create database with command
      # dbca
14.1.1      While setting Memory Parameter, Check off the Automatic Memory Management Check Box and choose Set SGA/PGA with Needs

HostName greater than 32 Character

When your server’s hostname exceeds 32 character, then there might have an issue of creating or Running enterprise Manager repository.
for a solution here are the steps to follow.
got to folder $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor$ cd $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor

backup the sqlfile self_monitor_post_creation.sql to any other name incase you need it.
$ cp self_monitor_post_creation.sql self_monitor_post_creation_bk.sql

and edit word “l_host_name  varchar2(32)” to “l_host_name  varchar2(50)” in 2 places of self_monitor_post_creation.sql file

and recreate the enterprise manager repository
 $ emca -config dbcontrol db -repos recreate

How to enable/disable USB ports

There is Three ways to Enable/disable USB port in windows

First Method

  • Right click on MY COMPUTER,
  • then select MANAGE option. a new window will appear, in that
  • select SYSTEM TOOLS, in that
  • click on DEVICE MANAGER, on the right side of the window pane.
  • select the USB port,  right click and diable/enable  the port

Second Method

  • disable through Bios :D

Third Method

  1. Click Start, and then click Run.
  2. type regedit, and then click OK.
  3. Locate, and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\UsbStor
  4. In the right pane, double-click Start.
  5. In the Value data box, type 4 (4 to disable, 3 to Enable) , and then click OK.
  6. Quit Registry Editor.

Enable the task manager

if your computer is infected by the virus which disables you taskmanager??
then here is the step to enable the task manager.
1. to go t run command and type ‘gpedit.msc’
2. go to local computer policy
3. then user configuration
4. then system, then Ctrl+Altr+Del options
5. click the ‘remove task manager’
and set it disable.
now you can see the task manager enabled.

if you like the post please type something,