Powered By Blogger

Wednesday, January 26, 2011

Using Variable in SQLPLUSW

Let’s see the behavior when using a bind variable. Here is the demonstrated behavior in Oracle Database 10g.

SQL> var state_code varchar2(2)
SQL> exec :state_code := ‘CT’
PL/SQL procedure successfully completed.
SQL> select max(times_purchased) from customers where state_code = :state_code
2 /

Loop Back Adapter Configuration (General information :-) )

If there is a loopback adapter installed, you would see a section that lists the values for the loopback adapter. For example:

Ethernet adapter Local Area Connection 2:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : Microsoft Loopback Adapter
Physical Address. . . . . . . . . : 02-00-4C-4F-4F-50
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Autoconfiguration IP Address. . . : 169.254.25.129
Subnet Mask . . . . . . . . . . . : 255.255.0.0

Installing a Loopback Adapter on Windows 2003 or Windows XP

To install a loopback adapter on Windows 2003 or Windows XP:

>Open the Windows Control Panel.
>Double-click Add Hardware to start the Add Hardware wizard.
>In the Welcome window, click Next.
>In the Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
>In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
>In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
>From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
>In the Select Network Adapter window, make the following selections:
Manufacturer: Select Microsoft.
Network Adapter: Select Microsoft Loopback Adapter.
Click Next.
>In the The wizard is ready to install your hardware window, click Next.
>In the Completing the Add Hardware Wizard window, click Finish.

If you are using Windows 2003, restart your computer.

>Right-click My Network Places on the desktop and choose Properties. This displays the Network Connections Control Panel.
>Right-click the connection that was just created. This is usually named "Local Area Connection 2". Choose Properties.
>On the General tab, select Internet Protocol (TCP/IP), and click Properties.
>In the Properties dialog box, click Use the following IP address and do the following:
>IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:

192.168.x.x (x is any value between 0 and 255)
10.10.10.10
Subnet mask: Enter 255.255.255.0.
>Record the values you entered, which you will need later in this procedure.
>Leave all other fields empty.
>Click OK.
>Click OK.
>Close Network Connections.
>Restart the computer.

Add a line to the SYSTEM_DRIVE:\WINDOWS\system32\drivers\etc\hosts file with the following format, after the localhost line:
IP_address hostname.domainname hostname

where:
IP_address is the non-routable IP address you entered in step 16.
hostname is the name of the computer.
domainname is the name of the domain.

For example:
10.10.10.10 mycomputer.mydomain.com mycomputer

>Check the network configuration:
>Open System in the Control Panel, and select the Computer Name tab. In Full computer name, make sure you see the host name and the domain name, for example, sales.us.mycompany.com.
>Click Change. In Computer name, you should see the hostname, and in Full computer name, you should see the host name and domain name. Using the previous example, the host name would be sales and the domain would be us.mycompany.com.
>Click More. In Primary DNS suffix of this computer, you should see the domain name, for example, us.mycompany.com.

Removing a Loopback Adapter

To remove a loopback adapter:

>Display System in the Windows Control Panel.
>In the Hardware tab, click Device Manager.
>In the Device Manager window, expand Network adapters. You should see Microsoft Loopback Adapter.
>Right-click Microsoft Loopback Adapter and select Uninstall.
>Click OK.

Some basic commands for DBA's

**************** queries ****************
-----------
The below query lists the records for which the employees have salary greater than 5000 and
the departments which has the number of employees greater than 2.

select * from emp where sal > 5000 group by detpno having count(empno) >2;
-----------

The below query lists the first two employee records filtered using rownum.

select a.* from
(select rownum as rowcnt,empno,ename from emp) a
where rowcnt > 2

-----------

select * from v$session
select * from v$database;
select * from v$archived_log;
select * from v$instance;
select * from v$tablespace;
select * from v$locks;
select * from v$bg;
select * from v$process;
select * from v$datafile;
select * from v$log
select * from v$logfile;
select * from v$controlfile;
select * from v$process
select * from v$system_events

--------- RMAN -----------

