一、实例启动与关闭（必考）
startup nomount; -- 仅启动实例（读取spfile/pfile）
startup mount; -- 加载控制文件
startup; -- 打开数据库
alter database open;shutdown normal; -- 等待会话结束
shutdown transactional;-- 等待事务结束
shutdown immediate; -- 推荐：回滚、断开、关闭
shutdown abort; -- 强制崩溃（仅故障）alter system checkpoint; -- 手动检查点
alter system flush buffer_cache; -- 刷新缓存
alter system flush shared_pool;
二、参数文件 SPFILE / PFILE
create pfile from spfile;
create spfile from pfile;show parameter sga;
show parameter pga;
show parameter processes;alter system set sga_target=2G scope=both;
alter system set sga_max_size=2G scope=spfile;
alter system set pga_aggregate_target=1G scope=both;
alter system set processes=500 scope=spfile;
alter system reset parameter_name scope=spfile;
三、用户、权限、角色
-- CDB 模式必须加 c##
create user c##user identified by pass default tablespace users temporary tablespace temp;alter user c##user identified by newpass;
alter user c##user account lock;
alter user c##user account unlock;
alter user c##user quota unlimited on users;
alter user c##user quota 100M on users;grant connect, resource to c##user;
grant dba to c##user;
grant create session, create table, create view, create sequence to c##user;
grant select any table to c##user;
grant alter database to c##user;revoke select on scott.emp from c##user;-- 角色
create role role1;
grant select on emp to role1;
grant role1 to c##user;
drop role role1;
四、表空间、数据文件
create tablespace users
datafile '/u01/oradata/ORCL/users01.dbf' size 100M
autoextend on next 10M maxsize unlimited
extent management local segment space management auto;create temporary tablespace temp tempfile '/u01/oradata/ORCL/temp01.dbf' size 50M autoextend on;alter tablespace users add datafile '/path/users02.dbf' size 100M autoextend on;
alter database datafile '/path/users01.dbf' resize 200M;
alter database datafile '/path/users01.dbf' autoextend on maxsize 500M;alter tablespace users read only;
alter tablespace users read write;
alter tablespace users offline;
alter tablespace users online;
alter tablespace users offline immediate;drop tablespace users including contents and datafiles;-- 查看
select tablespace_name, status from dba_tablespaces;
select file_name, tablespace_name from dba_data_files;
select file_name, tablespace_name from dba_temp_files;
五、重做日志、日志组、日志切换
select group#, status from v$log;
select member from v$logfile;alter database add logfile group 4 ('/path/redo04.log') size 50M;
alter database add logfile member '/path/redo04b.log' to group 4;alter database drop logfile group 4;
alter database drop logfile member '/path/redo04b.log';alter system switch logfile;
alter system archive log current;alter database clear logfile group 4;
alter database clear unarchived logfile group 4;
六、控制文件
show parameter control_files;alter system set control_files='/path/control01.ctl','/path/control02.ctl' scope=spfile;-- 备份控制文件
alter database backup controlfile to '/path/control.bkp';
alter database backup controlfile to trace; -- 生成重建脚本
七、归档模式 ARCHIVELOG（必考）
archive log list;-- 开启归档（mount 状态）
alter database archivelog;
alter database noarchivelog;alter system set log_archive_dest_1='location=/u01/arch' scope=both;
alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile;alter system archive log all;
八、表、索引、约束、分区
create table t(id number,name varchar2(10)) tablespace users;
alter table t add constraint pk_t primary key(id);
alter table t modify name varchar2(20);
alter table t drop column name;
alter table t rename column id to id_new;
truncate table t;
drop table t purge;
drop table t;
flashback table t to before drop;-- 索引
create index idx_t_id on t(id);
create unique index idx_unq_name on t(name);
alter index idx_t_id rebuild;
alter index idx_t_id rebuild online;
drop index idx_t_id;-- 约束
alter table t add constraint fk_dept foreign key(deptno) references dept(deptno);
alter table t disable constraint fk_dept;
alter table t enable constraint fk_dept;
九、Undo 回滚表空间
create undo tablespace undotbs2 datafile '/path/undo02.dbf' size 100M autoextend on;alter system set undo_tablespace=undotbs2 scope=both;
alter system set undo_retention=900 scope=both;show parameter undo;
select tablespace_name from dba_tablespaces where contents='UNDO';
十、闪回技术（OCP 超级重点）
-- 闪回查询
select * from emp as of timestamp systimestamp - interval '10' minute;
select * from emp as of scn 123456;-- 闪回表
alter table emp enable row movement;
flashback table emp to timestamp systimestamp - interval '10' minute;
flashback table emp to scn 123456;-- 闪回删除
flashback table emp to before drop;
flashback table emp to before drop rename to emp_old;-- 闪回数据库（必须 mount、归档、闪回开启）
alter database flashback on;
shutdown immediate
startup mount
flashback database to timestamp systimestamp - interval '1' hour;
flashback database to scn 123456;
alter database open resetlogs;-- 闪回数据归档
create flashback archive fda1 tablespace users retention 1 year;
alter table emp flashback archive fda1;
十一、数据泵 expdp / impdp（必考）
create directory dump_dir as '/u01/dump';
grant read,write on directory dump_dir to system;-- 导出
expdp system/password schemas=scott directory=dump_dir dumpfile=scott.dmp logfile=scott.log
expdp ... full=y -- 全库
expdp ... tables=emp -- 单表
expdp ... exclude=index -- 排除索引
expdp ... content=data_only -- 只导数据-- 导入
impdp system/password schemas=scott directory=dump_dir dumpfile=scott.dmp
impdp ... remap_schema=scott:c##user
impdp ... remap_tablespace=users:users2
impdp ... table_exists_action=append/replace/truncate
十二、RMAN 备份恢复（OCP 最难、必考）
rman target /-- 配置
configure controlfile autobackup on;
configure retention policy to redundancy 2;
configure device type disk parallelism 2;
show all;-- 备份
backup database plus archivelog delete all input;
backup archivelog all delete all input;
backup as compressed backupset database;
backup current controlfile;
backup spfile;
backup tablespace users;
backup datafile 1;-- 校验
crosscheck backup;
crosscheck archivelog all;
delete expired backup;
delete obsolete;-- 恢复（nocatalog）
shutdown immediate
startup mount
restore database;
recover database;
alter database open resetlogs;-- 恢复表空间
sql 'alter tablespace users offline immediate';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online';
十三、ASM 常用（OCP 19c 必考）
-- asmcmd
lsdg
lsdsk
lsct
cpcreate diskgroup data external redundancy disk '/dev/oracleasm/disks/DATA1';
alter diskgroup data add disk '/dev/oracleasm/disks/DATA2';
alter diskgroup data drop disk DATA1;
十四、CDB / PDB 多租户（19C 核心）
-- 连接 CDB
conn / as sysdba
show con_id
show con_name-- 查看 PDB
show pdbs;
select pdb_name, status from dba_pdbs;-- 启动关闭 PDB
alter pluggable database pdb1 open;
alter pluggable database pdb1 close immediate;
alter pluggable database all open;
alter pluggable database all close immediate;-- 切换 PDB
alter session set container=pdb1;-- 创建 PDB
create pluggable database pdb1 admin user pdb_admin identified by pass
file_name_convert=('pdbseed','pdb1');-- 删除 PDB
drop pluggable database pdb1 including datafiles;
十五、常用诊断视图（必须背）
select instance_name, status from v$instance;
select name, open_mode from v$database;
select log_mode from v$database;select * from v$datafile;
select * from v$log;
select * from v$logfile;
select * from v$controlfile;
select * from v$parameter;
select * from v$session;
select * from v$process;
select * from v$sysstat;
select * from v$sgainfo;
select * from v$pgastat;
select * from v$recovery_file_dest;
select * from v$flashback_database_log;
十六、ASM + 管理命令
select group_number, name, state from v$asm_diskgroup;
select path, mount_status, header_status from v$asm_disk;
十七、Job、Scheduler 简单命令
begin
dbms_scheduler.create_job(...);
end;
/alter job job1 enable;
alter job job1 disable;
