Skip to main content

ORACLE TABLESPACES

Creating Tablespaces

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline.
The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace".

3 Tablespaces, Datafiles, and Control Files

This chapter describes tablespaces, the primary logical database structures of any Oracle database, and the physical datafiles that correspond to each tablespace.
This chapter contains the following topics:

Introduction to Tablespaces, Datafiles, and Control Files

Oracle Database stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship.
Figure 3-1 Datafiles and Tablespaces
Description of Figure 3-1 follows
Description of "Figure 3-1 Datafiles and Tablespaces"
Databases, tablespaces, and datafiles are closely related, but they have important differences:
  • An Oracle database consists of at least two logical storage units called tablespaces, which collectively store all of the database's data. You must have the SYSTEM and SYSAUX tablespaces and a third tablespace, called TEMP, is optional.
  • Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle Database is running.
  • A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
This section includes the following topics:

Oracle-Managed Files

Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames. Oracle Database internally uses standard file system interfaces to create and delete files as needed for the following database structures:
  • Tablespaces
  • Redo log files
  • Control files
Through initialization parameters, you specify the file system directory to be used for a particular type of file. Oracle Database then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.
See Also:

Allocate More Space for a Database

The size of a tablespace is the size of the datafiles that constitute the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.
You can enlarge a database in three ways:
  • Add a datafile to a tablespace
  • Add a new tablespace
  • Increase the size of a datafile
When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace. Figure 3-2 illustrates this kind of space increase.
Figure 3-2 Enlarging a Database by Adding a Datafile to a Tablespace
Description of Figure 3-2 follows
Description of "Figure 3-2 Enlarging a Database by Adding a Datafile to a Tablespace"
Alternatively, you can create a new tablespace (which contains at least one additional datafile) to increase the size of a database. Figure 3-3 illustrates this.
Figure 3-3 Enlarging a Database by Adding a New Tablespace
Description of Figure 3-3 follows
Description of "Figure 3-3 Enlarging a Database by Adding a New Tablespace"
The third option for enlarging a database is to change a datafile's size or let datafiles in existing tablespaces grow dynamically as more space is needed. You accomplish this by altering existing files or by adding files with dynamic extension properties. Figure 3-4 illustrates this.
Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles
Description of Figure 3-4 follows
Description of "Figure 3-4 Enlarging a Database by Dynamically Sizing Datafiles"


References
Labels:

Comments

Post a Comment

Popular posts from this blog

How to make the email Address Dummy in Oracle Database?

How to make the email Address Dummy in Oracle Database? Query to make the email Address as dummy, update <tablename> set <Columnname> = '@' || <Columnname> where <Columnname>  is not null; Query to Remove the email Address as dummy, update <tablename> set <Columnname>= substr(<Columnname>,2,length(<Columnname>)) where <Columnname> is not null;
Post a Comment
Read more

What is (Computer) Networking?

In the world of computers, networking is the practice of linking two or more computing devices together for the purpose of sharing data. Networks are built with a mix of computer hardware and computer software. Area Networks Networks can be categorized in several different ways. One approach defines the type of network according to the geographic area it spans.Local area networks (LAN), for example, typically span a single home, school, or small office building, whereas wide area networks (WAN), reach across cities, states, or even across the world. The Internet is the world's largest public WAN. One way to categorize the different types of computer network designs is by their scope or scale. For historical reasons, the networking industry refers to nearly every type of design as some kind of area network. Common examples of area network types are: LAN - Local Area Network WLAN - Wireless Local Area Network WAN - Wide Area Network MAN - Metropolitan Area Network SAN - ...
Post a Comment
Read more

List of FTP commands for the Microsoft command-line FTP client

Command-line options As you're starting the program from a DOS prompt: ftp [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-w:windowsize] [computer] -v  - Suppresses  verbose  display of remote server responses. -n  - Suppresses auto-login upon initial connection. -i  - Turns off interactive  prompting  during multiple file transfers. -d  - Enables  debugging , displaying all ftp commands passed between the client and server. -g  - Disables filename  globbing , which permits the use of wildcard chracters in local file and path names. -s:filename  - Specifies a text file containing ftp commands; the commands will automatically run after ftp starts. No spaces are allowed in this parameter. Use this switch instead of redirection (>). -a  - Use any local interface when binding data connection. -w:windowsize  - Overrides the default transfer buffer size of 4096. computer  - Specifies the computer name o...
Post a Comment
Read more