Troubleshooting issues in Oracle, RedHat Linux, Windows and lot more!.

How to make the email Address Dummy in Oracle Database? Query to make the email Address as dummy, update <tablename> set <Columnname> = '@' || <Columnname> where <Columnname>  is not null; Query to Remove the email Address as dummy, update <tablename> set <Columnname>= substr(<Columnname>,2,length(<Columnname>)) where <Columnname> is not null;

Ever wonder to find out the oracle  query to find the oracle software version installed on a remote system, Here are the two queries will help you to find the  oracle software versions, SELECT * FROM v$version; BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production" TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SELECT * FROM PRODUCT_COMPONENT_VERSION; PRODUCT                                                       VERSION                                       STATUS NLSRTL                                                       11.2.0.4.0                                      Production Oracle Database 11g  Enterprise Edition    11.2.0.4.0                                     64bit Production PL/SQL                                                            11.2.0.4.0                                      Production TN

How to find the duplicate records in a table? Query: Simple Table SELECT c1, c2, ... ,cn, count(*) FROM TableName GROUP BY c1, c2,..., cn HAVING count(*) > 1; Table with Joins: SELECT c1, c2, ... ,cn, count(*) FROM TableName1 INNER JOIN TableName2 on TableName1.ColName=TableName2.ColName GROUP BY c1, c2,..., cn HAVING count(*) > 1;

As an Oracle DBA, you can expect to be involved in the following tasks: -          Installing Oracle software -          Creating Oracle databases -          Performing upgrades of the database and software to new release levels -          Starting up and shutting down the database -          Managing the database’s storage structures -          Managing users and security -          Managing schema objects, such as tables, indexes, and views -          Making database backups and performing recovery when necessary -          Proactively monitoring the database’s health and taking preventive or corrective action as required -          Monitoring and tuning performance In a small to midsize database environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs, each with their own specialty, such as database security or database tuning.

In order to set the run oracle SQL*Plus client and the other oracle binaries ORACLE_HOME environment variable path should be set which defines the path of Oracle Home[server] installation directory of the windows server. Follow the below steps to set the ORACLE_HOME path in windows server, c:\> Set ORACLE_HOME= C:\oracle\product\11.2.0\ - where [C:] defines the install drive letter How to check the Oracle environment variable set in windows server ? C:\> echo %ORACLE_HOME% - will display the path of oracle home

Below are the some of the sql queries which helps to find the log directories in oracle database server. select * from v$logfile; select * from v$log_history; show parameter debug; select * from dba_profiles; select profile,resource_name,resource_type,limit from dba_profiles; select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace. Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped. When you drop a tablespace, the file pointers in the control file of the associated database are removed. You can optionally direct Oracle Database to delete the operating system files (datafiles) that constituted the dropped tablespace. If you

SQL Queries to drop oracle tablespace, -- drop tablespace: syntax: DROP TABLESPACE <tablespace_name>; eg: DROP TABLESPACE tspace; -- drop tablespace including contents: syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS; eg: DROP TABLESPACE tspace INCLUDING CONTENTS; -- drop tablespace including contents & datafiles:     syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES; eg: DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES; -- drop tablespace including contents & datafiles -- where referential constraints exist:     syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; -- drop tablespace after datafile was accidentally dropped: conn / AS sysdba CREATE TABLESPACE testbed DATAFILE 'c:\temp\testbed.dbf' SIZE 10M BLOCKSIZE 4096 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

desc dba_tablespaces select * from dba_tablespace_usage_metrics select * from dba_directories select * from user_tablespaces Select * from dba_data_files Select * from dba_free_space

Creating Tablespaces Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the  SYSTEM  tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The  SYSTEM  tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the  SYSTEM  tablespace or take it offline. The  SYSAUX  tablespace, which acts as an auxiliary tablespace to the  SYSTEM  tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the  SYSTEM  tablespace, management of the  SYSAUX  tablespace requires a higher level of security and you cannot re

Oracle Network Configuration In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration. Assumptions Listener.ora Tnsnames.ora Sqlnet.ora Testing The example files below are relevant for an Oracle installation and instance with the following values. Parameter   Value HOST hostname ORACLE_HOME /u01/app/oracle/product/9.2.0 ORACLE_SID ORCL DOMAIN world Listener.ora The listerner.ora file contains server side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the server. Here is an example of a listener.ora file from Windows 2000. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TC

How do I copy Oracle Database from one server to another server? Defenitely there will be a need to copy Production Oracle DB at Demonstration/Training, QA/Acceptance or Development environment. Most of the times we will be using Oracle Export utility. step-by-step how to copy database to another server. Note: you may want to change DB name while copying it to another destination. This case does not change the procedure much and steps required to do so will be specially highlighted in the article. Also, you may want to change Oracle DBID of the Database. the reason is that Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. For changing Oracle DBID for a database please refer to the corresponding HOWTO. OK. Here we go. We will imagine a situation when you need top copy PRO (production) DB into TST (test) DB on test server... 1. Instance service creation. (If you

Pre-Requisite: You should have the Oracle database 10g, which you want to migerate. Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6) Step 1) Installing Oracle 11g Home We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home. Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0 then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0 Just a parallel 11.1.0 directory can be created and we can install oracle home in this location. Start the installation using the below command ./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst Screen 1 – Select Product Install select “Oracle Database 11g” Screen 2 – Select Installation Method Choose “Advanced Installation” Screen 3 – Specify Inventory directory and creditials Note: We are providing local inventory here inside the corresponding ORACLE_HOME l

Oracle Import getting stuck Processing object type SCHEMA_EXPORT/ /database1/rdbm6/export> tail -200f impdp_D.SPORTS_03192012.log ;;; Import: Release 11.2.0.2.0 - Production on Mon Mar 19 15:34:10 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA" REMAP_SCHEMA=SPORTS:SPORTS DIRECTORY=dpump_dir DUMPFILE=expdp_D.SPORTS_02282012.dmp LOGFILE = impdp_D.SPORTS_03192012.log REMAP_TABLESPACE = SPORTS_DATA_TS:SPORTS_DATA_TS , SPORTS_INDEX_TS:SPORTS_INDEX_TS Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SPORTS" already exists Processing object type SCHEMA_EXPORT/ROLE_GRANT Proc

Here is the way to find to SGA and PGA Values SQL> select * from v$sga; NAME                      VALUE -------------------- ---------- Fixed Size           ########## Variable Size        ########## Database Buffers     ########## Redo Buffers         ########## SQL> show sga; Total System Global Area  530288640 bytes Fixed Size                  2256864 bytes Variable Size             394264608 bytes Database Buffers          125829120 bytes Redo Buffers                7938048 bytes SQL> select max (pga_used_mem) / 1024/1024 M from v$process;          M ---------- 123.972422 SQL> show parameter pga NAME                                 TYPE        VALUE ------------------------------------ ----------- ---------------- pga_aggregate_target                 big integer 0 SQL> select * from v$pgastat; NAME                      VALUE UNIT -------------------- ---------- -------------------- aggregate PGA target ########## b