Oracle Study Notes: Comparison between Oracle tablespace management and sqlserver file groups

The management of database files in sqlserver is relatively simple. The main concepts include:

1. File Group. Storage Allocation unit of database objects. The purpose is to accommodate more database files and expand space.

2. File. File Group members, with space allocation and automatic growth management

3. default file group

4. Read-Only file group. This concept is available only after sqlserver2005, not sqlserver2000!

5. Master and tempdb databases. Used for basic dictionary and temporary space management

The management of database files in Oracle is complicated. Basic concepts:

1. tablespace

2. Table space data file members

3. default tablespace

4. Read-Only tablespace

5. Offline tablespace

6. Offline tablespace data file members

7. System, sysaux, undo, and temporary tablespaces exist as automatic, undo, and temporary data spaces.

8. bigfile File

9. Perform [logging | nologging | force logging] on the space

From the above basic comparison:

1. Oracle space management has many features not available in sqlserver

2. Oracle management becomes complicated. In fact, some features can depend entirely on OS. Maybe this is the compromise that Oracle has made for platform independence.

 

Basic elements of Oracle tablespace

1. Nature: User's permanent tablespace, undo tablespace, and temporary tablespace

2. whether to use the bigfile feature

3. Name

4. tablespace file members. If bigfile is used, only one file member can be used: datafile and tempfile.

5. Whether to record logs: logging, nologging, and force logging. Cannot use temporary or undo tablespace

6. Online or not: noline or offline

7. Data Block Size: blocksize {2 K, 4 K ......}. You must configure the db_nk_cache_size initialization parameter accordingly. And the value cannot be smaller than the OS block size! Temporary tablespaces cannot use non-standard data blocks!

