Oracle Questions
1. How to know the name of the database?
Answer: Login as sys or system. Then run the query.
select name from v$database;
V$database gives all the information about the database;
2. How to know user information?
Answer: select username,user_id,password from dba_users;
dba_users gives information about the users;
3. How to Change user password?
Answer: Alter USER <USERNAME> IDENTIFIED BY <PASSWORD>;
Alter user hr identified by 'sandip';
4. What is the database holding Capacity of Oracle ?
Answer: Database holding capacity of oracle 9i is 500 pb(peta bytes)
Database holding capacity of oracle 10 g is 8 trillion tera bytes.
5. What are the components of physical database structure of Oracle database?
Totally there are 6 files(components) of physical database structure.3 mandatory & 3 optional.
Three mandatory files are :
1> datafile : store actual data
2> control file : strores stuctural & status information of database.
3> redolog file : stores changed/committed data
Three optional files are :
4> parameter file : strores all size related parameters note : this file is mandatory for a1st time u create a database, then it is optional.
5> archivelog file : its offline copy of redolog files
6> password file : used to make normal user to behave as a super user.
6. How to create Control File in ORACLE?
Answer: ALTER DATABASE BACKUP CONTROLFILE TO TRACE (This create the backup in the trace file in UDUMP)
or
ALTER DATABASE BACKUP CONTROLFILE TO 'C:\SOMEWHERE\CONTROL01.bkp';
Then Open the trace file and copy the create control file line and run the script while the database in mount mode.
ALTER DATABASE OPEN RESETLOGS
7. V$Views for Performance Tuneing
Answer:
v$verson:- Tell the version of oracle database;
select * from v$version;
v$thread:- To know the name of the SID
select instance from v$thread;
8. What is the process of inserting data from one database to other?
Answer: COPY FROM USER/PASSWORD@DATABASE
copy from oraivr/makeit2ez@airtel to oraivr/makeit2ez@reldb create trnreliance using select * from relaincetest;
9. v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
10. How to know login user information in oracle?
Answer: First find the information of the all the login user using the following query.
SQL> select username,sid,serial# from v$session;
Now Kill the user using the following sql
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
SQL> ALTER SYSTEM KILL SESSION '10,43';
11. How to auto start the ORACLE database and shutdown during OS start and stop?
Answer: AUTO STARTING THE ORACLE DATABASE AT WINDOWS STARTUP
1. OPEN REGISTRY
2. HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOME0
3. set ORA_SID_AUTOSTART=TRUE (To auto start the Database during windows startup)
4. ORA_ORCL_SHUTDOWN=TRUE (To auto shut down the Database during windows shutdown)
Note: By Default windows set this parameter to false.
12. How to select random columns in oracle?
Answer: Use this query to select a random record from Oracle table.
SELECT * FROM (SELECT * FROM TABLENAME ORDER BY dbms_random.value) WHERE rownum = 1;
13. How to unlock Oracle user account
Answer: Here's how to lock or unlock Oracle database user accounts.
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;
14. How to enable trace in Oracle?
Answer: 1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;
2. Enable trace at session level
to start trace:
ALTER SESSION SET sql_trace = TRUE;
to stop trace:
ALTER SESSION SET sql_trace = FALSE;
- or -
EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);
- or -
EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;
3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
SELECT * FROM v$session WHERE osuser = OSUSER;
to start trace:
EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);
to stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);
- or -
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);
15. How to switch to another user
Answer: Need to become a different Oracle database user? There is a way to login into a another user's account without knowing their password:
1. Find out the encrypted password for the user
SQL> select username, password from dba_users where username = 'USERNAME';
USERNAME PASSWORD
------------ ------------------------------
USERNAME 6B4712549D4DA963
Save the encrypted password value somewhere i.e. in a text file
2. Change user's password to a temporary password
SQL> alter user USERNAME identified by 'password';
3. Do whatever you had to do as that user
4. Change user's password back to the original
SQL> alter user USERNAME identified by values '6B4712549D4DA963';
16. How to force user choose a new password ?
Answer: Run this from sqlplus to force Oracle user choose a new password when he/she logs in next time:
SQL> ALTER USER username PASSWORD EXPIRE;
17. How to load data from txt file to oracle?
Answer: First create a table
create table upboardtest
(
Roll varchar2(20) PRIMARY KEY,
NAME varchar2(20),
SUB1 varchar2(10),
MARKS1 varchar2(10)
);
create a txt file for the records
e.g. Record.txt
12345,sandip,English,20
12347,sandy,Math,20
12347,Rajesh,English,20
12348,Raju,Math,20
Now create a control file
e.g. Record.ctl and write the following lines in it
LOAD DATA INFILE 'E:\Record.txt.txt' INTO TABLE upboardtest FIELDS TERMINATED BY ',' (ROLL,NAME,SUB1,marks1)
Now in the command prompt type the following command
sqlldr userid=oraivr/makeit2ez@reldb control=E:\record.ctl
SQLLDR is the utility that load data from text to oracle database
18. Process To Create TABLESPACE
CREATE TABLESPACE "INDEX"
LOGGING
DATAFILE 'D:\DNORA\DATAFILE\INDEX.ora' SIZE 20M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
19. ALTER TABLESPACE
ALTER DATABASE
DATAFILE 'D:\DNORA\DATAFILE\INDEX.ORA' AUTOEXTEND
ON NEXT 3M
20. USER CREATE IN ORACLE
CREATE USER "ORAIVR" PROFILE "DEFAULT"
IDENTIFIED BY "makeit2ez" DEFAULT TABLESPACE "USER"
QUOTA UNLIMITED
ON "INDEX"
QUOTA UNLIMITED
ON "USER"
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "ORAIVR";
GRANT "USER_ROLE" TO "ORAIVR";
21. Process to select a range data from oracle tables.
Ans: select phone from ( select a.*, rownum rnum from ( select phone from reldata order by phone) a where rownum <= 64999 ) where rnum >= 1;