Oracle DBA

1) 用SQLPLUS连接数据库

SQLPLUS SYS/Abcd1234#@orcl AS SYSDBA

CONNECT store/store_password

2) 创建用户

CREATE USER store IDENTIFIED BY store_password;

ALTER USER store IDENTIFIED BY store_pwd;

 

3)赋予用户权限

GRANT CONNECT, RESOURCES TO store;

GRANT CREATE VIEW TO store;

 

4) Create a Central Plan Table

SQL>@ D:\oracle_11g\product\11.1.0\db_1\RDBMS\ADMIN\utlxplan.sql

CREATE PUBLIC SYNONYM plan_table FOR plan_table;

GRANT SELECT, INSERT, UPDATE, DELETE ON plan_table TO PUBLIC;

 

5) Flashback Data Archives

• View rows as they were at a specific timestamp
• View rows as they were between two timestamps

CREATE FLASHBACK ARCHIVE test_archive

TABLESPACE audit_archive

QUOTA 1 M

RETENTION 1 DAY;

 

ALTER TABLE store.products FLASHBACK ARCHIVE test_archive;

ALTER TABLE store.products NO FLASHBACK ARCHIVE;

ALTER FLASHBACK ARCHIVE test_archive MODIFY RETENTION 2 YEAR;

ALTER FLASHBACK ARCHIVE test_archive PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY);

ALTER FLASHBACK ARCHIVE test_archive PURGE ALL;

DROP FLASHBACK ARCHIVE test_archive;

 

查看Archive

SELECT * FROM store.products AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE);

SELECT * FROM store.products AS OF TIMESTAMP('2012-03-03 12:05:00', 'YYYY-MM-DD HH24:MI:SS');

SELECT * FROM store.products AS OF TIMESTAMP('2012-03-03 12:05:00', 'YYYY-MM-DD HH24:MI:SS') AND TIMESTAMP('2012-03-03 12:25:00', 'YYYY-MM-DD HH24:MI:SS');

SELECT * FROM store.products AS OF TIMESTAMP('2012-03-03 12:05:00', 'YYYY-MM-DD HH24:MI:SS') AND MAXVALUE;