Summary of Basic Common Commands in Oracle Database

1. Get the database name and creation date
SELECT name, created, log_mode, open_mode FROM v$database;

2. Host name of ORACLE database computer, instance name of ORACLE database and version information of ORACLE database management system
SELECT host_name, instance_name, version FROM v$instance;

3. In order to know some special information about oracle database version
select * from v$version;

4. Get the control file name
select * from v$controlfile;

5. Get the redo log configuration information of Oracle database
SELECT group#, members, bytes, status, archived FROM v$log;
select GROUP#,MEMBER from v$logfile;

6. Get the specific location of each redo log (member) file of oracle
select * from v$logfile;

7. Know the backup and recovery strategy of ORACLE database and the specific location of archive files
archive log list

8. Know how many tablespaces are in the ORACLE database and the state of each tablespace
select tablespace_name, block_size, status, contents, logging from dba_tablespaces;
select tablespace_name, status from dba_tablespaces;

9. Know which disk each tablespace exists on and the name of the file
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 are on the Oracle database system and when they were created
select username,created from dba_users;
select username, DEFAULT_TABLESPACE from dba_users;

11. Fetching information from the control file involves the following 1 related commands

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: the reusable part and the non-reusable part. The size of the reusable portion can be controlled by the CONTROL_FILE_RECORD_KEEP_TIME parameter, which defaults to 7 days. The content of the reusable portion is retained for 7 days and may be overwritten after 1 week. The reusable part is used by the recovery manager, and the contents of this part can be automatically extended. The Oracle database administrator can use the following keywords (parameters) in the CREAT DATABASE or CREAT CONTROLFILE statement to indirectly affect the size of the non-reusable portion:

MAXDATAFILES
MAXINSTANCES
MAXLOGFILES
MAXLOGHISTORY
MAXLOGMEMBERS

13. View the configuration of the control file
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 format the output first using the SQL*Plus command similar to set pagesize 100. The relevant formatting output commands are as follows:
record_size: The number of bytes per record.
records_total: The number of records allocated for this segment.
records_used: The number of records used for this segment.

15. Know the records used by all data files (DATAFILE), table spaces (TABLESPACE), and redo logs (REDO LOG) in the control file
SELECT type, record_size, records_total, records_used
FROM v$controlfile_record_section
WHERE type IN ('DATAFILE', 'TABLESPACE', 'REDO LOG');

16. Get the control file name
select value from v$parameter where name ='control_files';
Or: select * from v $controlfile

17. How do I add or move control files to an installed Oracle database?
Here are the steps to add or move control files to a 1 installed Oracle database:

a, using the data dictionary v $controlfile to get the name of the existing control file.

b, shut down the Oracle database normally.

c, adding the new control file name to the CONTROL_FILES parameter of the parameter file.

d, using the operating system's copy command to copy an existing control file to a specified location.

e, restart the Oracle database.

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

g, if there is an error, redo the above operation, if there is no error, delete useless old control files.

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

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 important file, you should backup the control file immediately after the structure of the database changes, in addition to the above-mentioned protection measures of storing multiple copies of the control file on different hard disks. Control files can be backed up with the Oracle command:
alter database backup controlfile to 'D:\ backup\ control. bak';

19. You can also back up to a trace file. The trace file contains the SQL statement required to rebuild the control file. You can use the following SQL statement to generate this 1 trace file:
alter database backup controlfile to trace;

20. Turn off the oracle command normally
shutdown immeditae