感觉Oracle 的study guide是挺好的资料, 里面包括了oracle中大部分基础的知识要点和基本的操作步骤. 有时间还是值得在过一下这些文档. 在此先贴上自己以前在学习study Guide I中做的一些操作笔记

-------------------------------------------- Chapter 3 -----------------------------------------------

-- Create a tablespace
CREATE TABLESPACE TEST2
DATAFILE SIZE 25G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT auto;

-- Create a temporary tablespace
CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:ORACLEORADATAORA10TEMP01.DBF' SIZE 2G;

-- Workaround for Deferred Temp File Disk Space Allocations
-- first create it as a permanent tablespace
-- to force the disk space to be allocated
CREATE TABLESPACE temp
DATAFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G;

-- dropping the tablespace does not remove
-- the file from the file system
DROP TABLESPACE temp;

-- the keyword REUSE is needed to use the existing
-- file created in the previous steps
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/ORADATA/PROD/TEMP01.DBF' SIZE 2G REUSE

-- Rename tablespace
alter tablespace test rename to test2;

-- Removing a Tablespace
DROP TABLESPACE TEST INCLUDING CONTENTS( cascade constraints) AND DATAFILES;

-- Alter tablespace in online or offline mode
ALTER TABLESPACE TEST offline immediate;
Alter TABLESPACE TEST online -- -- First, need to do RECOVER operation before online tablespace if immediate operation used

-- Alter tablespace in read only or read write mode
ALTER TABLESPACE TEST READ ONLY / READ WRITE;

-- Alter tablespace in backup mode
ALTER TABLESPACE system BEGIN BACKUP;
ALTER TABLESPACE system END BACKUP;

-- Generate DDL statement
set long 5000
select dbms_metadata.get_ddl('TABLESPACE','USER_INDEX') UDDL from dual;

-- Resize datafile
ALTER DATABASE DATAFILE 'C:ORACLEORADATAORA10DATA01.DBF' RESIZE 2000M

-- Auto extented datafile
ALTER DATABASE DATAFILE 'C:ORACLEORADATAORA10DATA01.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 8000M;

-- Relocate a datefile
-- offline datefile
alter database datefile 'C:ORACLEDATA02.DBF' OFFLINE;

-- Using host copy datefile to a new location

-- alter datafile path in control file
alter database RENAME FILE 'C:ORACLEDATA02.DBF' TO 'C:ORACLEORADATAORA10DATA02.DBF';

-- sync the file header with the database
recover DATAFILE 'C:ORACLEORADATAORA10DATA02.DBF';

-- Online datafile
alter database datafile 'C:ORACLEORADATAORA10DATA02.DBF' online;

-- Create a table through query
CREATE TABLE TEST
NOLOGGING COMPRESS
TABLESPACE USER
AS SELECT * FROM change_log
WHERE when BETWEEN TO_DATE('01-JAN-2004','DD-Mon-YYYY’) AND TO_DATE('31-JAN-2004','DD-Mon-YYYY’);
-- Note options regarding to NOLOGGIN and COMPRESS
-- abbreviation CTAS ( Create Table As Select ):

-- Creating a Temporary Table
CREATE GLOBAL TEMPORARY TABLE my_session
(category VARCHAR2(16)
,running_count NUMBER
) ON COMMIT DELETE ROWS / ON COMMIT PRESERVE ROWS;
-- causes the temp table to flush at the end of each transaction
-- causes the temp table to flush at the end of each session

-- Data dictinary for comment on table or column
DBA_TAB_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

DBA_COL_COMMENTS
ALL_COL_COMMENTS
USER_COL_COMMENTS

-- USING INDEX clause,
CREATE TABLE employees
(employee_id NUMBER NOT NULL,
hire_date DATE NOT NULL,
CONSTRAINT uniq_payroll_id UNIQUE (employee_id)
USING INDEX TABLESPACE indx_TEST
);

-- Use EXCEPTION INTO to identifying any duplicated
-- Create exception table
@/$oracle_home/rdbms/admin/utlexcpt.sql( or utlexpt1.sql );

-- Use exceptino into clause
ALTER TABLE employees ADD
CONSTRAINT uniq_payroll_id UNIQUE (payroll_id)
USING INDEX TABLESPACE indx
EXCEPTIONS INTO exceptions;