8. extended zone management mode: extent management {dictionary | Local [autoallocate | uniform [size N [M | K | G ..]}. Note that temporary and undo tablespaces have special requirements!

9. segment management mode: Segment space management {auto | manual }. Cannot use temporary or undo tablespace

10. Whether the flash back feature is enabled: flashback {on | off }. Cannot use temporary or undo tablespace

11. Retention mode: retention {guarantee | noguarantee}

Basic Oracle tablespace-related operations

0. Note:

1. Temporary tablespaces can only be usedAdd tempfile file-specOne statement

2. If the Undo table is empty, you can modify it.

3. The system tablespace does not allow offline and read

......

1. Add a tablespace: Create [bigfile] tablespace tbs_name datafileFile-spec,..

2. Modify the tablespace: add or delete a file: Alter tablespace tbs_name {Add | drop} {datafile | tempfile}File-spec,..

3. Modify the tablespace: Move and rename the file: Alter tablespace tbs_name rename datafileFile-spec,...File-spec,..

4. Modify the tablespace: online and offline files: Alter tablespace tbs_name {datafile | tempfile} {online | offline}

5. Modify the tablespace: tablespace name: Alter tablespace tbs_name Rename to new_tabs_name

6. Modify the tablespace: log mode: Alter tablespace tbs_name {logging | nologging | [No] force logging}

7. Modify tablespaces: online and offline tablespaces: Alter tablespace tbs_name {online | offline}

8. Modify the tablespace: Modify the read and write modes: Alter tablespace tbs_name read {only | write}

9. Modify the tablespace: Modify the flashback mode: Alter tablespace tbs_name flashback {on | off}

10. Modify the tablespace: Modify the flashback data retention mode: Alter tablespace tbs_name retention {guarantee | noguarantee}

11. Modify the tablespace: Modify the size of the bigfile file: Alter tablespace tbs_name resize n {k | M | G | t}

12. Modify the tablespace: Modify the partition auto-scaling mode of the bigfile file: Alter tablespace tbs_name autoextend {off | on [next n maxsize [M | unlimited]}

13. Modify the tablespace: Modify the group mode of the temporary space: Alter tablespace tbs_name Group {group_name | ''}

14. Modify the tablespace: Convert the table space in the permanent and temporary modes: Alter tablespace tbs_name {permanent | temporary }. You need to consider the restrictions on the temporay tablespace before conversion, otherwise it will not succeed.

15. Modify the tablespace: change whether the online backup mode is Enabled: Alter tablespace tbs_name {begin | end} backup

16. Modify tablespace: Modify read and write modes: Alter tablespace tbs_name read {only | write}

17. Set the default tablespace: Alter database set default [temporary] tablespace tbs_name

18. delete a tablespace: Drop tablespace tbs_name [including contents [{and | keep} datafiles] [cascade contraints]

19. OMF management method. You only need to set db_create_file_dest. It can be used in combination with user-defined methods.

20. query basic table space information: Select * From dba_tablespaces; select * from V $ tablespace;

21. query the basic information of the table space data file: Select * From dba_data_files; select * from V $ datafile;

22. query the basic information of temporary tablespace and data files: Select * From dba_temp_files; select * from V $ tempfile; select * From dba_tablespace_groups;

 

23. modify Database: Modify the size of data and temporary files: Alter database {datafile | tempfile}File-spec ,..Resize n {k | M | G | t }. It is not limited to bigfile or smallfile.

24. Modify the database: Change the automatic expansion mode of data and temporary files: Alter database {datafile | tempfile}File-spec ,..Autoextend {off | on [next n maxsize [M | unlimited]}. It is not limited to bigfile or smallfile.

25. Modify the database: Move or rename the file: Alter database rename FileFile-spec,...File-spec,..

26. modify Database: online and offline files: Alter database {datafile | tempfile}File-spec ,..{Online | offline }. For datafile offline, you can select for drop.

27. modify Database: Delete the tomporay file: Alter database tempfileFile-spec ,..Drop [including datafiles]

28. modify Database: Create a datafile file: Alter database create datafile {File-spec ,..|Newfilenumber} {File-spec ,..|New}

29. modify Database: Rename redo file: Alter database renameRedo-file-spec,...Redo-file-spec,..

30. modify Database: Create a redo file group: Alter database add [standby] logfile [group N]Redo-file-spec,..

31. modify Database: Create redo file members: Alter database add [standby] logfile MemberRedo-file-spec,... To group N

32. modify Database: delete redo file group: Alter database drop [standby] logfile {Redo-file-spec|Group N}

33. modify Database: delete redo file members: Alter database drop [standby] logfile MemberRedo-file-spec,...

34. modify Database: Create physical and logical standby controlfile file: Alter database create [logical | physical] standby controlfileCtlfilespec[Reuse]

35. modify Database: Back up controlfile to the specified full path file: Alter Database Backup controlfileCtlfilespec[Reuse]

36. modify Database: Back up controlfile to the specified or default trace file: Alter Database Backup controlfile to trace [Ctlfilespec] [Reuse] [{resetlogs | noresetlogs}]

37. modify Database: delete redo file members: Alter Database

 

 

Appendix: Management of Oracle Database Files

Oracle Database files are used in the following statements:

1. Create Database

2. Alter Database

3. Create controlfile

4. Create tablespace

5. Alter tablespace

There are two types of Oracle Database Files: datafile_tempfile and redo_logfile. The main difference is that the redo_logfile cannot be automatically autoextend!

The main specifications for specifying database files are as follows:

'Full _ path_filename '[size N] [reuse] [autoextend {off | on next maxsize {unlimited | m}]

Note:

The size can contain multiple character units {k | M | G | T | p |...}, but if the unit is not specified, it is byte bytes.

For undo tablespace files, the size must be specified. Other tablespaces, if the file already exists or OMF is used, do not specify the size.

If autoextend is ignored:

For OMF, if the size is specified, expansion is prohibited; otherwise, automatic expansion is disabled.

Prohibit User-Defined files

............................ Heaven, Earth, by default, it will all be automatic !!!............................... ..........