Show all;
report schema;
List backup summary;
restore database preview;
List backup;
List incarnation;
List archivelog all completed before 'sysdate-6';
backup archivelog all completed before 'sysdate-1';
backup archivelog all not backedup 1 time;
List backup of datafile 1 ;
restore database except datafile 1;
restore database exclude tablespace 1;
delete archivelog sequence between 1 and 100;
backup datafile 1;

run
{
allocate channel c1 device type disk format='e:\backup\navan.dbf';
backup database;
}

Restore datafile to another location:
----------------------------------------
run
{
allocate channel c1 device type disk;
set newname for datafile 1 to 'e:\backup\system01.dbf';
restore datafile 1;
recover datafile 1;
sql "alter database datafile 1 online";
switch datafile 1;
}

Alter database create datafile 'new datafile path' using 'old datafile path';

************* Parameter related options ***********

> set linesize 100
> col name format a20
> show parameter pfile
> show parameter spfile
> show parameter archivelog
> show parameter controlfile_record_keep_time
> show parameter datafile
> show parameter db_recovery_file_dest
> show parameter db_create_online_log_dest
> show parameter db_create_file_destw
> show parameter logfile
> show parameter undo_retention
> show parameter process
> show parameter listener

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

------------------ Flask back technology --------------

Flash back table to before drop;
select * from emp as of timestamp to_timestamp('14/04/2009 12:22:11 am','dd/mm/yyyy hh:mi:ss am');
select sysdate from dual;
select * from flashback_query
----------------------------------------------------
alter tablespace 1 offline;
alter database datafile 1 move 'new location'
------------------------------------------------------

---------- Linux commands ------------

ps -ef grep oracle
kill -9 processid
top
tail -f filename
[ -- df -g -> only in IBM AIX ]
fdisk -l
mount
df -h
pwd
----
service nfs restart
service network restart
----
[ ext3 -> Linux partition file system ]
**
mount -t /mnt/cdrom /dev/cdrom
cd /dev/cdrom
**
ls -l
ls -a

ls -l -t ->  << list files in last modified order >>.
find *.* -atime -2 exec ls -l {} \; -->  << list files that are accessed 2 times >>
find *.* -atime -2 -exec ls -al {} \;
find . -iname "*.*" -mtime -2 -print

Transport Tablespace Reference

For Oracle transport tablespace refer this:

1. Make the tablespace read only.
2.export tablespace using TRANSPORT_TABLESPACE option
3. copy the datafile to the destination database server.
4.transfer the dumpfile to the destination server.
5. import tablespace using TRANSPORT_TABLESPACE option.
6. Make the tablespace read write

Eg:
Transport Tablespace:
exp TRANSPORT_TABLESPACE=y TABLESPACES=(history) FILE=/disk2/backup/hs.dmp

Table Level Import by replacing statistics

table import
-------------------

[nvar@insiscdev01 Navan]$ impdp system/nvar@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TMP_22Apr10.dmp tables=nvartxn.REPORT_GROUP remap_schema=nvartxn:nvartxn TABLE_EXISTS_ACTION=replace

Import: Release 11.1.0.6.0 - Production on Friday, 23 April, 2010 0:42:22
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
nvarting "SYSTEM"."SYS_IMPORT_TABLE_01": system/********@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TMP_22Apr10.dmp tables=nvartxn.REPORT_GROUP remap_schema=nvartxn:nvartxn TABLE_EXISTS_ACTION=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "nvarTXN"."REPORT_GROUP" 14.16 KB 46 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 00:42:27

import schemas from dump
------------------------------

[nvar@insiscdev01 Navan]$ impdp system/nvar@nvarTMP DIRECTORY=EXP_DMP DUMPFILE=expdp_nvar4_TST_22Apr10.dmp remap_schema=nvartxn:nvartxn,nvarrep:nvarrep,nvartxnapp:nvartxnapp,nvarrepapp:nvarrepapp,nvarmig:nvarmig logfile=impdp_nvar4_TMP_22Apr10.log

Oracle 10g Complete Architecture

Oracle 10g Architecture


Oracle 9i complete Architecture

Oracle 9i complete Architecture