Query to view assigned tablespace and its associated datafiles to the database users

########RETURN NULL FOR THE TABLESPACE NOT ASSIGNED TO USERS#######
Here
ts# represents tablespace number
file# represents datafile number

select * from
(select c.username username,'default tablespace' type,a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$datafile b,dba_users c
where a.ts#=b.ts# and
a.name=c.default_tablespace(+)
union all
select c.username,'temporary tablespace' type, a.ts# ts#,a.name tablespace,b.file# file#,b.name name from v$tablespace a, v$tempfile b,dba_users c
where a.ts#=b.ts# and
a.name=c.temporary_tablespace)z
order by z.username,z.type;

Creating Database Manually

Here is the sample to create the oracle 9i database manually.

Steps:
1) run oradim utilily to create oracle agent service from command prompt.
eg oradim - new -sid sidname -intpwd password -maxusers no_of _users -startmode a
2) run orapwd utility to create password file in the default %ORACLE_HOME%\DATABASE folder
3) add folder named oradata and SID in %ORACLE_BASE% directory.
4) add folder named bdump, udump,cdump,pfile,create folder inside %ORACLE_HOME%\admin\SID directory
5) prepare initialisation file initSID.ora OR SPFILESID.ora IN %ORACLE_HOME%\DATABASE direcotry
6) Using Oracle Managed Files set
db_create_file_dest='%ORACLE_BASE%\oradata\SID'
db_create_online_log_dest_n='%ORACLE_BASE%\oradata\SID'
replace n with integer starting 1 upto 5 destinations
7) startup database in nomount stage from prompt
startup nomount
8) run the script below:


#########DATABASE CREATION USING DIRECTORY PATH NAMES
CREATE DATABASE database_name
logfile
group 1 ('%ORACLE_BASE%\oradata\SID\redo01.dbf') size 10m,
group 2 ('%ORACLE_BASE%\oradata\SID\redo02.dbf') size 10m,
group 3 ('%ORACLE_BASE%\oradata\SID\redo03.dbf') size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile '%ORACLE_BASE%\oradata\SID\system01.dbf' size 500m
undo tablespace UNDOTBS1
datafile '%ORACLE_BASE%\oradata\SID\undotbs.dbf' size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile %ORACLE_BASE%\oradata\SID\temp01.dbf' size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

#########OMF MANAGED DATABASE CREATION

CREATE DATABASE database_name
logfile
group 1 size 10m,
group 2 size 10m,
group 3 size 10m
maxlogfiles 10
maxlogmembers 3
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile size 500m
undo tablespace UNDOTBS1
datafile size 200m
autoextend on next 5120k maxsize unlimited
default temporary tablespace temp
tempfile size 200m
autoextend on next 5120k maxsize 500m
character set US7ASCII
national character set AL16UTF16
set TIME_ZONE='America/New_York';

9) run script catalog.sql and catproc.sql as a sys user from %ORACLE_HOME\rdms\admin directory
10) run script pupbld.sql as a system user from %ORACLE_HOME\sqlplus\admin directory

This completes the database manual database creation.