ماهى انواع tablespaces in database ?
وكيف يتم انشائها؟
وما هى الفائده الاساسيه منها ؟
جزاكم الله خيرا

ماهى انواع tablespaces in database ?
وكيف يتم انشائها؟
وما هى الفائده الاساسيه منها ؟
وكيف يتم عمل drop له ؟
جزاكم الله خيرا

انا بعد البحث عرفت اجابه السؤال الاول وهو ان انواع tablespace هى
1)system tablespace.

2)temp tablespace.

3)tools tablespace

4)users tablespace.

5)rollback tablespace.

6)data and index tablespace

اتمنى من الله ان تكون الاجابه صحيحه والمناقشه فى باقى الاسئله

الأخ الفاضل أحمد

تحية عطرة وبعد

سؤالكم
-- انواع tablespaces in database
--وكيف يتم انشائها
--وما هى الفائده الاساسيه منها
--وكيف يتم عمل drop له ؟
-- وحسب بريدكم الموقر لي كيف يتم عمل control files

انواع tablespaces in database

أولا تعريفها
A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.
Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.

وكذلك
Database is divided into one or more logical storage units known as table space. The table space is created by database administrator. After the table space gets created by administrator tables can be created by users in their table space in other words tables can be created in the table space provided for the users.

اذن قاعدة البيانات فيزيائياً تتقسم إلى ملفات تخزينية يمكن أن تشاهد عن طريق نظام التشغيل وهى ملفات الData files ، وكذلك قاعدة البيانات تنقسم إلى وحدات تخزين منطقية (Logical) وتسمى هذه الوحدات Tablespace
الTablespace وهو وحدة منطقية يحتوى على Data file واحد أو أكثر وكل Data file ينتمى فى الحقيقة إلى Tablespace واحد .
يمكن إنشاء Bigfile Tablespaces بحيث يحتوى على ملف Data file واحد ولكن كبير جداً بحيث يكون اكبر من 4GB .
الTablespace يتكون من مجموعة من الوحدات المنطقية الاصغر وهى تسمى Segments وهى وحدات منطقية يتكون منها الTablespace بحيث تنتمى الSegment لTablespace واحد فقط وهى تتكون من مجموعة من الوحدات المنطقية الأصغر التى تسمى Extents وهى ايضاً وحدات منطقية تنتمى كل Extent لSegment واحد وتتكون أيضا الExtents من مجموعة أخرى أصغر وهى أصغر وحدة منطقية تسمى الBlocks .
وعند إنشاء قاعدة البيانات يتم تحديد حجم الBlock لقاعدة البيانات ، ويمكن أن يكون حجم الBlock 3BK او 5BK أو غيره وأكبر حجم للBlock يتوقف على نظام التشغيل ، وقبل الاصدار Oracle 9i Release كانت قاعدة البيانات تعمل على حجم واحد للBlock وابتداءً من الإصدار Oracle 9i Release أمكن لقاعدة البيانات العمل بأحجام مختلفة من اللBlock

لماذا هي؟

هي المساحة التي تخزن فيها أوراكل بياناتها وتحزها لنفسها ضمانا لوجود مساحة لها

أنواعها
There are three types of tablespaces in Oracle:

* Permanent tablespaces
* Undo tablespaces
* temporary tablespaces

كيفية عملها
A tablespace is created with the create tablespace sql command.

ولعملها يرجي النظر في هذا الموضوع

http://www.araboug.org/ib/index.php?showtopic=41948&pid=192220&start=&st=#entry192220


فائدتها لعلك عرفت الان أهميتها كما أسلفت لك

حذفها
DROP TABLESPACE tbs_01
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

كذلك لحذف Operating System Files
DROP TABLESPACE tbs_02
INCLUDING CONTENTS AND DATAFILES;

تغيير اسمها
alter tablespace ts_user rename to ts_user_01;

بعض المفاهيم
The system tablespace
The system tablespace is always available when a database is open (it cannot be taken offline).
The system tablespace stores the data dictionary (or their base tables, respectively).

The sysaux tablespace
The sysaux tablespace is new with Oracle 10g. It is used to store database components that were stored in the system tablespace in prior releases of the database.
Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g.
Additionally, it is the place where automatic workload repository stores its information.

Bigfile tablespaces
This is a feature of Oracle 10g. A bigfile tablespace contains only one datafile (or tempfile) which can be as big as 2^32 (=4GB) blocks.

create bigfile tablespace beeeg_ts data file '/o1/dat/beeeg.dbf' size 2T

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (which is the default setting since Oracle 9i). There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
The system and sysaux tablespace cannot be created as bigfile tablespace.
Bigfile tablespaces should be used with automatic storage management, or other logical volume managers that support dynamically extensible logical volumes, striping and RAID.


Smallfile tablespaces
A smallfile tablespace is a traditional tablespace that can contain up to 1022 datafiles.


Default tablespaces
An Oracle database can be configured with a default tablespace and a default temporary tablespace. These are used for users who are not configured with default tablespaces or default temporary tablespaces.

عرضها من خلال dba_tablespaces

فوائد temporary tablespace:


Temporary tablespace group has the following benefits

* A temporary tablespace allows multiple default temporary tablespaces to be specified at the database level.

* A temporary tablespace allows the user to use multiple temporary tablespaces in different sessions at the same time.

* A temporary tablespace allows a single SQL operation to use multiple temporary tablespaces for sorting.


كيفية عمل control files
الجملة المستخدمه هي

CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;

لو كنت تعمل علي ويندور استبدل المسارات مثل '/u01/oracle/prod/system01.dbf'
ستكون
'c:\oracle\prod\system01.dbf'
أما الخطوات فهي


Complete the following steps to create a new control file.

1.

Make a list of all datafiles and redo log files of the database.

If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';

If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.
2.

Shut down the database.

If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
3.

Back up all datafiles and redo log files of the database.
4.

Start up a new instance, but do not mount or open the database:

STARTUP NOMOUNT

5.

Create a new control file for the database using the CREATE CONTROLFILE statement.

When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (step 8). You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.
6.

Store a backup of the new control file on an offline storage device.
تعلم كيف يمكنك عمل "Backing Up Control Files" for instructions for creating a backup.
7.

Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the new name.
8.

Recover the database if necessary. If you are not recovering the database, skip to step 9.

If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.


9.

Open the database using one of the following methods:
*

If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.

ALTER DATABASE OPEN;

*

If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use

تحياتي
أحمد

افادك الله ربنا يكرمك والله افتدنى بهذه المعلومات الشيقه ربنا يكرمك ويجعله فى ميزان حسناتك
ونشكرك على الاهتمام ربنا يبارك فيك

واياكم ولا تتردد في اي سؤال ترغب في توجييه لكن بعد أن تبحث عن حلهه في المنتدي وخارجه


تمنياتي لك بدوام التوفيق