Oracle Database tablespace and archive

Oracle Database TableSpace and archive [SQL] -- TableSpace (TableSpace) is a pioneering concept of Oracle. Table space makes database management more flexible and greatly improves database performance. -- Function: 1. avoid the risk of sudden depletion of disk space 2. more flexible planning data 3. improve database performance 4. improve database security/* ============================================== ======= create an Oracle tablespace ========================================= ========================================= */1. create a simple tablespace create tablespace test datafile 'f: \ database \ oracle \ test_data.dbf' size 20 M 2. specify the scalability of the data file ---------- autoextend on 3. specify the growth of data files ------- autoextend On Next 5 m 4. specify the maximum size of the data file ------- autoextend On Next 5 m Maxsize 500 M 5. view empty table Whether the tablespace is created successfully: select file_name, tablespace_name from dba_data_files order by file_name 6. Create multiple data files for a tablespace-you can query view dba_tablespaces and view dba_data_files to obtain the tablespace information of the database. Dba_tablespaces can be used to view basic information about all tablespaces, while dba_data_files can be used to view information about related data files. /* ===================================================== ======= Modify the default tablespace of the database ============================================ ===================================== */select user_id, username, default_tablespace from dba_users alter database default tablespace user1 -- Oracle 10 Gb, The tablespace name modification feature is added. Use the rename to command to modify the tablespace name. Alter tablespace user2 rename to user20 Note: Renaming a tablespace does not affect data files. Finally, you must note that you cannot rename the SYSTEM tablespace of the data, for example, SYSTEM or SYSAUX. /* ===================================================== ========================================================== =======================================*/-- If a tablespace does not exist, you can run the delete tablespace command to release disk space. The command to delete a tablespace is drop tablespace. There are two ways to delete a tablespace. One is to delete only its records in the database, and the other is to drop tablespace user20. The other is to delete the records and data files together. Drop tablespace user20 including contents and DATAFILES/* ============================== ========== use a tool to view a data table ============================= =======================================*/-- tips: right-click the Columns branch and run the Copy comma separated menu command to Copy all column names and use commas as the separator. This is most useful when there are many columns in the data table and INSERT statements are used. Agent_name, db_username DESC dual create table test_table (id number, name varchar2 (20); insert into test_table Values (111, 'yang'); insert into test_table Values (112, 'peng'); SELECT * FROM test_table SELECT * FROM all_tables WHERE table_name LIKE '% test_table %' owner SELECT table_name FROM user_tables describe test_table; alter table test_table add (status varchar2 (3); describe test_table; SELEC T * FROM test_table alter table test_table drop column status; drop table test_table; archive log list [SQL] [SQL] -- check whether the database is in ARCHIVE mode select name, log_mode from v $ database; the Oracle database has a redo log of the connected machine. This log records changes made to the database, such as insertion, deletion, and data update. All these operations are recorded in the online redo log. Generally, a database must have at least two online redo log groups. When an online redo log group is full, log switching occurs. At this time, online redo log group 2 becomes the currently used log. When online redo log group 2 is full, log switching occurs again. Write online redo log group 1, and then repeat it. If the database is in non-archive mode, online logs are discarded during Switching. In archive mode, logs are archived When switching. For example, currently online redo log 1 is used. When 1 is full, log switching occurs and online redo log 2 is started, at this time, the content of online redo log 1 will be copied to another specified directory. This directory is called the archive directory, and the copied file is called the archive redo log. You can perform catastrophic recovery only when the database is running in archive mode. 1. the difference between the archive log mode and the non-archive log mode is that only cold backup can be performed in non-archive mode, and only full backup can be performed during recovery. data during the last full backup to system error cannot be recovered. archive mode supports hot backup, Incremental backup, and partial recovery. you can use archive log list to check whether the current mode is ARCHIVE or non-ARCHIVE. edit this section to configure the archiving mode of the database. change non-archive mode to archive mode: 1) SQL> conn/as sysdba (connect to the database as DBA) 2) SQL> shutdown immediate; (close the database immediately) 3) SQL> startup mount (start the instance and load the database, but do not open it) 4) SQL> alter database archivelog; (change the database to archive mode) 5) SQL> alter database open; (Open the database) 6) SQL> alter system archive log Start; (enable automatic archiving) 7) SQL> exit (exit) 8) SQL> SHUTDOWN Abort (as a last resort, such as an earthquake) for a full backup, because the backup logs generated in non-archive log mode are no longer available in archive mode. this step is not very important! 2. change archive mode to non-archive mode: 1) SQL> shutdown normal/IMMEDIATE; 2) SQL> startup mount; 3) SQL> alter database noarchivelog; 4) SQL> ALTER DATABASE OPEN; 3. enable automatic archiving: In LOG_ARCHIVE_START = TRUE archive mode, the log file group cannot be overwritten. When the log file is full, if the file is not archived manually, the system suspends, until the archiving is completed. at this time, you can only read but not write. shut down and restart the archiving LOG process SQL> archive log stop SQL> ARCHIVE LOG START 4. manual archiving: LOG_ARCHIVE_START = FALSE archiving of the current log File SQL> alter system archive log current; SQL> ALTER System archive log sequence 052; archive all log files SQL> alter system archive log all; change the archive log target SQL> ALTER SYSTEM ARCHIVE LOG CURRENT TO '& path'; 5. archive mode and non-archive mode are reversed in step 1. 6. configure multiple archiving processes Q: When do I need to use multiple archiving processes? A: If the archiving process consumes A lot of time, you can start multiple archiving processes. This is A dynamic parameter and can be modified dynamically using alter system. SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10; you can specify up to 10 dynamic performance views related to the archiving process in Oracle9i v $ bgprocess, v $ archive_processes 7. configure archiving targets, multiple archiving targets, remote archiving targets, and archiving log formats. Target LOG_ARCHIVE_DEST_n local archiving targets: SQL> LOG_ARCHIVE_DEST_1 = "LOCATION = D: ORACLEARCHIVEDLOG"; target of remote Archiving: SQL> LOG_ARCHIVE_DEST_2 = "SERVICE = STANDBY_DB1"; Mandatory archiving target. If an error occurs, retry after 600 seconds: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = "LOCATION = E: oraclearchivedlog mandatory reopen = 600 "; OPTIONAL archiving target. If an error occurs, discard Archiving: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 =" LOCATION = E: ORACLEARCHIVEDLOG OPTIONAL "; archiving target status: close archiving target and open archiving Target 1 SQL> alter system set LOG_ARCHIVE_DEST_STATE_1 = DEFER open archiving Target 2 SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE archive log format LOG_ARCHIVE_FORMAT 8. obtain archived LOG information V $ ARCHIVED_LOG V $ ARCHIVE_DEST V $ LOG_HISTORY V $ database v $ ARCHIVE_PROCESSES archive log list ;*/