OCP 1Z0‑082 最全面、最系统、必须死记的命令大全

Oracle OCP 082 必背命令全集（最全版）

一、实例启动与关闭（必考）

startup nomount;          -- 仅启动实例，读 spfile/pfile
startup mount;            -- 加载控制文件
startup;                  -- 完全打开
alter database open;

shutdown normal;          -- 等待会话结束
shutdown transactional;   -- 等待事务结束
shutdown immediate;       -- 推荐，回滚+断开+关闭
shutdown abort;           -- 强制崩溃（仅故障）

alter system checkpoint;
alter system switch logfile;
alter system archive log current;

二、参数文件 SPFILE / PFILE

create pfile from spfile;
create spfile from pfile;

show parameter name;

alter system set parameter=value scope=memory;
alter system set parameter=value scope=spfile;
alter system set parameter=value scope=both;

alter system reset parameter scope=spfile;

三、用户、权限、角色

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 100M on users;
alter user c##user quota unlimited on users;

grant create session, create table, create view, create sequence, create procedure to c##user;
grant connect, resource to c##user;
grant dba to c##user;
grant select any table to c##user;

revoke dba from c##user;

create role myrole;
grant select on emp to myrole;
grant myrole to c##user;
drop role myrole;

四、表空间、数据文件、临时表空间

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;
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;

五、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 group#, thread#, sequence#, 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 database clear logfile group 4;
alter database clear unarchived logfile group 4;

七、控制文件

show parameter control_files;

alter system set control_files='/path/ctl01.ctl','/path/ctl02.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='%t_%s_%r.arc' scope=spfile;

九、表、约束、索引

create table emp(id number,name varchar2(20));
alter table emp add constraint pk_emp primary key(id);
alter table emp modify name varchar2(30);
alter table emp add (sal number);
alter table emp drop column sal;
alter table emp rename column name to ename;

truncate table emp;
drop table emp;
drop table emp purge;

alter table emp enable row movement;
flashback table emp to before drop;

create index idx_emp_id on emp(id);
create unique index idx_emp_ename on emp(ename);
alter index idx_emp_id rebuild;
alter index idx_emp_id rebuild online;
drop index idx_emp_id;

十、闪回技术（082 高频）

-- 闪回查询
select * from emp as of timestamp systimestamp - interval '5' minute;
select * from emp as of scn 123456;

-- 闪回表
alter table emp enable row movement;
flashback table emp to timestamp systimestamp - interval '5' minute;
flashback table emp to scn 123456;

-- 闪回删除
flashback table emp to before drop;

十一、数据泵 expdp / impdp

create directory dump_dir as '/u01/dump';
grant read, write on directory dump_dir to system;

-- 导出
expdp system/pass schemas=scott directory=dump_dir dumpfile=scott.dmp logfile=scott.log
expdp ... full=y
expdp ... tables=emp
expdp ... content=data_only
expdp ... exclude=index

-- 导入
impdp system/pass schemas=scott directory=dump_dir dumpfile=scott.dmp
impdp ... remap_schema=scott:user1
impdp ... remap_tablespace=users:users2
impdp ... table_exists_action=append/replace/truncate

十二、核心动态视图（必须背）

select instance_name, status from v$instance;
select name, open_mode, log_mode from v$database;
select * from v$datafile;
select * from v$tempfile;
select * from v$log;
select * from v$logfile;
select * from v$controlfile;
select * from v$parameter;
select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_users;
select * from dba_objects;

十三、管理会话、锁、杀会话

select sid, serial#, username from v$session;
alter system kill session 'sid,serial#';

---