My oracle9i Study Notes

Source: Internet
Author: User
Welcome to the Oracle community forum and interact with the 2 million technical staff to enter the workshop (u01oradatadb01log_01_db01.rdo) SIZE15M, GROUP2 (u01oradatadb01log_02_db01.rdo) SIZE

Welcome to the Oracle community forum and interact with 2 million technical staff> go to create database db01 maxlogfiles 10 maxdatafiles 1024 maxinstances 2 logfile GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo ') SIZE 15 M, GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Create database db01

Maxlogfiles 10

Maxdatafiles 1024

Maxinstances 2

Logfile

GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15 M,

GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15 M,

GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15 M,

Datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100 M,

Undo tablespace UNDO

Datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40 M

Default temporary tablespace TEMP

Tempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20 M

Extent management local uniform size 128 k

Character set AL32UTE8

National character set AL16UTF16

Set time_zone = 'America/New_York ';

############## Data dictionary ##########

Set wrap off

Select * from v $ dba_users;

Grant select on table_name to user/rule;

Select * from user_tables;

Select * from all_tables;

Select * from dba_tables;

Revoke dba from user_name;

Shutdown immediate

Startup nomount

Select * from v $ instance;

Select * from v $ sga;

Select * from v $ tablespace;

Alter session set nls_language = american;

Alter database mount;

Select * from v $ database;

Alter database open;

Desc dictionary

Select * from dict;

Desc v $ fixed_table;

Select * from v $ fixed_table;

Set oracle_sid = foxconn

Select * from dba_objects;

Set serveroutput on

Execute dbms_output.put_line ('sfasd ');

############ Control file ###########

Select * from v $ database;

Select * from v $ tablespace;

Select * from v $ logfile;

Select * from v $ log;

Select * from v $ backup;

/* Backup user tablespace */

Alter tablespace users begin backup;

Select * from v $ archived_log;

Select * from v $ controlfile;

Alter system set control_files = '$ ORACLE_HOME/oradata/u01/ctrl01.ctl ',

'$ ORACLE_HOME/oradata/u01/ctrl02.ctl' scope = spfile;

Cp $ ORACLE_HOME/oradata/u01/ctrl01.ctl $ ORACLE_HOME/oradata/u01/ctrl02.ctl

Startup pfile = '../initSID. ora'

Select * from v $ parameter where name like 'control % ';

Show parameter control;

Select * from v $ controlfile_record_p;

Select * from v $ tempfile;

/* Backup control file */

Alter database backup controlfile to '../filepath/control. Bak ';

/* Back up the control file and change the binary control file to an asc text file */

Alter database backup controlfile to trace;

############## Redo log ##############

Archive log list;

Alter system archive log start; -- start automatic archiving

Alter system switch logfile; -- force a log switch

Alter system checkpoint; -- force a checkpoint

Alter tablspace users begin backup;

Alter tablespace offline;

/* The checkpoint synchronization frequency parameter FAST_START_MTTR_TARGET. The higher the synchronization frequency, the shorter the system recovery time */

Show parameter fast;

Show parameter log_checkpoint;

/* Join a log Group */

Alter database add logfile group 3 ('/$ ORACLE_HOME/oracle/ora_log_file6.rdo' size 10 M );

/* Add a member to the log Group */

Alter database add logfile member '/$ ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;

/* Delete a log group: The current log group cannot be deleted; the active log group cannot be deleted; non-archive log groups cannot be deleted */

Alter database drop logfile group 3;

/* Delete a member in the log group, but the last member in each group cannot be deleted */

Alter databse drop logfile member '$ ORACLE_HOME/oracle/ora_log_file6.rdo ';

/* Clear online logs */

Alter database clear logfile '$ ORACLE_HOME/oracle/ora_log_file6.rdo ';

Alter database clear logfile group 3;

/* Clear non-archive logs */

Alter database clear unarchived logfile group 3;

/* Rename the log file */

Alter database rename file '$ ORACLE_HOME/oracle/ora_log_file6.rdo' to '$ ORACLE_HOME/oracle/ora_log_file6a.rdo ';

Show parameter db_create;

Alter system set db_create_online_log_dest_1 = 'path _ name ';

Select * from v $ log;

Select * from v $ logfile;

/* When the archive mode of a database is switched to the non-archive mode, it must be started to the mount State before it can be changed; startup mount;

Then open the database .*/

Alter database noarchivelog/archivelog;

Achive log start; --- start automatic archiving

Alter system archive all; -- manually archive all log files

Select * from v $ archived_log;

Show parameter log_archive;

###### Analyze the log file logmnr ##############

1) set utl_file_dir parameter in init. ora

2) Restart oracle

3) create directory file

Desc dbms_logmnr_d;

Dbms_logmnr_d.build;

4) add/remove log file

Dhms_logmnr.add_logfile

Dbms_logmnr.removefile

5) start logmnr

Dbms_logmnr.start_logmnr

6) query the analyzed content v $ logmnr_content -- sqlredo/sqlundo

Practice:

Desc dbms_logmnr_d;

/* Perform operations on the data table to prepare for the Restoration Operation */

Update table set qty = 10 where stor_id = 6380;

Delete table where stor_id = 7066;

/***********************************/

Utl_file_dir path

Execute dbms_logmnr_d.build ('foxdict. ora ',' $ ORACLE_HOME/oracle/admin/fox/cdump ');

Execute dbms_logmnr.add_logfile ('$ ORACLE_HOME/oracle/ora_log_file6.log', dbms_logmnr.newfile );

Execute dbms_logmnr.start_logmnr (dictfilename =>; '$ ORACLE_HOME/oracle/admin/fox/cdump/foxdict. ora ');

######### Tablespace ##############

Select * form v $ tablespace;

Select * from v $ datafile;

/* Ing between tablespaces and data files */

Select t1.name, t2.name from v $ tablespace t1, v $ datafile t2 where t1.ts # = t2.ts #;

Alter tablespace users add datafile 'path' size 10 M;

Select * from dba_rollback_segs;

/* Restrict the usage of a tablespace */

Alter user user_name quota 10 m on tablespace_name;

Create tablespace xxx [datafile 'path _ name/datafile_name '] [size xxx] [extent management local/dictionary] [default storage (xxx)];

Exmple: create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 m autoextend on next 5 m maxsize 200 M;

Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management dictionary default storage (initial 100 k next 100 k pctincrease 10) offline;

/* After 9i, oracle recommends using local management instead of dictionary management. because local uses bitmap to manage tablespaces, no voluntary competition for system tablespaces will be generated ;*/

Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local uniform size 1 m;

Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local autoallocate;

/* When creating a tablespace, set the segment space management mode in the tablespace. Automatic Management is used here */

Create tablespace userdata datafile '$ ORACLE_HOME/oradata/userdata01.dbf' size 100 M extent management local uniform size 1 m segment space management auto;

Alter tablespace userdata mininum extent 10;

Alter tablespace userdata default storage (initial 1 m next 1 m pctincrease 20 );

/* Undo tablespace (cannot be used in dictionary management mode )*/

Create undo tablespace undo1 datafile '$ ORACLE_HOME/oradata/undo101.dbf' size 40 M extent management local;

Show parameter undo;

/* Temporary tablespace */

Create temporary tablespace userdata tempfile '$ ORACLE_HOME/oradata/undo101.dbf' size 10 m extent management local;

[1] [2] [3] [4] [5]

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.