-- Create foreign key
ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr)
REFERENCES departments(dept_nbr) ON DELETE CASCADE / ON DELETE SET NULL;

-- Deferred Constraint Checking
ALTER TABLE departments ADD CONSTRAINT
dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES
employees(employee_id) ON DELETE SET NULL
DEFERRABLE INITIALLY DEFERRED / Immediate;

-- Rebuild Index
ALTER INDEX INDX_TEST REBUILD TABLESPACE INDEX_TEST;
ALTER INDEX INDX_TEST COALESCE; -- Coalescing an index is like a quick-and-dirty rebuild. Instead of re-creating the Btree, a coalesce combines entries in nearby leaf blocks with the intent of freeing space from some of the leaf blocks

------------------------------------------------------ Chapter 3 -----------------------------------------------

IM3520564

------------------------------------------------------ Chapter 6 -----------------------------------------------

SET ROLE ALL EXCEPT admin_test;

SELECT * FROM session_roles;

select * from user_role_privs;

-- set the default role
ALTER USER DEFAULT ROLE "role_list"
ALTER USER scott DEFAULT ROLE ALL EXCEPT "role_list";

-- Assign resource limit with profile
ALTER SYSTEM SET resource_limit = TRUE SCOPE = BOTH;
CREATE PROFILE TEST LIMIT CONNECT_TIME 10;
ALTER PROFILE TEST LIMIT CONNECT_TIME UNLIMITED;

-- Expire and lock account
alter user TEST password expire account lock;

-- Audit trail
alter system set audit_trail='DB/DB_EXTENDED/OS';

audit insert table by harry;

audit insert table by harry whenever not successful;

-- View what's auidt opoint enabled
select * from dba_stmt_audit_opts;

-- check audited entry
select * from sys.aud$ -- through a underlying table

select * from dba_audit_trail; -- through a view

-- Disable auditing
noaudit insert table by harry;

-- Enable privilege audit
audit create any table by harry by access;

-- Check enabled privilege audit
select * from dba_priv_audit_opts;

-- Disable
noaudit create any table by harry;

-- enable audit on object
audit select on harry.test by access/session whenever successful/ not successful;

-- Identifying enalbed audit object
select * from dba_obj_audit_opts
where owner='harry' and
object_name='TEST';
-- Note: a dash (-) to indicate no auditing is enabled), an A to indicate BY ACCESS, or an S to indicate BY SESSION. The first code (preceding the slash) denotes the action for successful statements, and the second code (after the slash) denotes the action for unsuccessful statements.

-- Disable object audit
noaudit select on harry.text whenever sucessful;

-- Purging audit trail data
-- Older than 90 days
delete from sys.aud$ WHERE timestamp# < SYSDATE -90;

-- Create disabled FGA policy
begin
DBMS_FGA.ADD_POLICY(object_schema=>'HARRY'
,object_name=>'TEST2'
,policy_name=>'HARRY_TEST'
,audit_column=>'NAME,ADDRESS'
,enable=>FALSE
,statement_types=>'SELECT');
end;
/

-- Enable FGA policy
begin
DBMS_FGA.ENABLE_POLICY(object_schema=>'HARRY'
,object_name=>'TEST2'
,policy_name=>'HARRY_TEST');
end;
/

--Disabling an FGA Policy
begin
DBMS_FGA.DISABLE_POLICY(object_schema=>'HARRY'
,object_name=>'TEST2'
,policy_name=>'HARRY_TEST');
end;
/

--Dropping an FGA Policy
begin
DBMS_FGA.DROP_POLICY(object_schema=>'HARRY'
,object_name=>'TEST2'
,policy_name=>'HARRY_TEST');
end;
/

-- Identify FGA plicy
select * FROM dba_audit_policies;

-- Identifying FGA audit trail
SELECT db_user, timestamp, userhost, sql_text
FROM dba_fga_audit_trail
WHERE policy_name
/

------------------------------------------------------ Chapter 6 -----------------------------------------------

------------------------------------------------------ Chapter 7 PL/SQL -----------------------------------------------
-- Insert into mutiple tables
Insert first/all
when 1 = 1 then
into test2(name, address )
values( name, address )
when 1!= 2 then
into test3(name, address )
values( name, address )
select name, address
from test4;

