Define the purpose of table spaces and data files
Create tablespace
Manage table spaces
Use Oracle Management Files (OMF) to create and manage table spaces and obtain table space information
Oracle logically stores data in table spaces and physically stores data in data files.
1. Table space
1.1 Table space and data files
1.1.1Tablespaces:
-can only belong to one database at a time containing one or more
-data file
-Whether to divide the step-by-step into editing storage units
1.1.2 Data File:
-Can only belong to one table space and one database
– is a repository for schema object data
1.2 Storage hierarchy summary
(1). The database consists of one or more table spaces.
(2). The table space consists of one or more data files. These files may be cooked files in the file system, raw partitions, ASM-managed database files, or files in the cluster file system. The tablespace contains segments.
(3).A segment (TABLE, INDEX, etc.) consists of one or more sections. A segment exists in one tablespace, but there may be data in many data files within that tablespace.
(4). A segment is a group of logically contiguous blocks on the disk. Extents reside in a single tablespace and are always located in a single file within that tablespace.
(5). Block is the smallest allocation unit in the database. A block is the smallest unit of I/O used by the database.
1.3 Tables and data files
1.4 Table space type
system tablespace
-Create using database
-Contains data dictionary
-Contains SYSTEM undo segment
non-system tablespace
-separate parts
– Simplify space management
-Control the amount of space allocated to users
1.4.1 Create table space
(1) First query the location of other table spaces
select file_name,tablespace_name from dba_data_files;
(2) Start creating
create tablespace name datafile 'Location of most tablespaces' size 5m; name - the name of the tablespace size - tablespace size
Space management in 1.5 table space
1.5.1 Locally managed table space:
-Free sections are managed in table spaces.
-Bitmap is used to record free sections. Each bit corresponds to a block or group of blocks.
-Bit value indicates free or used.
1.5.2 Dictionary management table space:
-The free area is managed by the data dictionary.
-Update the corresponding table when a segment is allocated or freed.
bit and byte 
1byte = 8bit
0xE8A2 = 1110.1000.1010.0010
1.6Dictionary-Managed TS (data dictionary management table space)
– Sections are managed in the data dictionary.
-Each segment stored in a tablespace can have a different storage clause.
– Need to be merged.
CREATE TABLESPACE userdata datafile '/u01/oradata/userdata01 .dbf' size 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT1M PCTINCREASE 0);
1.7 Locally Managed Tablespacs
-Reduced contention on data dictionary tables
– No undo occurs when space is allocated or reallocated
-No need to merge
CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
1.8 Migrating a DM SYSTEM TS (Migrating a DM SYSTEM TS)
-Migrate the dictionary-managed SYSTEM tablespace to local management:
DBMS SPACE ADMIN.
TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');
1. Make a complete backup of the database.
2. Make sure the database has a default temporary table space, not SYsTEM. The temporary tablespace is created through the CREATE temporary tablespace command.
3. Eliminate any undo (rollback) segments in the dictionary-managed table space.
4. There should be at least one online undo segment in the locally managed table space, or the undo table space should be online.
5. Except for table spaces containing undo spaces and default temporary table spaces, all table spaces should be set to READ ONLY mode. 6. Launch the instance in restricted mode.
7. Migrate the SYSTEM table space: DBMS_SPACE_ADMIN. Tablespace_migrate_to_local (‘ system ‘);
Undo tablespace
1.9Undo Tablespace (Undo Tablespace)
-cannot contain any other objects
– Sections are managed locally
-Only DATAFILE and EXTENT management clauses can be used
CREATE UNDO TABLESPACE undo1 DATAFILE'/u01/oradata/undo01.dbf' SIZE 40M;
1.10 Temporary Tablespaces
-for sorting operations
-Can be shared by multiple users
-cannot contain any permanent objects
-It is recommended to use locally managed sections
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/temp01.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
When the SYSTEM tablespace is locally managed, at least one default temporary tablespace must be defined when the database is created. The locally managed SYSTEM tablespace cannot be used for default temporary storage.
If SYSTEM is dictionary-managed and no default temporary tablespace is defined when the database is created, then SYSTEM is still used for the default temporary storage. However, you will receive a warning in ALERT.LOG stating that the default temporary tablespace is recommended and will be required in a future release.
1.10.1 Default temporary table space
-Specify the database-wide default temporary tablespace
-Avoid using SYSTEM tablespace to store temporary data
-can be created using:
-CREATE DATABASE
-ALTER DATABASE
1.10.2 Create default temp
After the database is created: (To put it bluntly, it is modified)
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp2;
Find the default temporary tablespace for database queries database PROPERTIES:
SELECT * FROM DATABASE_PROPERTIES;
The default temporary table space cannot be:
Delete until new default is available
Cannot be offline
Cannot be modified to a permanent table space
1.11 Read-Only Tablespaces
Use the following command to set the tablespace to read-only mode:
ALTER TABLESPACE userdata READ ONLY;
-Leads to checkpoint
-Data objects that can only be used for read operations
-Can be deleted from tablespace
example:
First create a table space
create tablespace qwe datafile '/u01/app/oracle/oradata/orcl/qwe.dbf' size 10m extent management local uniform size 128k;
Create abc user
create user abc identified by abc default tablespace qwe; //Create abc user grant connect,resource to abc; //Give abc user permissions
Open another window, use abc user, and create a table
sqlplus abc/abc create table a (id integer,name char(10)); // Create table a
Insert data into table a
insert into a values(0,'orcale'); insert into a values(1,'java'); //Insert two pieces of data select * from a; //View table data commit;
Return to the sys user window
alter tablespace qwe read only;
verify
//Insert a piece of data for verification insert into a values(2,'python'); An error will be reported as a result, which is expected, because your purpose is to let him only read select * from a; The result will not be an error alter tablespace qwe read wite; //Change the table back to readable and writable
1.12 Taking a Tablespace Offline
Cannot be used for data access
Table spaces that cannot be taken offline
-System table space
-Tablespace with active undo segment
-Default temporary tablespace
Taking a tablespace offline
alter tablespace userdata offline;
Bringing a tablespace online
alter tablespace userdata online;
Perform offline processing of the qwe table space under the sys user
alter tablespace qwe offline; //Offline processing of qwe table
Query again
select * from a; An error will be reported because you closed the table space earlier
Offline processing successful
Online processing of qwe table space under sys user
alter tablespace qwe online;
Make a query
select * from a;
Online processing successful
1.13 Adjust table space size
Tablespaces can be resized in the following ways:
Change the size of data files:
– Automatically use AUTOEXTEND
– Manually use ALTER DATABASE
Add data files using ALTER TABLESPACE
Query the table space
Create a new file on the linux system
vim sql //Create sql file
Add the following content to the file
SELECT
a.tablespace_name,
ROUND(((a.bytes / 1024 / 1024)), 2) AS total_space_mb,
ROUND(((b.bytes / 1024 / 1024)), 2) AS used_space_mb,
ROUND((((a.bytes - b.bytes) / a.bytes) * 100), 2) AS used_percent
FROM
dba_data_files a
LEFT JOIN (SELECT
file_id,
SUM(bytes) bytes
FROM
dba_extents
GROUP BY
file_id) b ON a.file_id = b.file_id
ORDER BY
1;
After exiting change file
mv sql s.sql
Check the current directory
pwd
Enter sqlplus and log in using sys user
@/home/oracle/s.sql //@The following is the file directory
2. Data files
2.1 Dynamically adjust data file size
2.2 Add data files
2.3 Add table space
2.4 Enable Automatic Extension
Automatic resizing can be done using the following command:
-Create database
-Create tablespace (create tablespace)
-Modify tablespace…add data file (alter tablespace…add datafile)
alter tablespace qwe add datafile '/u01/app/oracle/oradata/orcl/qwe2.dbf' size 5m autoextend on next 1m maxsize 10m;
Check
Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.
select tablespace_name,autoextensible from dba_data_files;
Modify closed AUT
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/qwe.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 20M;
Check whether it is opened successfully
select tablespace_name,autoextensible from dba_data_files;
2.5 Manually adjust the date file size
Change the database by manually increasing or decreasing the data file size.
Resizing the data files can increase space without adding more data files.
Manually resizing the data files reclaims unused space in the database.
alter database datafile '/u01/app/oracle/oradata/orcl/qwe2.dbf' resize 10m;
First check the size of the original data file
select file_name,tablespace_name,bytes from dba_data_files;
2542880≈5mb
Execute directly Order
alter database datafile '/u01/app/oracle/oradata/orcl/qwe2.dbf' resize 10m;
Check the data file size again
select file_name,tablespace_name,bytes from dba_data_files;
okokokokokokok is done
Note: If the data file occupies 20, but you have to modify it to 10, it is not allowed. It cannot be smaller than the storage size.
2.6 Add data files
-Increase the space allocated to the tablespace by adding additional data files
-ADD DATAFILE clause is used to add data files
Yes in 2.4, I won’t write it here
2.7 Mobile Data File
2.7.1 Moving data files-modifying table space
-The tablespace must be offline.
-The target data file must exist.
Check the data file first
select file_name,tablespace_name,bytes from dba_data_files;
Close tablespace
alter tablespace qwe offline;
View the data file again
select * file_name,tablespace_name,bytes from dba_data_files;
No bytes, shutdown successful
Find the location of the data files
Go to the directory
Move (you can also copy cp)
mv file name target path mv qwe.dbf /u01/app/oracle/oradata/test
Modify table space location
alter tablespace qwe rename datafile '/u01/app/oracle/oradata/orcl/qwe.dbf' to '/u01/app/oracle/oradata/test/qwe.dbf';
Put data files online
alter tablespace qwe online;
View Files
select file_name,tablespace_name,bytes from dba_data_files;
Location change successfula.a
2.7.2 Moving data files-modifying database
-The database must be mounted
-The target data file must exist.
Mount database
shutdown immeidate;
Modify database location
alter database rename file '/u01/app/oracle/oradata/test/qwe.dbf' to '/u01/app/oracle/oradata/orcl/qwe.dbf';
Open database
alter database open;
View data files
select file_name,tablespace_name,bytes from dba_data_files;
Change successful
2.8 Delete table space
Tablespaces cannot be dropped under the following circumstances
-Is the SYSTEM table space
-Dynamic part
Violent deletion – the data files under the table space are also deleted
drop tablespace userdata including comtemts and datafiles;
3. Obtain table space information
Table space and data file information can be obtained as follows:
3.1 Table space information:
-DBA_TABLESPACES
-V$TABLESPACE
Spatial data file information:
-DBA_DATA_FILES
-V$DATAFILE
Temporary file information:
-DBA_TEMP_FILES
-V$TEMPFILE
4. Summary
In this lesson, you should learn how to: Separate data using table spaces Create various types of table spaces Manage table spaces Use OMF to manage table spaces Obtain table space information