-
How do you know that fragmentation is occurred and how do you fix it. ...?
subha gunasekaran
- Jun 5th, 2008
To see fragmentation:select sum(bytes)/1024 from dba_data_files where tablespace_name='';output: 10select sum(bytes)/1024 from dba_segments where tablespace_name='';ouput :...
sjpunshi
- Sep 25th, 2007
Query dba_segments, dba_free_space for the tablespace and table, if too many extents are avaiable, yet space availability is a problem, implies that the table is fragmented. Solution, export the segment. recreate and import, fragmentation is eliminated.
-
-
-
-
Can RAC structures have different operating system nodes in cluster. e.g one node linux and other node is Windows. if not then why?
arulibra
- Jan 13th, 2010
No, you cant have different operating system in different nodes. All the nodes should be of same hardware, same OS, same version of cluster ware and RAC software. Even the oracle software and operatin...
parthokonar1
- Sep 26th, 2007
No it cannot be all the nodes have to be same os/platform. before oracle installation RAC software has to be installed. for load balancing etc
-
Why minimum extents are required in roll back segment?
dbashashi
- May 20th, 2008
It is two. It is the minimum no of extents that we can assign to roll back segment.The max extents depends on the version of Oracle being used.
phani99_sakar
- Feb 19th, 2008
The minimum requirement for a DB or to start an instance it is mandatory to have atleast one roll back segment( if we or not ginving it oracle will take it from sys...) that is minimum.....and mandatory.............
-
If you 100 table spaces so how to find which table space is growing and which one is volatile?
narendra05
- Oct 2nd, 2009
In 10g the AWR repository keeps track of data for 60 days by default and can store the information of tablespace growth for this period.Alternativey you can set the threshold in entreprise manager for a tablespace to respective which can trigger email alert if configered.
hemant.agarwal03
- Mar 17th, 2008
Select sum(bytes),TABLESPACE_NAME from dba_segments group by TABLESPACE_NAMEorder by sum(bytes) desc/
-
Two Phase Locking Protocol
Explain the concept of two phase locking protocol with an example?
dhs01
- Apr 15th, 2009
Not sure of the particular terminology but 2 phase locking I believe would be similar to 2 phase commit where you must first make sure that everybody involved is ready, able, and willing (phase 1) then the next phase is when it actually happens (phase 2).
rpeters83
- Dec 9th, 2008
I'm no DB admin but I'll give it a shot. It's a protocol gauranteed to allow serialized actions on a database by having a locking phase and an unlocking phase. Meaning, during locking, no other transactions may be performed on the database until it is unlocked.
-
Redo Log
If we are running in NOARCHIVELOG mode and my all redo log files are full and data is still not committed by the current session. What activity will Oracle perform and what will happen with the un-comitted data?
ianilreddy
- Aug 7th, 2010
In database whatever changes we made that going to store in redologs. The changes are record in redolog buffer when certain conditions lgwr will write to redologs 1) When a transaction commit 2)&...
MAbdullah47
- Feb 5th, 2009
NONARCHIVE log will Protect the DB from Instence faliure but not from Media failure.The Redo files will be covered until the last backup taken , but after that all uncommited datae will be lost for the period (after the last backup) taken.
-
Target Database RMAN Backup
What are the steps required to enable the RMAN backup for a target database?
bapiora
- Jun 29th, 2009
Open a RMAN session and connect with target database, if you follow standalone command method just issue BACKUP DATABASE?command its allocates channel and do the backup, but if you follow batch comma...
anuradhad
- Feb 24th, 2009
Steps required for RMAN1)create a user to hold the recovery catalog2)Grant connect,resource,recovery_catalog_owner to the user3)Create recover catalog4)Register database.Each database to be backed up by RMAN must be registered
-
Power Failure
What is the effect on database at the time of total power failure when an UPDATE query is under execution in Oracle 9i?
ianilreddy
- Aug 7th, 2010
When database is restarted, SMON will perform instance reecoveryRoll forward commited transactionsRoll back uncommited transactions using undo information.
Ram Sangwan
- Jun 24th, 2009
When the power failure occurs while an update statement is running. When the database is restarted it does an automatic recovery, when it finds the update statements pending, it performs a rollback of the update statement.Regards-Ram
-
NoArchiveLog Mode
A Database is running in NoArchiveLog mode then which type of backups you can take?
mangeshchede
- Mar 21st, 2010
If your Databse is in No Archivelog Mode then you must take a Cold backup of your Database.
naveen_rc
- Aug 4th, 2009
If your database is in NOARCHIVELOG mode, then the only type of backups that you should perform are closed, consistent (COLD BACKUP), whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are also not required.
-
Database Buffer Blocks
How many type of database buffer blocks are available? What are they?
shekar2130
- Jul 15th, 2010
There are 3 types of buffers present:1. Free buffer2. Pinned buffer,3. Dirty buffer
sharmme
- Feb 17th, 2010
Following are the types of Buffer blocks available.Pinned - Marked not to be released as per LRU algorithm. These are marked as required for further writes/changes/reads by transactions.Dirty - Change...
-
Parsing Process
Which process creates parsing?
himaVemula
- Nov 18th, 2014
Hard parse is the process that creates parsing
sharmme
- Feb 17th, 2010
Server process dedicated to the user both in shared and dedicated server connection configuration.
-
Control Number of Datafiles
How do you control number of Datafiles one can have in an Oracle Database?
-
How to recover the datafile without having backup?
Take cold backup and recover
shoaib1786
- Nov 5th, 2011
Just create the datafile with same location mention in ur controlfile and recover the datafile ... Its simple.
naveen
- Sep 19th, 2011
Alter database create datafile 'and give the datafile location and /name' by using this command we can recover the datafile without backup also
-
Oracle - Change Columns to row
What is the query to change the rows into column and columns into row
Manas Kumar Hati
- Oct 31st, 2011
Unsing Pivot Query
-
Which tool you have used for monitoring the databases?
Akbar Yuldoshev
- Jun 18th, 2016
Oracle Enterprise Manager
-
Slow Performance in Production Database
What is the solution if some users are encountering slow performance in production db?
-
How is locking implemented?
Raghunathan Asari
- Jul 22nd, 2007
Whenever an SQL is issued for execution, DB2 checks for lock avoidance by checking the SQL.Eg. SELECT ...... WITH UR or BIND with ISOLATION (UR) (UR Uncommitted Read)If not DB2 checks&nbs...
Culver_lake
- Mar 19th, 2006
Locking in most modern database management systems is implemented implicitly. That is you never have to ask for a lock nor will you ever fail to release a lock. In DB2 this is all controll...
Database Admin Interview Questions
Ans