Oracle

Oracle : interview Questions

How to get the DB name?

select FN_GET_DBNAME from dual;

How to get list of tables in the oracle database?

select object_name from all_objects where object_type='TABLE'

To get view
select object_name from all_objects where object_type='

To get list of packages
select object_name from all_objects where object_type='PACKAGE';

To get list of triggers

select object_name from all_objects where object_type='TRIGGER';

To get list of synonyms

select object_name from all_objects where object_type='SYNONYM'

How to check number connection in oracle DB?

oracle:select * from V$SESSION;

used to find the number of connection to oracle.

How to change the password:

alter USER sasikala identified by newpassword;

or

use PASSW command in SQL PLUS

what is data dictionary?

The data dictionary holds the information about the database. You can use this to view the privileges assigned to individual. The database information stored in tables and views.

How to get the indexes detail?

user index details available in user_indexes, query it and get the details.

select index_name, table_name from user_indexes where table_name='emp';

sql plus shows only first 80 characters of the LonG columns, how to view more?

you can use command

SET LONG 1000;

How to get version number?

select version from v$instance;

How to get hostname on which the oracle is installed?

select host_name from v$instance

How to get number of threads running in the instance?

select thread# from v$instance;

How to get the instance name?

select instance_name from v$instance;

How to get the current date in Oracle?

select sysdate from dual;

How to get current date and time?

select to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss') from dual;

How to interpret the field value?

use decode function.
select decode(emp.status,'A','Active') from employee;
If the employee status is A then convert that Active and pass on to result.

Oracle: How to check what NLS_Date_Format set in the database?

Use show parameter NLS

It will display NLS parameter Name, Type and Value

or

you can get those values using following query also

SELECT * FROM NLS_SESSION_PARAMETERS;

Oracle - SQL Developer: msvcr100.dll is missing from your computer

I have installed 11g in Windows 7 (64 bit machine). Since the SQL developer wont work with 64 bit jdk.

Installed the 32 bit jdk1.7.0
and changed the ORACLE_HOME\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file SetJavaHome point to 32 bit jdk1.7.0.

Again started the SQL developer, but it throws msvcr100.dll missing. Find that the SQL Developer3.x supports at max jdk1.6.X.

These information is specified in ORACLE_HOME\sqldeveloper\releasenotes. So install the jdk1.6 and make the sqldeveloper.conf SetJavaHome point to this.

other workaround is go to jdk1.7.0 installed path jdk1.7.0\jre\bin copy msvcr100.dll and paste it into ORACLE_HOME\sqldeveloper\sqldeveloper\bin and again try start SQL Developer. It will start.

Oracle - SQL Developer : unable to find the java virtual machine

I have installed oracle 11g (win64_11gR2_client) in windows 7 (64 bit machine). I could able to use sqlplus and connect to database.

But the sql developer was not working. While investigated find that SQL developer won't work with 64 bit version of jdk and it throws unable to find the java virtual machine.

To solve the issue install 32bit/x86 jdk and change ORACLE_HOME\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file SetJavaHome point to 32 bit jdk.

Note: make sure you install 32 bit/ x86 jdk1.6.X or jdk1.5.x

MySQL Limit equivalent in oracle

In MySQL , we use limit clause to get the range of records from query. For example to get 11 th to 20th records.

select * from projects limit 11,20;

The rownum is the oracle equivalent of limit in the MySQL.

select * from projects where rownum>10 and rownum<=20;

Oracle : Where to get the constraints details

To get the constraint details you can either use

1. user_constraints table
select * from user_constraints

If you know the table name then you can specify the condition to limit the result specific to table.

2. user_cons_columns

You can use this data dictionary view also to get the constraint details.

Syndicate content