search
HomeDatabaseMysql TutorialOracle表空间管理

2 表空间 Oracle磁盘管理中的最高逻辑层是表空间,Oracle11g中必须创建的4个表空间是SYSTEM, SYSAUX,TEMP, UNDOTBS1。 2 SYSTEM:存储数据字典等,pl/sql代码等。 2 SYSAUX:存储与数据库选项相关的数据 2 TEMP:用于大的排序操作 2 UNDUTBS1:为读一致性和

   2 表空间

  Oracle磁盘管理中的最高逻辑层是表空间,Oracle11g中必须创建的4个表空间是SYSTEM, SYSAUX,TEMP, UNDOTBS1。

  2 SYSTEM:存储数据字典等,pl/sql代码等。

  2 SYSAUX:存储与数据库选项相关的数据

  2 TEMP:用于大的排序操作

  2 UNDUTBS1:为读一致性和恢复的目的,存储事务信息。

  表空间的下一层是段,一个段只能驻留在一个表空间中;一个或多个区可以组成一个段,每个区只能驻留在一个数据文件中;一组连续的数据块可以组成一个区。如果要查询表空间与对应的数据文件的相关信息,可以从dba_data_files数据字典中查询表空间及其包含的数据文件,举例如下:

  SQL> col tablespace_name for a10;

  SQL> col file_name for a50;

  SQL> col bytes for 999,999,999;

  SQL>Select tablespace_name,file_name, bytes fromdba_data_files order by tablespace_name;

  1、 SYSTEM表空间

  SYSTEM表空间存放内部数据和数据字典,主要存放SYS用户的各个对象和其他用户的少量对象。例如:查询USERS表空间中存放的数据对象及其类型和拥有者。

  SQL>col owner for a10;

  SQL>col segment_name for a30;

  SQL>col segment_type for a20;

  SQL>select segment_type,segment_name,owner fromdba_segments where tablespace_name='USERS';

  2、 SYSAUX表空间

  SYSAUX表空间充当SYSTEM表空间的辅助表空间,,主要用于存储除数据字典以外的其他数据对象。例如,查询SYSAUX表空间所存放的用户及其所拥有的对象数量:

  Select owner as 用户, count(segment_name) as 对象数量 fromdba_segments where tablespace_name='SYSAUX' group by owner;

  3、 创建表空间

  创建表空间的语法如下:

  Create [smallfile | bigfile] tablespace tablespace_name

  Datafile '/path/filename' size num[k|m] reuse

  ['/path/filename' size num[k|m]reuse]

  [, …]

  [autoextend [on|off] next ] num[k|m]

  [maxsize [unlimited | num[k|m]]]

  [mininum extent num[k|m]]

  [default storage storage]

  [online | offline]

  [logging | nologging]

  [permanent | temporary]

  [extent management dictionary | local [autoallocate |uniform size num[k|m]]];

  说明:

  ? smallfile | bigfile:表示创建的是小文件表空间还是大文件表空间

  ? autoextend [on|off] next:表示数据文件为自动扩展或非自动扩展,如为自动扩展则需要设置next的值。

  ? maxsize:表示数据文件自动扩展时,允许数据文件扩展的最大长度字节数,如果指定unlimited关键字,则不需要指定字节长度。

  ? minimum extent:指出在表空间的extent的最小值,这个参数可以减少空间碎片,保证在表空间的extent是这个数值的整数倍。

  ? online | offline:创建表空间时可以指定为在线或离线。

  ? permanent | temporary:指定创建表空间是永久表空间或临时表空间。默认为永久表空间。

  ? logging | nologging:指定该表空间内的表在加载数据时是否产生日志,默认为产生日志,即使设定为nologging,但在进行insert,update,delete操作时,oracle仍会将信息记录到redo log buffer中。

  ? extent management dictionary | local:指定表空间的扩展方式是使用数据字典管理还是本地化管理。默认为本地化管理。

  ? autoallocate | uniform size:如果采用本地化管理,在表空间扩展时,指定每次区的扩展大小是系统自动指定还是按照同等大小进行。如果设定uniform关键字,默认扩展大小为1MB。

  ? reuse:表示如果该文件存在,则清除该文件再重建该文件;若文件不存在,则创建该文件。

  ? default storage:设定以后要创建的表、索引、簇的存储参数值。

  4、 删除表空间

  ? 删除空的表空间,但是不包含物理文件

  drop tablespacetablespace_name;

  ? 删除非空表空间,但是不包含物理文件

  drop tablespacetablespace_name including contents;

  ? 删除空表空间,包含物理文件

  drop tablespace tablespace_nameincluding datafiles;

  ? 删除非空表空间,包含物理文件

  drop tablespacetablespace_name including contents and datafiles;

  ? 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段,就要加上CASCADECONSTRAINTS

  drop tablespacetablespace_name including contents and datafiles CASCADE CONSTRAINTS;

  5、 案例

  ? 创建表空间,然后删除该表空间。

  Createtablespace exampletb

  Datafile 'E: examp01.dbf' size 5M autoextend on next 128k maxsize 1000m,

  'E: examp02.dbf' size 5Mautoextend on next 128k maxsize 1000m;

  说明:以上例子创建的表空间由examp01.dbf和examp02.dbf两个文件组成。创建完成后,可以发现在相应路径下增加了2个文件。

  drop tablespaceexampletb;

  ? 创建表空间和表,然后删除该表空间。

  Createtablespace exampletb Datafile 'E: examp01.dbf'size 5M autoextendon next 128k maxsize 1000m reuse,

  'E:examp02.dbf' size 5Mautoextend on next 128k maxsize 1000m reuse;

  create tablescott.student

  (

  id number,

  name VARCHAR2(10)

  )tablespaceexampletb;

  说明:向student表插入数据时,数据将存储在表空间exampletb中,而exampletb表空间拥有一个或多个数据文件,所以student数据最终存储到examp01和examp02的数据文件中。

  drop tablespaceexampletb including contents;

  ? 创建表空间,然后删除该表空间及数据文件。

  Create tablespaceexampletb

  Datafile 'E: examp01.dbf' size 5M autoextend on next 128k maxsize 1000mreuse,

  'E:examp02.dbf' size 5Mautoextend on next 128k maxsize 1000m reuse;

  drop tablespaceexampletb including datafiles;

  ? 创建表空间和表,然后删除该表空间及数据文件。

  Createtablespace exampletb

  Datafile 'E: examp01.dbf' size 5M autoextend on next 128k maxsize 1000m,

  'E:examp02.dbf' size 5Mautoextend on next 128k maxsize 1000m;

  create tablescott.student

  (

  id number,

  name VARCHAR2(10)

  )tablespaceexampletb;

  drop tablespaceexampletb including contents and datafiles;

  说明:如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示错误。

  ? 创建两个表空间,分别在其中创建主码表和外码表,然后删除包含主码表的表空间及数据文件。

  Createtablespace exampletb1

  Datafile 'E: examp03.dbf' size 5M autoextend on next 128k maxsize 1000m;

  Createtablespace exampletb2

  Datafile 'E: examp02.dbf' size 5M autoextend on next 128k maxsize 1000m;

  create tabletest1(mobile number(13) primary key)tablespace exampletb1;

  create table test2(mobile number(13) references test1(mobile)) tablespace exampletb2;

  drop tablespace exampletb1 including contents and datafiles cascade constraints;

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are stored procedures in MySQL?What are stored procedures in MySQL?May 01, 2025 am 12:27 AM

Stored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.

How does query caching work in MySQL?How does query caching work in MySQL?May 01, 2025 am 12:26 AM

The working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.

What are the advantages of using MySQL over other relational databases?What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AM

The reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.

How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),