Sharing Knowledge by Mithun Ashok

Everytime you want to start or stop a database service you will have to either do this using sqlplus prompt or through windows services. There is a much simpler way of stopping or starting a database. Follow the steps below. Create a file with an extension .bat for example start.bat Open this file and add the following line in it. NET START OracleServiceORCL Create another file as stop.bat , open this file and add the following line. NET STOP OracleServiceORCL Here I have 3 words, 1. NET helps start and stop services. 2. START/STOP -- Keyword means start or stop the services 3. OracleServiceORCL -- This is the service name of the database which has been installed on my windows. To start the database just double click on start.bat and to stop the database double click on stop.bat. To know the service name of the database on your machine, follow the steps below. 1. Go to Control Panel --> Administrative Tools --> Click on Services icon. This will open

Login to sqlplus session either on your command prompt or on sql*plus window using user "/as sysdba" or user SYSTEM. If you are using user "/as sysdba" then it does not require any password. Check the screenshot below, just enter "/as sysdba" and click ok you will be allowed to login or user SYSTEM user and its password to login. Once you have logged in issue an sql*plus command SHUTDOWN. SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL> This shutsdown your database. To start the database you need to login again as

First, Go to Control Panel --> Administrative Tools --> Click on Services icon. This will open a window show below. Here scroll down to names starting with alphabet O check for anything starting with Oracle. Observe the screenshot and look at Startup Type, for all the service names starting with Oracle Startup Type is set to Automatic except one. This means that everytime you restart your windows database restarts automatically. This is why you feel your windows has gone slow after installing the database. Now right click on each of the Oracle Service and Click

Listing some of the Standards and Full Forms of standard institutes and programing languages. This is not the complete list, you can contribute to this list by posting comments to this article. ANSI    -- American National Standards Institute IEEE    -- Institute of Electrical and Electronics Engineers -- http://www.ieee.org/index.html ISO     -- International Organization for Standardization BIS     -- Bureau of Indian Standards IEC     -- International Electrotechnical Commission ITU     -- International Telecommunication Union SEI     -- Software Engineering Institute WSC     -- World Standards Cooperation NSB     -- National Standards Bodies W3C     -- World Wide Web Consortium IETF    -- Internet Engineering Task Force UPU     -- Universal Postal Union CEC     -- European Committee for Standardization CENELEC -- European Committee for Electrotechnical Standardization PASC    -- Pacific Area Standards Congress CMMI    -- Capability Maturity Model Institute COPANT  -- Pan America

SSL Vulnerability with Webcache with SSL Weak Cipher Following is one of the reports from Nessus. SSL Weak Cipher Suites Supported Synopsis: The remote service supports the use of weak SSL ciphers. Description: The remote host supports the use of SSL ciphers that offer either weak encryption or no encryption at all. Note: This is considerably easier to exploit if the attacker is on the same physical network. Risk factor: Medium CVSS Base Score:4.3 CVSS2#AV:N/AC:M/Au:N/C:P/I:N/A:N See also: http://www.openssl.org/docs/apps/ciphers.html Solution: Reconfigure the affected application if possible to avoid use of weak ciphers. This is usually reported from your security scanner. It does not matter if you have a strong encryption with 128 bit, it all depends on your SSL Cipher version supported. In security it is said that with a weak SSL cipher version server is still vulnerable to attacks. There are 3 types of SSL Version, SSL 2.0, 3.0 and 3.1 or TLS. TLS st

BrainBench.com one of the sites that I have come across which gives you access to free online tests. Anything good that comes for free is rare :-D. Brainbench tests that are available for free. There are about 600 assessments and certifications that they offer, and a great way to try it. There are four types of free tests, to know and try them access the link below. CLICK HERE FOR BRAIN BENCH FREE TESTS

Free download from Oracle and end to end SQL reference guide. I can just say that it is a bible for SQL. CLICK HERE FOR ORACLE 10G SQL REFERENCE --DOWNLOAD PDF CLICK HERE FOR SQL, PL/SQL, and SQL*Plus Syntax and Examples -- ONLINE REFERENCE

