Oracle Database Basic Common command Rollup _oracle

1. Get database name and date created
SELECT name, created, Log_mode, open_mode from V$database;

2, the Oracle database computer host name, Oracle database instance name and Oracle Database management system version information
SELECT host_name, instance_name, version from V$instance;

3. To know some special information about Oracle database version
SELECT * from V$version;

4. Get control File name
SELECT * from V$controlfile;

5, get the Oracle database redo log configuration information
SELECT group#, members, Bytes, status, archived from V$log;
Select Group#,member from V$logfile;

6. Get the exact location of each redo log (member) file for Oracle
SELECT * from V$logfile;

7, know the Oracle database backup and recovery strategy and the location of the archive
Archive Log List

8. Know how many table spaces are in the Oracle database and the state of each table space
Select Tablespace_name, Block_size, status, contents, logging from dba_tablespaces;
Select Tablespace_name, status from Dba_tablespaces;

9, know each table space on which disk and the name of the file and other information
SELECT file_id, file_name, tablespace_name, status, bytes from Dba_data_files;
Select file_name, tablespace_name from Dba_data_files;

10. Know how many users and when the Oracle database system was created
Select username,created from Dba_users;
Select Username, default_tablespace from dba_users;

11, from the control file to remove information related to the following relevant commands

Copy Code code as follows:

SELECT * FROM v$archived
SELECT * FROM V$archived_log
SELECT * FROM V$backup
SELECT * FROM V$database
SELECT * FROM V$datafile
SELECT * FROM V$log
SELECT * FROM V$logfile
SELECT * FROM V$loghist
SELECT * FROM V$tablespace
SELECT * FROM V$tempfile

12. The control file consists of two parts: a reusable part and a component that is not reusable. The size of the reusable part is availableControl_file_record_keep_time parameter to control, the default value of this parameter is 7 days, the content of the part can be reused for 7 days, after a week this part of the content may be overwritten. The reusable part is for the recovery manager to use, and this part of the content can be automatically extended. Oracle database administrators can indirectly influence the size of creat parts by using the following keywords (parameters) in the database or creat controlfile statement:

Copy Code code as follows:

Maxdatafiles
Maxinstances
Maxlogfiles
Maxloghistory
Maxlogmembers

13, view the configuration of control files
SELECT type, record_size, Records_total, records_used from V$controlfile_record_section;

14, if your display is divided into two parts, you need to use a sql*plus command similar to set pagesize 100 to format the output first. The following are the formatted output commands for:
Record_size: The number of bytes per record.
Records_total: The number of records assigned to the segment.
Records_used: The number of records used for this segment.

15, know the control file of all data files (datafile), tablespace (tablespace), and redo logs (REDO log) used by the record
SELECT type, record_size, Records_total, records_used
From V$controlfile_record_section
WHERE type in (' DataFile ', ' tablespace ', ' REDO LOG ');

16. Get control File name
Select value from v$parameter where name = ' Control_files ';
Or: SELECT * from V$controlfile

17. How to add or move control files in an Oracle database that has already been installed?
The following are steps to add or move a control file in an Oracle database that is already installed:

A, the use of data dictionary v$controlfile to obtain the name of the existing control file.

b, gracefully shut down the Oracle database.

C, add the new control file name to the Control_files parameter of the parameter file.

D, use the Copy command of the operating system to copy the existing control files to the specified location.

E, restart the Oracle database.

F, use the data dictionary v$controlfile to verify that the new control file name is correct.

G, if the error redo the above operation, if the correct deletion of useless old control files.

Note: If you use the server initialization parameter file (SPFILE), you cannot close the Oracle database and should use the Oracle command of ALTER system set control_files in step 3rd to change the location of the control file.

Copy Code code as follows:

Sql> alter system Set Control_files =
' D:\Disk3\CONTROL01. CTL ',
' D:\Disk6\CONTROL02. CTL ',
' D:\Disk9\CONTROL03. CTL ' Scope=spfile;

18,Since the control file is an extremely desired file, you should back up the control file immediately after the structure of the database changes, in addition to the above mentioned protection measures for multiple copies of the control file. You can use the Oracle command to back up the control file:
ALTER DATABASE backup Controlfile to ' D:\backup\control.bak ';

19, You can also backup to a tracking file. The trace file contains the SQL statements required to rebuild the control file. You can use the following SQL statement to generate this trace file:
ALTER DATABASE backup Controlfile to trace;

20, the normal shutdown Oracle command
Shutdown Immeditae