To get Oracle Version and Release from SQL

SQL> show release
release 1002000300

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

Finding Linux and Windows bit version from sql.

finding the linux bit version, i mean 32-bit or 64-bit ?

SQL> select metadata from sys.kopm$ ;
METADATA
------------------------------

--------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405
0607080823472323081123081141B023008303670367130000000000000000000000000000000000
0000000000000000000000000000000000000000

B023----------32BIT
BO47----------64BIT

Which session generating so many archives

This is the query for which session is generating so many arch files

select c.username,c.sid,c.serial#,c.

process,to_char( b.value/1024,
'999,999,999' ) || ' kbytes' kbytes from v$statname a, v$sesstat b,
v$session c where a.statistic# = b.statistic# and b.sid = c.sid and
a.name = 'redo size'
and c.username is not null
and b.value > 0
order by 5

Working with OMF Tablespaces

Using OMF (Oracle Managed File), you don’t explicitly name datafiles or temp files; the database does this for you. To enable OMF, set the initialization parameter DB_CREATE_FILE_DEST to the directory where you want the database to create and manage your data and temp files.

ALTER SYSTEM SET
db_create_file_dest=’D:\sample\OMF’
SCOPE=BOTH;

When creating a tablespace with OMF, you simply omit the filename:
CREATE TABLESPACE ; [OR] CREATE BIGFILE TABLESPACE ;

By default the datafile created using OMF will have auto extend enabled and be 100MB unless you specify a different size.

Reference: Sybex Oracle 10g Admin I Study Guide

Creating Bigfile/Smallfile Tablespaces

You create tablespaces using CREATE DATABASE or CREATE TABLESPACE statement. Several choices when creating a tablespace include: whether to make tablespace a bigfile or smallfile, whether to manage extents locally or with dictionary, and whether to manage segment space manually or automatically. Additionally there are tablespaces for temporary and undo segments.

Bigfiles are intended for very large databases. When a very large database has thousands of read/write datafiles, operations that must update the datafile headers, such as checkpoints, can take relatively very long time. If you reduce the number of datafiles, this operation can complete faster.
CREATE BIGFILE TABLESPACE
DATAFILE ‘/sample/sample/dbf’ SIZE 25G;

Bigfile tablespace is new to Oracle 10g. Bigfile tablespaces can be as many as 232 data blocks in size. So, a bigfile tablespace using 8KB data blocks can be as much as 32 TB in size. Bigfile tablespace can have only one datafile.

Smallfile tablespace is the new name for old Oracle tablespace datafile option. With a smallfile tablespace you can have multiple datafiles for a tablespace. Each datafile can be as much as 222 data blocks in size. So, a smallfile tablespace using 8KB data blocks can be as much as 32 GB in size.
CREATE TABLESPACE
DATAFILE ‘/sample/sample/dbf’ SIZE 25G;

Reference: Sybex Oracle 10g Admin I Study Guide

Tablespace and Tablespace Management

Common use of tablespace is performance related. You can place tables into a tablespace that sits on datafiles residing on one set of disk drives and placing indexes for those tables into another tablespace that sits on datafiles residing on different disk drives. This reduces disk contention that might otherwise occur on heavily accessed tables.

Reference: Sybex Oracle 10g Admin I Study Guide

more

Rename datafile in Tablespace:

select name,status from v$datafile;

alter tablespace TEST offline;

alter tablespace TEST rename datafile '/database/test1/files/test01.dbf'
to '/database/test1/files/test02.dbf';

alter tablespace TEST online;

Other commands:

select file_name,status from dba_data_files;

http://www.md.chalmers.se/Support/Software/Databases/server.901/a90117/dfiles.htm#7527

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm

Segments, Extents, and Data Blocks

• Segments (highest-level of space management) reside in only one tablespace
• Each segment is constructed from one or more extents
• Each of these extents can reside in only one datafile
• An extent is composed of contiguous set of data blocks, which is at the lowest-level of space management
• Data block size is a tablespace attribute
• SYSTEM and SYSAUX tablespaces have the standard data block size defined at the creation time by the db_block_size initialization parameter
• Other tablespaces can have different data block sizes defined at tablespace creation time
• A database can have as many as five data block sizes

Reference: Sybex Oracle 10g Admin I Study Guide