To view the table space status statement:
Select Tablespace_name, status from Dba_tablespaces;
CREATE TABLE Space
Create Tablespace MySpace
DataFile ' G:\app\myspace\myspace01.dbf '
Size 10m autoextend on next 5m maxsize 100m;
Table Space properties are mainly online, offline, read only, ReadWrite, four states
To modify Table properties:
Alter tablespace myspace offline;
To modify the size of a data file in a table space
ALTER database datafile ' G:\app\myspace\myspace.dbf ' RESIZE newsize;
Adding data files to Tablespace
Alter Tablespace Tablespace_name
Add datafile ' ... ' size 10m
Renaming a table space
Alter tablespace tablespace_name rename to New_tablespace_name;
Delete Table space
Drop Tablespace Tablespace_name
[including Contents[and Datafiles]]
Creation and modification of temporary tablespace
Create temporary tablespace mytemp
Tempfile ' G:\app\myspace\mytemp.dbf '
Size 5m autoextend on next 2m maxsize 2
The modification syntax is the same as the basic table space.
Temporary table space groups
Create 1
Create temporary tablespace Tempgroup
Tempfile ' G:\app\myspace\tempgroup01.dbf ' size 5m
Tablespace group GROUP01;
Create 2
Create temporary tablespace tempgroup02
Tempfile ' G:\app\myspace\tempgroup02.dbf ' size 5m
Tablespace group GROUP02;
Move Tempgroup to GROUP02
Alter tablespace tempgroup tablespace group group02;
Create a large file table space
Create Bigfile tablespace mybigspace
DataFile ' G:\app\myspace\bigspace.dbf '
Size 10m;
Undo Table Space
To enable operations such as data fallback, recovery, 15 rollback, and Undo, the Oracle database provides a portion of the storage space, specifically saving the undo record, and saving the modified data to that space, so this part of the space becomes the undo table space.
Create an undo table space
Create undo Tablespace Undotbs
Dafafile ' G:\app\myspace\undo1.dbf ' size 20m
Autoextend on;
To add a new file for the undo table space
Alter Dablespace UNDOTBS
Add datafile ' G:\app\myspace\undo02.dbf ' size 10m
Modify
ALTER DATABASE datafile ' G:\APP\MYSPACE\UNDO02.DBF ' resize 15m;
Set the [temporary] default table space
ALTER DATABASE defult [tempory] tablespace tablespace_name;
Select Defult_tablesapce from User_users;
Create a log file group
ALTER DATABASE database_name
Add Logfile[group Group_number]
(file_name,) [Size n] [Reuse];
Create a log file
ALTER DATABASE add LogFile Member
' ... ' to group 4;
Toggle Log File Group
alter system switch logfile;
Emptying the log file group
ALTER DATABASE clear logfile Group group_number;
Delete a log file group
ALTER DATABASE drop logfile group group_number;
Log mode
Non-archived log mode (Noarchivelog) vs. archived log mode (Archivelog)
ALTER DATABASE Archivelog|noarchivelog;
Create a table and specify a table space
CREATE TABLE Person2
(ID number (4), name varchar (8), Birtyday date) tablespace MySpace;
Oracle Basic Learning Record 1.0