priya

• May 30th, 2005

 

the ans for thwe above question is the memroy space created for the rows and columns

Ravi Prakash Pichika

• Jun 11th, 2005

 

An Oracle database consists of one or more logical storage units called tablespaces, which collectivley store all of the database's data. 
 
Each tablespace in an ORACLE database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.  
 
For Example::: A simple Oracle Database have one tablespace and one datafile.  
For more Information @ prakash.dba@gmail.com

Jacob

• Jul 9th, 2005

 

TABLESPACE INFORMATION NOTES: 
 
 
Tablespace Name - Name of the tablespace  
Initial Extent - Default initial extent size  
Next Extent - Default incremental extent size  
Min Extents - Default minimum number of extents  
Max Extents - Default maximum number of extents  
PCT Increase - Default percent increase for extent size  
Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)  
Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.  
 
select TABLESPACE_NAME, 
INITIAL_EXTENT, 
NEXT_EXTENT, 
MIN_EXTENTS, 
MAX_EXTENTS, 
PCT_INCREASE, 
STATUS, 
CONTENTS 
from dba_tablespaces 
order by TABLESPACE_NAME  
 
Coalesced Exts 
WAIT STATISTIC NOTES: 
 
 
Tablespace Name - Name of tablespace  
Total Extents - Total number of free extents in tablespace  
Extents Coalesced - Total number of coalesced free extents in tablespace  
% Extents Coalesced - Percentage of coalesced free extents in tablespace  
Total Bytes - Total number of free bytes in tablespace  
Bytes Coalesced - Total number of coalesced free bytes in tablespace  
Total Blocks - Total number of free oracle blocks in tablespace  
Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace  
% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace  
 
select TABLESPACE_NAME, 
TOTAL_EXTENTS, 
EXTENTS_COALESCED, 
PERCENT_EXTENTS_COALESCED, 
TOTAL_BYTES, 
BYTES_COALESCED, 
TOTAL_BLOCKS, 
BLOCKS_COALESCED, 
PERCENT_BLOCKS_COALESCED 
from dba_free_space_coalesced 
order by TABLESPACE_NAME 
 
Usage 
TABLESPACE USAGE NOTES: 
 
 
Tablespace Name - Name of the tablespace  
Bytes Used - Size of the file in bytes  
Bytes Free - Size of free space in bytes  
Largest - Largest free space in bytes  
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%  
 
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 
order by ((a.BYTES-b.BYTES)/a.BYTES) desc 
 
Users Default (SYSTEM) 
SYSTEM TABLESPACE USAGE NOTES: 
 
 
Username - Name of the user  
Created - User creation date  
Profile - Name of resource profile assigned to the user  
Default Tablespace - Default tablespace for data objects  
Temporary Tablespace - Default tablespace for temporary objects  
 
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.  
 
select USERNAME, 
CREATED, 
PROFILE, 
DEFAULT_TABLESPACE, 
TEMPORARY_TABLESPACE 
from dba_users 
order by USERNAME 
 
Objects in SYSTEM TS 
OBJECTS IN SYSTEM TABLESPACE NOTES: 
 
 
Owner - Owner of the object  
Object Name - Name of object  
Object Type - Type of object  
Tablespace - Tablespace name  
Size - Size (bytes) of object  
 
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace  
 
select OWNER, 
SEGMENT_NAME, 
SEGMENT_TYPE, 
TABLESPACE_NAME, 
BYTES 
from dba_segments 
where TABLESPACE_NAME = 'SYSTEM' 
and OWNER not in ('SYS','SYSTEM') 
order by OWNER, SEGMENT_NAME 
 
Freespace/Largest Ext 
FREE, LARGEST, & INITIAL NOTES: 
 
 
Tablespace - Name of the tablespace  
Total Free Space - Total amount (bytes) of freespace in the tablespace  
Largest Free Extent - Largest free extent (bytes) in the tablespace  
 
select TABLESPACE_NAME, 
sum(BYTES) Total_free_space, 
max(BYTES) largest_free_extent 
from dba_free_space 
group by TABLESPACE_NAME

onkar

• Oct 3rd, 2005

 

tablespace is nothing but a logical object comprisiing of one or more data files which actually stores the data.

Adityendu Panda

• Jan 12th, 2006

 

A tablespace is a collection of one or more datafile.

RAVI SALUJA

• Jan 10th, 2007

 

A tablespace is a collection of one or more datafiles and each datafile consists of Datablocks, Extents and Segments

Thanks

Taj_N

• Jul 16th, 2007

 

Oracle Stores logically data in tablespaces, every database atleast one tablespace accumulated with it, tablespace is two types
System Tablespaces:- created automatically when database is created by oracle server, to hold system objects, data dictionary and not user data.
Non-System Tablespace:- created by DBA to store UNDO, TEMP, and APPLICATION objects.

tablespace is consist of :-
One or more segments, each segment composed of one or more extents, and extent is further consist of data blocks, and this is last stage.

sivaraju.sateesh

 Profile Answers by sivaraju.sateesh 

• Apr 5th, 2009

 

Tablespace is the logical component of a database. It is the combination of one or more segments as each segment is formed by one Or more extents. On the other hand tablespace consists of one or more data files in which the data resides. So a tablespace is the combination of logical and physical structure.

cmanne

 Profile Answers by cmanne 

• Sep 2nd, 2009

 

A tablespace in Oracle database is logical grouping of physical datafiles.

Dharma Kotha

 Profile Answers by Dharma Kotha 

• Oct 10th, 2011

 

Tablespace is a logical location for data in oracle architecture, contains no.of datafiles.

Sharfuddin

• Oct 18th, 2011

 

A tablespace is a logical entity which links between sysdba and physical file.And also it can have many datafile.
Syntax :- create tablespace datafile '' size 20m autoextend on;

Kiranpuli

• Nov 10th, 2011

 

Tablespace is a logical storage structure in Oracle database
In tablespaces ->Segments->Extents->Blocks data is stored in the manner as mentioned

In which data is stored in Data files(physical)

kamesh

• Nov 29th, 2011

 

Table space is the space given to any of data file to get exist into the database provided no two same data files that should exist in any table space.

ANIL

• Dec 13th, 2011

 

Tablespace is a logical content of data
It is also called collection of datafiles
They are of two types

Small tablespace
Big tablespace

For small tablespace we can create many datafile
For big tablespace we can create only one datafile

dba_tablespaces,dba_data_files,v$datafile etc are data dictionary views for tablespaces

Dulluh

• Apr 18th, 2015

 

Its wrong if you say a tablespace is a collection of datafiles you will be more on physical storage and forgetting the logical spects.Tablespace is an abstraction of physical and logical storage.A tablespace also spreads a segment. 

venkat

• May 5th, 2015

 

Tablespace is a logical storage structure in oracle database which containts datafiles

shruthi reddy

• Jun 2nd, 2015

 

Oracle use tablespace for logical data storage. Physically, data will get stored in datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles.

ahmed

• Jul 10th, 2015

 

Tablespace is a logical structure.

NARESH NADHAN

• May 4th, 2016

 

Simply I say DATABASE is divided into some logical storage units that is called TABLESPACE.This is logical storage.And a TABLESPACE can have 1024 datafiles.

Ashok

• Jul 13th, 2017

 

Tablespace is a logical storage device, the database stores the data physically in datafiles and logically in tablespaces