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 ............