Oracle create tablespace syntax

Source: Internet
Author: User

  • CREATE [UNDO] TABLESPACE tablespace_name
  • [DATAFILE datefile_spec1 [, datefile_spec2] ......
  • [{Mininum extent integer [k | m]
  • | BLOCKSIZE integer [k]
  • | Logging clause | FORCE LOGGING
  • | DEFAULT {data_segment_compression} storage_clause
  • | [Online | offline]
  • | [PERMANENT | TEMPORARY]
  • | Extent_manager_clause
  • | Segment_manager_clause}]
  • 1. undo
  • The system creates a tablespace for rollback.
  • In 9i, the database administrator can manage rollback segments without having to create undo tablespaces. The system automatically manages rollback segment allocation and recovery.
  • Of course, you can also create general tablespaces and create rollback segments on them. However, for users, system management is much better than self-management.
  • If you need to manage it by yourself, see the detailed command for rollback segment management.
  • When the system does not specify a tablespace for rollback, the system uses the system rollback segment for transaction management.
  • //
  • 2. tablespace_name
  • Name of the tablespace.
  • //
  • 3. datafile datefile_spec1
  • Specifies the space file contained in the tablespace.
  • Datefile_spec1 is like :['Filename'] [SIZE integer [K | M] [REUSE] [autoextend_clause]
  • [Autoextend_clause] is an example: AUTOEXTEND {OFF | ON [NEXT integer [K | M] [maxsize_clause]}
  • Filename is the path name of the data file. It can be a relative or absolute path.
  • Size is the file size,
  • REUSE indicates whether the file is reused.
  • AUTOEXTEND indicates whether to automatically expand.
  • OFF | ON indicates whether auto scaling is disabled.
  • NEXT indicates the extended size when the data file is full.
  • Maxsize_clause indicates the maximum size of the data file, for example, MAXSIZE {UNLIMITED | integer [K | M]}.
  • UNLIMITED indicates an infinite tablespace.
  • Integer is the maximum size of the data file.
  • DATAFILE'D: "Oracle" oradata "IMAGEDATA01.dbf'SIZE 2000 M,
  • 'D: "oracle" oradata "IMAGEDATA02.dbf'SIZE 2000 M
  • //
  • 4. mininum extent integer [k | m]
  • Specifies the minimum value of the range in the tablespace. This parameter can reduce space fragments and ensure that the range in the tablespace is an integer multiple of this value.
  • //
  • 5. BLOCKSIZE integer [k]
  • This parameter can be used to set the size of an unstandard block. To set this parameter, you must set db_block_size,
  • At least one db_nk_block_size, and the declared integer value must be equal to db_nk_block_size.
  • Note: This parameter cannot be set in the temporary tablespace.
  • //
  • 6. logging clause
  • This clause declares the log attributes of all user objects in the tablespace (logging by default ),
  • Including tables, indexes, partitions, materialized views, and indexes and partitions on materialized views.
  • //
  • 7. FORCE LOGGING
  • Use this clause to indicate that the tablespace enters the forced log mode. At this time, the system records all changes to objects in the table space, except for changes to temporary segments.
  • This parameter is higher than the object's nologging option.
  • Note: The database cannot be opened and is in read/write mode. This option cannot be used in temporary tablespace and rollback tablespace.
  • //
  • 8. DEFAULT storage_clause
  • Declare the default storage clause.
  • //
  • 9. online | offline
  • Changes the status of a tablespace. Online makes the tablespace valid immediately after being created. This is the default value.
  • Offline makes the tablespace invalid after it is created. This value can be obtained from dba_tablespace.
  • //
  • 10. PERMANENT | TEMPORARY
  • Specifies whether a tablespace is a permanent tablespace or a temporary tablespace.
  • Permanent tablespace stores permanent objects, while temporary tablespace stores temporary objects existing in the session lifecycle.
  • The temporary tablespace generated by this parameter is always in dictionary management after it is created, and the extent management local option cannot be used.
  • To create a local tablespace, you must use the create temporary tablespace
  • Note: block size cannot be declared after this parameter is declared.
  • //
  • 11. extent_management_clause
  • This is the most important clause that describes how to manage the range of a tablespace. Once you declare this clause, you can only change these parameters by porting them.
  • If you want to manage the tablespace locally, declare the local option. Local tablespace management is implemented through bitmap management.
  • Autoallocate indicates that the tablespace is automatically allocated with a range. You cannot specify the range. Only Versions later than 9.0 have this function.
  • Uniform indicates the fixed size of the tablespace range. The default value is 1 MB.
  • You cannot set the system tablespace of a locally managed database to dictionary management.
  • Oracle recommends using local tablespace management.
  • If extent_management_clause is not set, oracle sets a default value for it.
  • If the initialization parameter compatible is smaller than 9.0.0, the system creates a dictionary management tablespace.
  • If the value is greater than 9.0.0, set the value as follows:
  • If noDefaultStorage_clause: oracle creates an automatically allocated local management tablespace.
  • Otherwise,
  • If the mininum extent is specified, oracle determines whether the mininum extent, initial, and next are equal, and whether the pctincrease is 0.
  • If the preceding conditions are met, oracle creates a local tablespace for management, and the extent size is initial.
  • If the preceding conditions are not met, oracle creates an automatically assigned local tablespace.
  • If the mininum extent is not specified. Initial, then oracle determines whether next is equal, and whether pctincrease is = 0.
  • If you want oracle to create a local tablespace and specify the uniform. Otherwise, oracle creates an automatically allocated local tablespace.
  • Note: Local tablespace management can only store permanent objects.
  • If you declare local, you cannot declareDefaultStorage_clause, mininum extent, temporary.
  • EXTENT MANAGEMENT LOCAL
  • //
  • 12. segment_management_clause
  • Segment space management method, automatic or manual:
  • Segment space management {AUTO | MANUAL}
  • --
  • Instance 1: create a permanent tablespace
  • You can create a single tablespace at a time or multiple tablespaces at a time.
  • 1.1 create a single tablespace
  • Create tablespace ts_something
  • Logging
  • Datafile'/Dbf1/ts_sth.dbf'
  • Size 32 m
  • Autoextend on
  • Next 32 m maxsize 2048 m
  • Extent management local;
  • //
  • 1.2 create multiple tablespaces
  • Create tablespace TS_IMAGEDATA
  • NOLOGGING
  • DATAFILE'D: "oracle" oradata "DATA01.dbf'SIZE 2000 M,
  • 'D: "oracle" oradata "DATA02.dbf'SIZE 2000 M,
  • 'D: "oracle" oradata "DATA03.dbf'SIZE 2000 M,
  • 'D: "oracle" oradata "DATA04.dbf'SIZE 2000 M,
  • 'D: "oracle" oradata "DATA05.dbf'SIZE 2000 M EXTENT
  • MANAGEMENT LOCAL SEGMENT
  • Space management auto;
  • //
  • Create tablespace TS_IMAGEDATA
  • LOGGING
  • DATAFILE'E: "ORACLE" ORADATA "data_01.dbf'SIZE 2000 M REUSE
  • AUTOEXTEND ON
  • NEXT 51200 k maxsize 3900 M,
  • 'E: "ORACLE" ORADATA "XL" DATA_02.DBF'SIZE 2000 M REUSE
  • AUTOEXTEND ON
  • NEXT 51200 k maxsize 3900 M,
  • 'E: "ORACLE" ORADATA "XL" data_03.dbf'SIZE 2000 M REUSE
  • AUTOEXTEND ON
  • NEXT 51200 k maxsize 3900 M,
  • 'E: "ORACLE" ORADATA "XL" data_04.dbf'SIZE 2000 M REUSE
  • AUTOEXTEND ON
  • NEXT 51200 k maxsize 3900 M
  • EXTENT MANAGEMENT LOCAL
  • Segment space management auto;
  • Instance 2: Create an undo tablespace
  • Create undo tablespace ts_undo
  • Datafile'/Dbf/undo. dbf'Size 100 M;
  • //
  • Create undo tablespace ts_undo01
  • DATAFILE'/Data/ts_undo01.dbf'
  • SIZE 50000 M REUSE
  • Autoextend on;
  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.