Difference between 10g and 11g

1.Simplified and improved automatic memory management

2.New fault diagnosability infrastructure to prevent, detect, diagnose, and help resolve critical database errors

3.Invisible Indexes

4.Virtual columns

5.Enhanced security for password-based authentication by enabling use of mixed case in passwords.

6.Tablespace-level encryption

7.Ability to online redefine tables that have materialized view logs

any mistake is there or u wanna give any suggestion so please write to me ............

Create database manually

Create database manually in windows
or Create database through command line

STEP1: c:\> set oracle sid= rose 

STEP2:  create Pfile


 pfile content these parameter 
 "db_NAME=ROSE
   SGA_TARGET=600M
   COMPATIBLE =10.2.0 (your db version)"

Step3: (for windows we need to create instance)


create instance

"C:\>oradim -new -sid ROSE -SRVCOracleServiceROSE -STARTMODE auto -SRVCSTART system -PFILE  C:\ORACLE_HOME\database\INITrose.ORA"



Instance created.


step 4:  c:\>sqlplus  sys as sysdba


step 5:   SQL> startup nomount


 step 6: SQL> create database Rose;




step 7:  create datafile system
               SQL> alter tablespace
               system add
               datafile 'C:\%ORACLE_HOME%\rose\system01.dbf '                size 500m

Tablespace altered.

Step 8: create datafile SYSAUX
              SQL>alter tablespace
             SYSAUX add
             datafile 'C:\%ORACLE_HOME%\rose\SYSAUX01.dbf '             size 500m

Tablespace altered.
 
STEP 9:  Create temporary tablespace
                 SQL> CREATE TEMPORARY tablespace temp
                 tempfile 'C:\%ORACLE_HOME%\rose\tmp01.tmp '     size 100m
 Tablespace created.


 Step 10:  add log files Group
                  SQL>alter database
                  add logfile
                  group 2 'C:\%ORACLE_HOME%\rose\log06.log' SIZE 25M, 
                 group 3 'C:\>ORACLE_HOME%\oradata\rose\log07.log' SIZE 25M
 
Step 11:NOW RUN

SQL>@%ORACLE_HOME%\rdbms\admin\CATALOG.SQL


SQL>@%ORACLE_HOME%\rdbms\admin\CATAPROC.SQL

DONE .......


any mistake is there or u wanna give any suggestion so please write to me ............

Few Oracle SQL Command

Increase the Size of Tablespace



If you want to increase the size of tablespace, its so simple. You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.

OR

For Example

ALTER DATABASE
DATAFILE ‘(datafile path)/u01/oradata/ userdata03. dbf’
RESIZE 200M;

If you don’t have any free space on that partition of disk then you can add another datafile on the other partition  for particular tablespace.

For example

ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

-----------------------------------------------------------------------------------------

Tablespace used in Percentage 

# login oracle with sqlplus as  sysadm user
# conn as sysdba

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
 and round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)  > 75
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
/







any mistake is there or u wanna give any suggestion so please write to me ............

General ORACLE DBA interview Question

1. What is an Oracle Instance?
    An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA).             It  is  the instance that manipulates the data stored in the database.

2. What information is stored in Control File?
The database name
The timestamp of database creation
  The names and locations of associated datafiles and redo log files
  Tablespace information
  Datafile offline ranges
  The log history
  Archived log information
  Backup set and backup piece information
  Backup datafile and redo log information
  Datafile copy information
  The current log sequence number

3. When you start an Oracle DB which file is accessed first?
Oracle first open and reads the initialization parameter file (init.ora).
   


4. What is the Job of SMON, PMON processes?
PMON—It cleaned up the failed processes BY
>> Rolling back the transaction
>> Releasing all currently held table  or row  locks
>> freeing other resource currently held by the user
>>Restart the dead dispatcher

SMON—if the oracle instance fails , any information that in SGA that has not been written  to the disk is cost .
>> the background process smon automatic perform instance recovery when the database is reopened.
>>Instance recovery
•    Roll forward changes in the redolog
•    Open the database for user access
•    Roll back the uncommitted transaction also perform
•    It combines or coalesces, adjacent area of free space in the datafile .
•    Dislocate the temporary segment  

5. What is Instance Recovery?


 Instance recovery
•    Roll forward changes in the redolog
•    Open the database for user access
•    Roll back the uncommitted transaction also perform
•    It combines or coalesces, adjacent area of free space in the datafile .
•    Dislocate the temporary segment 
 
6. What is written in Redo Log Files?

# Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. Log writer does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile. 






7. How do you control number of Datafiles one can have in an Oracle database?


8. How many Maximum Datafiles can there be in an Oracle Database?

In a database there can have maximum 65533 data files.
 
9. What is a Tablespace?

A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.

Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user datais placed in other tablespaces.

There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
10. What is the purpose of Redo Log files?

11. Which default Database roles are created when you create a Database?

12. What is a Checkpoint?

13. Which Process reads data from Datafiles?

14. Which Process writes data in Datafiles?

15. Can you make a Datafile auto extendible. If yes, how?

16. What is a Shared Pool?

17. What is kept in the Database Buffer Cache?

18. How many maximum Redo Logfiles one can have in a Database?

19. What is difference between PFile and SPFile?

20. What is PGA_AGGREGRATE_TARGET parameter?

21. Large Pool is used for what?

22. What is PCT Increase setting?

23. What is PCTFREE and PCTUSED Setting?

24. What is Row Migration and Row Chaining?

25. What is 01555 - Snapshot Too Old error and how do you avoid it?

26. What is a Locally Managed Tablespace?

27. Can you audit SELECT statements?

28. What does DBMS_FGA package do?

29. What is Cost Based Optimization?

30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?

32. Can you make collection of Statistics for tables automatic?

33. On which columns you should create Indexes?

34. What type of Indexes are available in Oracle?

35. What is B-Tree Index?

36. A table is having few rows, should you create indexes on this table?

37. A Column is having many repeated values which type of index you should create on this
column, if you have to?

38. When should you rebuilt indexes?

39. Can you built indexes online?

40. Can you see Execution Plan of a statement.

41. A table is created with the following setting 
storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease

40) What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 9i to 10g?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?




any mistake is there or u wanna give any suggestion so please write to me ............