-- Update mutiple columns
update test2
set( name, address ) = (select 'aaaa','chongqing' from dual )
where id=4
/

-- Delete duplicate row
DELETE test2
WHERE rowid NOT IN (SELECT MAX(rowid)
FROM test2
GROUP BY (id) )
/

-- Create directory
create directory TEST_DIR as 'C:oracleredandencebackup';
grant read, write on directory TEST_DIR to public;

------------------------------------------------------ Chapter 7 PL/SQL --------------------------------------------------

------------------------------------------------------ Chapter 8 Managing Consistency and Concurrency --------------------------------------------------

---Check current active undo segment
select * from v$rollname;

-- Check both active and inactive undo segment
select * from dba_rollback_segs

-- Check transiaction and undo segment relationship
set transaction name 'relationship test'

select xid, status, start_time, xidusn seg_num,r.name seg_name
from v$transaction t, v$rollname r
where t.xidusn = r.usn and
t.name = 'relationship test';

--set read only transaction
set transaction read only;

-- view undo space usage for the interval last serveral 10 Mins periods
select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') starttime,
to_char(end_time,'yyyy-mm-dd hh24:mi:ss') endtime,
undoblks,
maxquerylen maxqrylen
from v$undostat;

-- calculate redo size
undo_tablespace_size = ( undo_retention ) * ( select max(undoblks) from v$undostat ) * ( db_block_size )
-- NOTE: Add about 10 to 20 percent to this calculation for unexpected spikes in undo usage.

-- Guaranteeing Undo Retention
alter tablespace undotbs1 retention guarantee;
alter tablespace undotbs1 retention noguarantee;

-- Terminal a session
alter system kill session 'SID, SERIAL#';

------------------------------------------------------ Chapter 8 Managing Consistency and Concurrency --------------------------------------------------

------------------------------------------------------ Chapter 9 Performance Monitoring --------------------------------------------------
-- Check AWR setting
execute dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>43200); -- The parameter is in minutes

-- Monitory invalid object
select owner, object_name , object_type
from dba_objects
where status='INVALID';

ALTER VIEW sys.dba_hist_filestatxs COMPILE;

ALTER PROCEDURE sh.p_update_sales_history COMPILE;

-- Check unusable index
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE status = 'UNUSABLE';

ALTER INDEX hr.job_id_pk REBUILD;

------------------------------------------------------ Chapter 9 Performance Monitoring --------------------------------------------------

------------------------------------------------------ Chapter 10 Implementing database Backup --------------------------------------------------

-- forcing a checkpoint
alter system checkpoint;

--Forcing a log switch
alter system switch logfile;

------ mamange redo log file

-- Add a redo log group
alter database add logfile group 4
('/storage/ora_data/harry/redo04.log',
'/storage/ora_add_data/harry/redo04_bk1.log') size 50m
;

-- Add more log file in a group
alter database add logfile member '/storage/ora_add_data/harry/redo03_bk1.log' to group 2;

-- Rename log file
shtudown immediate
startup mount;
alter database rename file 'old logfile name' to 'new logfile name';
alter database open;

-- drop logfile group
alter database drop logfile group 4;

-- drop a logfile member
alter database drop logfile member 'logfile full path';

-- Clear log file
ALTER DATABASE CLEAR LOGFILE GROUP 3;

---- Enable archived mode
-- related parameter
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_FORMAT

alter system set log_archive_dest_1='location=/storage/ora_add_data/harry/archived mandatory reopen=60';
shutdown immediate;
startup mount
alter database archivelog / NOARCHIVELOG;
alter database open;

select dbid, name, created, log_mode from v$database;

----------- Flash area
-- two parameter
db_recovery_file_dest_size
db_recovery_file_dest

-- Error can be fond through the view as below
select * from DBA_OUTSTANDING_ALERTS

-- Backup control file
alter database backup controlfile to trace;

alter database backup controlfile to 'filename.bkp';

-------------------- RMAN
-- ----Create catalog
create tablespace rmantablespace datafile size 100m;

create user rman
identified by rman123
default tablespace rmantablespace;

rman target /
connect catalog rman/rman123@rman

create catalog tablespace rmantablespace;
register database;

show all;

------------------------------------------------------ Chapter 9 Implementing database Backup --------------------------------------------------