Oracle Learning Notes

1,oracle View:
User view, which has been user_ as a prefix, and records information about the users ' objects;
The all view, prefixed with All_, records the user object information and the object information that is authorized to access it.
DBA view, which is used to record information about all objects of a DB instance.
v$ view, which records performance statistics related to database activity information.
gv$ view that records dynamic information for all instances in a distributed environment.
2. Create a new control file:
Startup Nomount;
Create Controlfile
Noresetlogs
Noarchivelog
Maxlogfiles 100
Maxlogmembers 5
Maxdatafiles 100
Maxinstatances 10
Maxloghistory 449
LogFile
Group 1 ' d:\. \redo1.log ' size 50m,
Group 2 ' D:\. \redo2.log ' size 50m,
DataFile
' D:\. \.DBF ';
After the new control file is created, you need to set the control file of the Control_files parameter in the server parameter file, and let the new control file take effect.
Select name from V$controlfile;
alter system set Control_files = ' D:\. \.ctl ', scope=spfile;
ALTER DATABASE open;
If you use Resetlogs when you set up a control file, you must set the alert database open Resetlogs when you open the database

3, Backup control file: Alter Dabase backup Controlfile to ' D:.BKP ';

4. Recovery control File:
Shutdown immediate;
Startup
Query control File Location: Select Name,value from v$spparameter where name = ' Control_files ';
alter system set Control_files = ", scope = SPFile;
Shutdown immediate;

5. Create a log file group:
ALTER DATABASE ORCL add logfile Group 4
(' D:\. \.log ') size 10m;
Create a fourth set of log files.

6. Add log file members to the log file group:
ALTER DATABASE ORCL add logfile member ' d:\. \.log ' to group 4;
The log file can be queried for success from the V$logfile data dictionary.

7. Redefine log file members:
Shutdown
Startup Nomount
ALTER DATABASE Oracle rename file oldfilename to NewFileName;
startup Mount;

8. Delete log file group members:
ALTER DATABASE ORCL drop logfile member ' d:\. \.log ';
ALTER DATABASE ORCL drop logfile group groupnumber;

9, manually switch the log file group:
alter system switch logfile;

10, archive log: Inactive log backup.
Number of Query archive processes: Select Name,value from v$paramter where name = ' log_archive_max_processes ';
alter system Set Log_archive_max_processes = 8 scope = both;

11. Set the database archive mode:
ALTER DATABASE ORCL Noarchivelog
ALTER DATABASE ORCL Archivelog
Archive operations consume CPU and I/O resources frequently and can initiate multiple archive processes.

12. Set Archive target: directory where archived log files are stored
Show Paramter db_recovery_file_dest;
Localtion: local directory; service: remote directory.
Alter system set log_archive_dest= ' D:\. \.log ';

13, archive log format
%s: Log sequence number
%s: Log sequence number with leading 0
%t: Redo Thread Number
%T: Redo thread number, with 0
%A: Activity ID Number
%d: Database ID number
ID of the%r:resetlogs

Alter system set log_archive_format= '%s_%t_%r.arc ' scope=spfile

14. View data files, control files, log files:
Select name from V$datafile;
Select name from V$controlfile;
Select member from V$logfile;

15. Move the database table to another tablespace:
Table space that the query table belongs to
Select Tablespace,table from user_tables where tablename = ';
Table move
ALTER TABLE tablename move tablespace system;

16, using Data dictionary view to monitor user session information, v$session;
Terminate user session: Alter system kill session ' Sid,serial ' (two fields from view query, meanwhile, separated by good);
Query the most recently executed SQL statement using the data field view v$open_cursor;


17,data Pump Data Pump tool '
Creating directory: Create directory Name (directory object) as Directory_path (directory);
Create directory dir as ' D:\app\administrator\admin\orcl\dbdump\directory '
EXPDP Commands and Parameters

Oracle Learning Notes