Basic PL/SQL programming (11) [tablespace overview, description, and creation] [local management and dictionary management, switch management]

1. concept:
The tablespace is the largest logical carrier. It stores the associated database logic and stores database files.

2. Create a tablespace using SQLPLUS
Create tablespace tabs -- create a tablespace
Datafile 'f: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ tabs. dbf' size 10 M;
-- Set the size of the associated file and tablespace
Alter user zhanglei default tablespace tabs; -- change the default tablespace
Connect zhanglei/zhanglei -- connect to the zhanglei user
-- Create a table
Create table tre (t char (8), tr number );
-- Create a table. The default tablespace prevails. That is, tabs, which is specified During User Creation
-- The system prompts that you do not have permission to apply the tabs tablespace:
Create table tre (t char (8), tr number)
*
Row 3 has an error:
ORA-01950: no permission for table space 'tabs

Grant unlimited tablespace, dba to zhanglei;
-- Switch to the dba user for authorization, and grant unlimited tablespace and DBA maximum Permissions
Create table ty (t varchar2 (10), y number) tablespace tt; -- create a table and specify the tablespace

3. Create a tablespace www.2cto.com by using the OEM.
Note: The table space created by the OEM is not described here. I believe that the OEM interface is sufficient for everyone to operate and understand.

4. Differences between local management and dictionary management:
Dictionary management tablespaces check their data dictionaries whenever tables or other objects need to be expanded to ensure available space is allocated to objects, allocate a new section to the object and update its available space information.
Local management tablespace stores the space management information of the data file itself, and the tablespace automatically tracks the available or used status of each data file block.
In databases with many transactions, it is clear that dictionary management checks the data dictionary every time data is inserted, which causes database performance loss.

5. convert local management and field management methods and descriptions:

The first method is command transfer.
First, you need to create an oracle tablespace. After 10 Gb of oracle, local tablespace management is used by default.
Use commands for table space Transfer
Alter table temp move tablespace new_temp;
You need to rebuild the index
Alter table index rebuild tablespace new_index;
You may use DBMS_SPACE_ADMIN in PL/SQL data packets provided by oracle.
Obviously, the above method does not apply to system table conversion, because you cannot create two system tables with the same name.
Before converting a system table, you must convert all other tablespaces to local management.
EXECUTEdbms_space_admin.tablespace_migrate_to_local ("tablespace"); --- transpose table space
Then, the system tablespace is converted in the same way.
EXECUTEdbms_space_admin.tablespace_migrate_to_local ("system ");
This method is good, but the tablespace created by it does not have the automatic segment spacemanagerment option. All the dictionary-managed tablespaces are managed using the default manual segment space, in addition, you cannot modify the tablespace to be locally managed.
Another drawback is that if the tablespace contains space fragments, this method cannot solve the fragmentation problem.


From the column zhang6622056