Oracle tablespace operation & create tablespace command

Turn: http://blog.sina.com.cn/s/blog_49605b460100b1pi.html

Elect *
From dba_tables
Where owner = 'Peter 'create tablespace data01
Datafile 'd: \ oracle \ ora92 \ oradata \ dB \ data01.dbf' size 200 m
Uniform size 128 K;
# The size of the specified area is 128 kb. If this parameter is not specified, the default size of the area is 64 kcreate temporary tablespacetemp_data.
Tempfile 'd: \ oracle \ ora92 \ oradata \ dB \ temp_data.dbf' size 100 m
Create user Peter identified by Peter
Default tablespace data01 temporary tablespace temp_data; grantconnect, resource, DBA to Peter;

1. Create a tablespace

Create tablespace data01
Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m
Uniform size128k; # specify the area size as 128 K. If not specified, the area size defaults to 64 K.

2. Create an undo tablespace

Create undo tablespace undotbs02
Datafile '/Oracle/oradata/DB/undotbs02.dbf' size 50 m

# Note: In the open state, only one undo tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:

Alter system set undo_tablespace = undotbs02;

3. Create a temporary tablespace

Create temporary tablespace temp_data
Tempfile '/Oracle/oradata/DB/temp_data.dbf' size 50 m

Iv. Change the tablespace status

1. Take the tablespace offline

Alter tablespace game offline;

If the data file is accidentally deleted, the recover option must be included.

Alter tablespace game offline for recover;

2. Bring the tablespace online

Alter tablespace game online;

3. offline data files

Alter database datafile 3 offline;

4. Bring data files online

Alter database datafile 3 online;

5. Read-Only tablespace

Alter tablespace game read only;

6. Make the tablespace readable and writable

Alter tablespace game read write;

5. Delete tablespaces

Drop tablespace data01 including contents and datafiles;

Vi. Extended tablespace

First, check the name and file of the tablespace.

Select tablespace_name, file_id, file_name,
Round (Bytes/(1024*1024), 0) total_space
From dba_data_files
Order by tablespace_name;

1. Add data files
Alter tablespace game
Add datafile '/Oracle/oradata/DB/game02.dbf' size 1000 m;

2. manually add data file size
Alter database datafile '/Oracle/oradata/DB/game. dbf'
Resize 4000 m;

3. Set automatic expansion of data files
Alter database datafile '/Oracle/oradata/DB/game. DBF
Autoextend on next 100 m
Maxsize 10000 m;

View tablespace information after setting

Select a. tablespace_name, A. bytes total, B. bytes used, C. bytesfree,
(B. bytes * 100)/A. bytes "% used", (C. bytes * 100)/A. bytes "% free"
From SYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free C
Where a. tablespace_name = B. tablespace_name Anda. tablespace_name = C. tablespace_name;

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Create tablespace command

Create [undo] tablespace tablespace_name

[Datafile datefile_spec1 [, datefile_spec2] ......

[{Mininum extent integer [k | M] | blocksize integer [k] | loggingclause | 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 a general tablespace and create a rollback segment on it. 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 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] How can this problem be solved? Autoextend {off | on [next integer [k | M] [maxsize_clause]}

Filename indicates the full path name of the data file, size indicates the file size, and reuse indicates whether the file is reused.

Autoextend indicates whether automatic expansion. Off | on indicates whether automatic expansion is disabled. Next indicates the size of the expansion after 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 indicates the maximum size of the data file.

 

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. If you want 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 in 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: The tablespace is valid immediately after being created. This is the default value. Offline: Invalid after being 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 extentmanagement 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 default storage_clause is not specified, Oracle creates an automatically assigned local 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 extentsize is initial. if the preceding conditions are not met, Oracle creates an automatically assigned local tablespace.

If the mininumextent 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 declare defaultstorage_clause, mininum extent, temporary.

12. segment_management_clause

 

Create a tablespace

Create tablespace data01
Datafile '/Oracle/oradata/DB/data01.dbf' size 500 m
Uniform size128k; # specify the area size as 128 K. If not specified, the area size defaults to 64 K.

Delete a tablespace

Drop tablespace data01 including contents and datafiles;

 

Slave --------------------------------------------------------------------------------------------------------------------------------

1. Create a role
Create role "qiudingrole" not identified;

Grant "Connect" to "qiudingrole ";
Grant "dba" to "qiudingrole ";
Grant "resource" to "qiudingrole ";

2. Create a tablespace
Create smallfile tablespace "diaocha" datafile 'd: \ oracle \ product \ 10.1.0 \ oradata \ qiuding \ diaocha 'size 100 m autoextend on next 1 m maxsize unlimited logging extent management local segment space management auto;

Begin dbms_server_alert.set_threshold (9000, null, 1, 1, null, 5, 'diaocha '); end;

Create smallfile temporary tablespace "diaocha_temp" tempfile 'd: \ oracle \ product \ 10.1.0 \ oradata \ qiuding \ diaocha_temp 'size 100 m autoextend on next 1 m maxsize unlimited extent management local uniform size 1 m;

Begin dbms_server_alert.set_threshold (9000, null, 5, 'diaocha _ temp '); end;

3. Create a user
Create user qiuding
Identified by "qiuding"
Default tablespace "diaocha"
Temporary tablespace "diaocha_temp"
Profile default
Account unlock;

Grant qiudingrole to qiuding;
Grant unlimited tablespace to qiuding

4. Create a backup directory and grant corresponding Permissions
Create directory dump_dir as 'd: \ oracle \ backup ';
Grant read, write on directory dump_dir to qiuding;

 

Bytes -------------------------------------------------------------------------------------------------------------------------

Some preliminary work is required to use Oracle.

Oracle users
# Su-Oracle

Create a tablespace-> Create a new user-> authorize a user

1. Create a tablespace
SQL> Create tablespace test01
Datafile '/data/Oracle/oradata/DB/test01.dbf' size 1024 M uniform size 128 K; 

1) datafile: path for storing tablespace data files
2) Size: Set it to 1 GB at first.
3) uniform: the size of the specified area is 128 K. If not specified, the default area size is 64 K.
4) The space name test01 is not required to be the same as the data file name test01.dbf.

Alter database to modify the size of a space extension

SQL> alter database datafile '/data/Oracle/oradata/DB/test01.dbf'' autoextend on;

1)Autoextend on: In Oracle10g, You need to manually start the automatic partition expansion function.
2. Create a new user
SQL> create user test identified by 123456 default tablespace test01;

1) create a user to allocate table space at the same time

Three-user authorization
1) User Role authorization
SQL> crant connect, resource to test;

A) Here I will assign the connect and resource roles to the new user test, and test will have operation permissions for these two roles.

2) direct authorization
Multiple users
SQL> crant select, insert, update, delete, on users to test, test1;

The following two methods can be used to log on to Oracle DB:
One sqlplus
1) Local Login
SQL> sqlplus test/123456

2) Remote Logon
Assume that the database is on another server.
SQL> sqlplus test/123456 @ DB

2. PLSQL developer
As described in the previous article, enter the account password.

The following is related to the above and will be used in the work.
1. delete a user
SQL> drop user test cascade;

2. Delete tablespaces
SQL> drop tablespace test01 including contents and datafiles;

1) Delete the tablespace content and data files.
2) Generally, invalid tablespaces occupy disk space, so this should be very common.

 

View User Role Permissions

Select * From user_role_privs;

View User table Permissions

Select * From user_tab_privs;