Oracle常用命令

1. 基础命令

1、 以oracle身份登录数据库，命令：su - oracle
2、 进入Sqlplus控制台，命令：sqlplus / nolog
3、 以系统管理员登录，命令：connect / as sysdba
　　可以合并为：sqlplus sys/密码 as sysdba
4、 启动数据库，命令：startup
5、 如果是关闭数据库，命令：shutdown immediate（shutdown abort）
6、 退出sqlplus控制台，命令：exit
7、 进入监听器控制台，命令：lsnrctl
8、 停掉监听器，命令：stop 
9、 启动监听器，命令：start
10、退出监听器控制台，命令：exit
11、重启数据库结束
12、查看数据库监听的状态：lsnrctl status

2. 多租户模式操作

查看容器：

show pdbs;

修改容器：

alter seeion container='容器';
ALTER SESSION SET container=OAPDB;

3. 表空间操作

3.1 查询表空间大小

-- 表空间基础信息
select * from Dba_Tablespaces;
select TABLESPACE_NAME from Dba_Tablespaces;

-- 常规表空间大小
SELECT
	file_name,
	tablespace_name,
	( bytes / 1024 / 1024 ) bytes_M,
	autoextensible,
	( maxbytes / 1024 / 1024 ) maxbytes_M,
	( user_bytes / 1024 / 1024 ) user_bytes_M 
FROM
	dba_data_files;
	

-- 临时表空间大小
SELECT
	file_name,
	tablespace_name,
	( bytes / 1024 / 1024 ) bytes_M,
	autoextensible,
	( maxbytes / 1024 / 1024 ) maxbytes_M,
	( user_bytes / 1024 / 1024 ) user_bytes_M 
FROM
	dba_temp_files;

3.2 创建表空间

-- 临时表空间
create temporary tablespace cs01_temp tempfile '/data/cs01_temp.DBF' size 200m autoextend on next 50m maxsize UNLIMITED extent management local;

-- 普通表空间
CREATE TABLESPACE CS01 DATAFILE '/data/CS.DBF' SIZE 1024M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

3.3 修改表空间大小

-- 修改表空间大小
alter database datafile '/apps/oracle/oradata/ORCL/undotbs01.dbf' resize 30720M;
alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF' resize 30720M;

-- 修改临时表空间大小
ALTER DATABASE TEMPFILE '/apps/oracle/oradata/ORCL/temp01.dbf' RESIZE 30720M;
ALTER DATABASE TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF' RESIZE 30720M;

3.4 添加表空间数据文件

ALTER tablespace CS01 ADD datafile '/data/CS01.DBF' SIZE 1024M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

ALTER tablespace YLGFOASPACE ADD datafile 'E:\APP\ADMINISTRATOR\ORADATA\YLGFOASPACE\YLGFOASPACE001.DBF' SIZE 10240M AUTOEXTEND ON;

4. 用户操作

4.1 查询用户列表

-- 查询用户信息：
select * from dba_users;
select username from dba_users;

4.2 创建用户并指定默认表空间

create user CS01 identified by "Abc123456" default tablespace CS01 temporary tablespace cs01_temp;

4.3 用户授权

--单独授权
GRANT CREATE VIEW,ALTER SESSION,CONNECT,RESOURCE,UNLIMITED TABLESPACE TO CSOA;

--授权DBA
GRANT dba TO CS01;

4.4 修改用户密码、默认表空间

--修改密码
select username from dba_users; --查看用户

alter user 用户名 identified by 新密码；

-- 修改临时表空间
ALTER USER CS01 temporary tablespace CS01_temp;

--修改默认表空间
ALTER USER 用户名 DEFAULT TABLESPACE  表空间名称；

select 'alter table'||a.table_name||'move tablespace workflow;',
           a.owner,a.table_name,a.tablespace_name,a.*
  from dba_tablesa
where a.Owner='用户名'
order by a.owner,a.table_name;

4.5 用户解锁

ALTER USER OADBA ACCOUNT UNLOCK;

5. 数据库归档模式调整

-- 关闭归档。
-- 查看是否是归档方式
archive log list;
-- 关闭数据库
shutdown immediate;
-- 打开控制文件，不打开数据文件
startup mount;
-- 数据库切换为非归档模式
alter database noarchivelog;
-- noarchivelog不归档，archivelog归档)
-- 打开关闭数据库
alter database open;
-- 查看是否是归档方式
archive log list;

6. 基础参数调整

-- 查询
show parameter processes;
show parameter sessions;
show parameter open_cursors;
show parameter AQ_TM_PROCESSES;
show parameter deferred_segment_creation;
show parameter cursors;

-- 参数修改
ALTER system SET aq_tm_processes = 1 scope = both;
ALTER system SET deferred_segment_creation = false scope = spfile;
alter system set processes=1000 scope=spfile;
alter system set open_cursors=1000 scope=both;

-- UNDOTBS01表空间大小修改
alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF' resize 30720M;
-- TEMP01表空间修改
ALTER DATABASE TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF' RESIZE 30720M;

-- 查看db_recovery_file_dest_size参数
show parameter db_recovery_file_dest_size;

-- 重新设置db_recovery_file_dest_size参数
alter system set db_recovery_file_dest_size=10G scope=both;

-- 重启
shutdown immediate
startup

7. 逻辑备份目录

-- 创建逻辑目录
CREATE DIRECTORY mydir AS '/data/oraclebak';

-- 查看逻辑目录是否创建成功
SELECT	* FROM	dba_directories WHERE DIRECTORY_NAME = 'MYDIR';

-- 逻辑目录授权
GRANT READ,WRITE ON DIRECTORY MYDIR TO CS01;

8. 数据备份及还原

-- 备份库
expdp cs01/Abc123456 schemas=oa dumpfile=cs-bak.dmp logfile=cs-bak.log DIRECTORY=DATA_PUMP_DIR

-- 还原库
impdp cs01/Abc123456 schemas=oa DIRECTORY=DATA_PUMP_DIR DUMPFILE=cs-bak.dmp logfile=cs-bak.log

备份默认路径：
/apps/oracle/admin/orcl/dpdump/
D:\app\Administrator\admin\orcl\dpdump\

-- 备份表
expdp cs01/Abc123456 DIRECTORY=DATA_PUMP_DIR dumpfile=daochu.dmp tables=COL_0801BAK,CTP_0801BAK,COMMENT_0801BAK

-- 还原表
impdp cs01/Abc123456 DIRECTORY=DATA_PUMP_DIR dumpfile=daochu.dmp

-- 还原
-- 用户不一致情况(将user用户导出的表空间添加进test用户下)(table_exists_action=replace直接覆盖)
impdp test/Abc123456 remap_schema=user:test remap_tablespace=user:test DIRECTORY=DATA_PUMP_DIR DUMPFILE=user-bak.dmp logfile=user-bak.log

9. 删除表空间及用户

-- 删除表空间
drop tablespace cs01 including contents and datafiles;

-- 删除用户
drop user cs01 cascade;

-- 修改表空间文件存储路径：
alter tablespace  datafile 'D:\cs01.DBF' offline drop;

10. 生成批量命令

-- 删除表
select 'drop table '||table_name||';' AS "删除表" from all_tables where owner='OAUSER' ORDER BY table_name;

-- 清空表
select 'truncate table '||table_name||';' AS "清空表" from all_tables where owner='OAUSER' ORDER BY table_name;