Have you thought of sorting by the day of the week. Firstly you have to get the day of the week, check the statement below to get the day of the week. If you try to order day of the week by using ORDER BY on column 3 which is a character column, ORDER BY treats this as text and sorts it from A-Z. So if your intention is to sort from MONDAY to SUNDAY, beginning with  MONDAY as day1 and SUNDAY as day7 then you need to use either CASE or DECODE functions. Check an example with decode below.

Definition: NVL ( expr1 , expr2 ) If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. Example : Login as scott user and access table EMP. First insert a row in table EMP using the following statement. INSERT INTO EMP VALUES(321,'MITHUN',NULL,7782,sysdate,600,300,10); In the above statement am trying to insert an employee MITHUN with job value NULL. Now, SELECT ename,job,NVL(JOB,'NO DESIGNATION') from emp; Here first column is name, second is job and third column is again job with NVL function. For all other employees whose job is not null the value returned from NVL function is job but in case of ename MITHUN nvl returns the second argument that is 'NO DESIGNATION'. Now consider the next statement, SELECT ename,job,NVL(JOB,123) from emp; In the above statement am trying to replace job with 123 in case if it is NULL. This gets executed and 123 is displayed instead of NULL for ename MITHUN. This is because

Hi All, Thanks for all your votes, though my sister did not win the contest but could win the title MISS POPULAR. Thanks again. Mithun

Hey Guys Vote for my sister who is one of the contestants for Max Miss Bangalore. Just drop a mail to maxmissblore@gmail.com,with Sharanya in the subject line. FaceBook Link You can submit your facebook likes here.

Normalization

Codd Rules

Fed up of seeing Lotus Sametime popping up everytime you get a message, check how to disable Lotus sametime chat window popping up. File --> Preferences --> Notifications On the right side click on "One-on-One chat" uncheck "Bring chat window to front" I tried in and out for this configuration before landing up with this solution. Hope this helps.

