MOHAMMED ABDUL HAMEED: November 2016

Wednesday, November 30, 2016

How to Uninstall Oracle Database 11g from Windows

Posted by MOHAMMED ABDUL HAMEED on 2:17 AM

Steps:

Stop all Oracle services first.

START -> RUN -> Services.msc ( Locate all Ora* services & click on STOP )

Start -> RUN -> Regedit

Locate HKEY_LOCAL_MACHINE folder in the registry
Open Software folder
Delete the Oracle folder under Software
Open System folder in HKEY_LOCAL_MACHINE
Open CurrentControlSet folder within the System folder
Open Services
Delete all keys related to Oracle. Every key starts with “ORA..”
Close the regedit
Delete ORACLE_HOME folder
Delete the Oracle folder in Program Files

Reboot

Friday, November 25, 2016

Include Select statement in EXPDP

Posted by MOHAMMED ABDUL HAMEED on 3:38 PM

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd11.dmp
LOGFILE = hyd_lg.log

include=TABLE:"IN (select table_name from dba_tables where table_name like 'NAM%')"

include=table:\"in\(select table_name from all_tables where table_name like \'NAM%\')\"

Now Export it by using the below syntax of export :

[oracle@stdby ~]$ expdp PARFILE='/u01/hyd.par'

Export: Release 11.2.0.3.0 - Production on Sat Nov 26 05:00:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: hameed
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HAMEED"."SYS_EXPORT_SCHEMA_01": hameed/******** PARFILE=/home/oracle/Desktop/hyd.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HAMEED"."NAME" 5.828 KB 1 rows
. . exported "HAMEED"."NAMEALL" 5.828 KB 1 rows
. . exported "HAMEED"."NAMEFEW" 5.828 KB 1 rows
Master table "HAMEED"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HAMEED.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/hyd/dpdump/hyd11.dmp

Job "HAMEED"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:00:57

Export / Import by using Parameter File

Posted by MOHAMMED ABDUL HAMEED on 2:34 PM

Export :

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd.dmp
LOGFILE = hyd_lg.log
TABLES = hameed.info

Now Export it by using the below syntax of export :

[oracle@stndby ~]$ expdp PARFILE='/u01/hyd.par'

Export: Release 11.2.0.3.0 - Production on Sat Nov 26 03:31:46 2016

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA PARFILE=/home/oracle/Desktop/hyd.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "HAMEED"."INFO" 5.828 KB 1 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/admin/hyd/dpdump/hyd.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:32:04

Import :

Create a parameter file with all the details as follows :

DIRECTORY = DATA_PUMP_DIR
DUMPFILE = hyd.dmp
LOGFILE = hyd_lg.log
TABLES = hameed.info

Now Import it by using the below syntax of import :

[oracle@localhost ~]$ impdp PARFILE=/home/oracle/Desktop/hyd.par

Import: Release 11.2.0.3.0 - Production on Sat Nov 26 04:02:31 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA PARFILE=/home/oracle/Desktop/hyd.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HAMEED"."INFO" 5.828 KB 1 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 04:02:38

Wednesday, November 23, 2016

Oracle Statistics

Posted by MOHAMMED ABDUL HAMEED on 12:36 PM

When a valid SQL statement is fired , the oracle database have to decide its plan in order to retrieve the necessary data requested by the query.

This decision can be made by the using the following methods mention below :

Rule Based Optimizer (RBO) : Optimization is done via a set of rules,distribution of data is not taken into account.

Cost Based Optimizer (CBO) : Execution plan is calculated by taking into account the distribution of data. Requires analysis of tables and indexes (DBMS_STATS)

Sunday, November 20, 2016

Create Database using DBCA - Oracle 11g

Posted by MOHAMMED ABDUL HAMEED on 1:07 PM

Tuesday, November 15, 2016

Checking operating system version: must be 5.0, 5.1, 5.2 or 6.0 . Actual 6.1 Failed

Posted by MOHAMMED ABDUL HAMEED on 1:55 PM

Hi Folks ,

We got this error while installing Oracle 10g on Window server 2008 R2

when we run the setup.exe file by admin (Select setup.exe -> Right click -> Run as admin ) then , it throw this error:

Workaround :

After putting long hours the workaround is to modify the "oraparam.ini" file which is placed with Oracle setup files, in order to make compatible with Window Server 2008 R2.

Location : database\install\oraparam.ini

We need to add the below mention lines in oraparam.ini:

[Certified Versions]
# You can customise error message shown for failure, provide value for
CERTIFIED_VERSION_FAILURE_MESSAGE
Windows = 5.0,5.1,5.2,6.0,6.1

[Windows-6.1-required]
# Minimum display colours for OUI to run
MIN_DISPLAY_COLORS = 256
# Minimum CPU speed required for OUI
# CPU = 300