In R12 after upgrade from 11i, none of the diagnostic tests are available under Diagnostic tab. To test this we submitted concurrent request "Diagnostics Patching CP" which is available from 12.1.1. This request completes normally and in the log file following errors are records, [main][20:23:22:457, 9/4/11] Running oracle.apps.fnd.oam.diagnostics.cp.MigrateCustomizationData [main][20:23:22:458, 9/4/11] $Header: MigrateCustomizationData.java 120.0.12010000.4 2009/06/30 11:40:28 sramados noship $ [20:23:23:532, 9/4/11] Test type xml file location = /oraprod/oracle/finprdcomn/java/oracle/apps/fnd/oam/diagnostics/config/fnddiagTestTypes.xml [main][20:23:23:560, 9/4/11] INV:Onhand Material Status:INV_DIAG_OH_PLAIN_NO_STATUS  Test type xml is not available at the expected location. Please refer log for more details. [20:23:23:645, 9/4/11] Test type xml file location = /oraprod/oracle/finprdcomn/java/oracle/apps/fnd/oam/diagnostics/config/fnddiagTestTypes.xml [main][20:23:2

Hi ALL, Had a very hard time last week, my phone crashed, my laptop hardisk crashed and was loaded with work. For this reason I was not able to reply to anyone's comments or update my site with latest information. I should be able to do this in a day or two as I have got hold of a spare laptop. Still waiting for the data from my old hard disk to be recovered. Mithun

Many of you may have some concern on Basics of DBMS and about the definitions of R-DBMS, H-DBMS and N-DBMS. Some of you might have studied the concepts little different than the one I have defined on my site, but nevertheless definitions and concepts(DBMS) on my site are simple and easy to understand which are explained at a very basic level. Am collating some links which will provide similar and more detailed definitions and insight on DBMS concepts. DBMS and its history -- http://en.wikipedia.org/wiki/Database_management_system Types of DBMS -- http://en.wikipedia.org/wiki/Types_of_DBMS                               http://sqlserver-guide.blogspot.com/2009/05/data-base-system-models-or-types-of.html Notes from Penn State University, USA -- http://www.personal.psu.edu/glh10/ist110/topic/topic07/topic07_06.html Flat File Database -- http://en.wikipedia.org/wiki/Flat_file_database Hierarchical DBMS -- http://en.wikipedia.org/wiki/Hierarchical_database_model Network DBMS -- ht

This video will take you through the steps to install Oracle 10g Release 2 on Windows 7 (Same steps work for Windows Vista). Usually if you install Oracle database on window 7/Vista you would get an error, Abnormal Program Termination. An internal error has occurred. Please provide the following files to Oracle Support. "Unknown" "Unknown" "Unknown" Follow the steps shown in this video to successfully install Oracle Database on Windows 7/Vista.

Following are the different scripts under COMMON_TOP/admin/scripts/$CONTEXT_NAME and their usages, 1. adapcctl.sh --> Apache Startup/Shutdown script 2. adfrmctl.sh --> Forms server Startup/Shutdown script 3. adrmsctl.sh --> Forms metric server Startup/Shutdown script 4. adfmcctl.sh --> Forms metric client Startup/Shutdown script 5. adcmctl.sh --> Concurrent manager Startup/Shutdown script 6. adautocfg.sh --> Autoconfig Startup/Shutdown script 7. adtcfctl.sh --> TCF Socket Startup/Shutdown script 8. adaprstctl.sh -->  Start / Stop Apache Web Server Listener in restricted mode 9. addisctl.sh --> Discoverer Startup/Shutdown script 10. adalnctl.sh --> Applications RPC Listener process Startup/Shutdown script 11. jtffmctl.sh --> JTF Fulfillment server Startup/Shutdown script 12. adstrtal.sh --> Start all of the scripts above using this script 13. adstpall.sh --> Stop all the services from the above script using this script

Oracle Forms Web CGI: Error detected Your request cannot be serviced at this time due to the following error: ERROR: Error getting least loaded host from Load Balancer Server. This error occurs if Oracle forms metric server does not get updated information on existing clients. In Forms metric server load balancing, always clients ping back the information about themselves and server just listens to it. In case for some reason none of the clients are alive or have not passed the information or if forms metric server is down then this error is observed. Sometimes if forms metric server is restarted without restarting client metric server even then this error is observed. You need to have the following information handy along with the sequence of services to be restarted. Always start the script in the order in which it is shown below. 1. adfrmctl.sh --> Forms server startup script 2. adfmsctl.sh --> Forms metric server startup script 3. adfmcctl.sh --> Forms met

RECYCLEBIN is one of the new and core feature of version 10G. This is similar to recyclebin on Mircosoft Windows. In MS Windows temporarily deleted files are kept in recyclebin and you can selectively restore the files you want. Similar to this option Oracle came up with RECYCLEBIN where in dropped tables are kept in RECYCLEBIN and you can selectively restore the tables. SQL> desc recyclebin  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OBJECT_NAME                               NOT NULL VARCHAR2(30)  ORIGINAL_NAME                                      VARCHAR2(32)  OPERATION                                          VARCHAR2(9)  TYPE                                               VARCHAR2(25)  TS_NAME                                            VARCHAR2(30)  CREATETIME                                         VARCHAR2(19)  DROPTIME                                           VARCHAR2

Most of them have been asking me how do I display single quote in a select statement, here we go. Single quotes are used to represent literal strings so one should always know how to handle them, Oracle provides different ways of handling single quotes. 1. If you want to begin and end with single quote, SELECT '''hello how are you''' FROM DUAL; 'hello how are you' Notice that you have to use 3 single quotes in the beginning  and at the end. 2. If you want to display single quote in a using concatenation operator, SELECT 'hi, how' || '''' || 's mithun' FROM DUAL; hi, how's mithun Notice that you have to use 4 single quotes here. 3. If you want to display single quote in between, SELECT 'hey how''z dhruv doing' FROM DUAL; hey how'z dhruv doing Notice that you have to use 2 single quotes here.

Links to all the Presentations on SQL BASICS. Intorduction to RDBMS Basic Select Statement Restricting and Sorting Data Functions Subqueries Group Functions JOINS  Data Definition, Modification and Transaction Control Language   Codd Rules and Normalization PL/SQL Basics

Major goofup on one of my portal servers, suddenly started seeing the following while restarting webcache and webcacheadmin. Protocol of Origin Servers were changed. 13001 Virtual Host Map has a mixture of HTTP and HTTPS Protocol origin servers. All origin servers for a virtual host map must use the same protocol. This is caused due to virtual host map consists of origin servers with HTTP and HTTPS protocols. A virtual host mapping must consist of origin servers that use either the HTTP protocol exclusively or the HTTPS protocol exclusively. To resolve this go to Origin Servers definitions page of OracleAS Web Cache Manager, correct the mapping. 1. If you do not have access to Webcache Administrator or if its down, open webcache.xml under ORACLE_HOME/webcache/. 2. Search for Origin Servers definition by searching for the hostname of one of your origin servers. Make sure SSLENABLED="NONE" if your protocol is HTTP for all your Origin Servers. 3. Once this is done re

Errors in webcache event_log, [10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 378117729099,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxx.in/portal/pls/portal/!PORTAL.wwpob_smd.has_privilege/selfcare/viewlans [10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 1168391710083,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxxxx.in/portal/pls/portal/PORTAL.wwv_setting.render_css?p_lang_type=NOBIDI&p_subscriberid=1&p_styleid=1&p_siteid=0&p_rctx=P These messages are just warning and not errors. These messages are expected if you are using an external SSL server, meaning if SSL is implemented on the load balancer and not on your portal then these messages are expected in webcache event_log.

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH'); 2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO')); 3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST'); 4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES'); 5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES') 6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING')) 7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp) 8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4; 2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7; 3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual; 4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0; 5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1; 6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1; 7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0; 8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc at('dept ',deptno) AS text from emp; OR SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp; 9. SQL

Answers on Basic SELECT statement with Conditions: 1.  SQL> SELECT * FROM emp WHERE ename LIKE 'S%'; 2.  SQL> SELECT * FROM emp WHERE ename LIKE '_L%'; 3.  SQL> SELECT * FROM emp WHERE ename LIKE '%E_'; 4.  SQL> SELECT * FROM emp WHERE ename LIKE '____'; 5.  SQL> SELECT * FROM emp WHERE ename LIKE '%L%'; 6.  SQL> SELECT * FROM emp WHERE ename LIKE '_____%'; 7.  SQL> SELECT * FROM emp WHERE sal BETWEEN 2000 and 3000; 9.  SQL> SELECT * FROM emp WHERE mgr IS NULL OR COMM IS NULL; 10.  SQL> SELECT * FROM emp WHERE mgr IS NULL AND COMM IS NULL; 11.  SQL> SELECT * from emp WHERE job = 'MANAGER'; 12.  SQL> SELECT * from emp WHERE job = 'MANAGER' and deptno in (10,20); 13.  SQL> SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') AND sal >= 1000 AND deptno IN (20,30); 14.  SQL> SELECT * FROM emp where deptno in (20,30) and comm IS NULL; 15.  SQL>

Questions on Basic SELECT statement: Syntax: SELECT *|{[DISTINCT] column|expression [ alias ],...} FROM table [WHERE condition(s) ]; 1. Display all the employees whose name starts with 'S' 2. List the employees name having letter 'L' as the second character 3. List the employees name having 'E' as last but one character 4. List the employees name having exactly 4 letters 5. List the employee whose name is having letter 'L' 6. List the employees name having atleast 5 characters 7. List employees earning between 2000 and 3000 9. List emp who do not have any reporting manager or commision is Null 10. List emp who do not have any reporting manager AND commision is Null 11. List only managers 12. List managers working in dept 10 and 20 13. List all the clerks and analysts with salary atleast 1000 in dept 20 and 30 14. List the employees in dept 20 and 30 who get no commision 15. List employees whose name starts with either 'A' or

(1) How do I list all of my tables? (2) Create one table from another table without copying the data from the first table. (3) How do I select from different user’s tables? (4) What is SQL? (5) What is a function in oracle? (6) What is the default format of date in Oracle? How can I change my default date format? (7) Difference between a)      ROLLBACK & SAVEPOINT b)      ROLLBACK & COMMIT c)       Truncate & Delete d)      Truncate & Drop e)      Delete & Drop f)       PK and (UNIQUE + Not Null) g)      ROUND & TRUNC h)      ROWID & ROWNUM i)         Alternate Key & Candidate Key j)        Simple Key & Composite Key k)      Stored Procedure and Function l)         Stored Procedure and Trigger m)    Equi-Join and Outer Join n)      CHAR & VARCHAR2 o)      CASE Statements & DECODE p)      UNION and UNION ALL 8) What is a view? What are its advantages? 9) What is a synonym? 10) What is PL/SQL? 11) What is an Index? Why it is useful?