Oracle常用操作

一、启动及停止监听

启动监听

lsnrctl start

关闭监听

lsnrctl stop

监听状态

lsnrctl status

启动及停止Oracle

 # 启动的3个过程及命令
 startup
 # 关闭Oracle 数据库
 shutdown immediate

操作系统层面直接登入oracle

# 服务器登录oracle
[oracle ~]$   sqlplus "/as sysdba"
# 执行sql脚本
SQL> @a.sql
# 连接到数据库
SQL>  conn sys as sysdba
# 查看数据库当前实例
select instance_name from v$instance;

二、表空间相关操作

创建表空间

create tablespace xx datafile ‘/data/xxx.dbf’ size 10g autoextend off;
create temporary xx tempfile ‘/data/xxx.dbf’ size 10g auntoextend off;

扩容表空间

ALTER TABLESPACE xx ADD datafile ‘/data/xxx2.dbf’ size 10g;
ALTER temporary xx ADD tempfile ‘/data/xxx2.dbf’ size 10g;
ALTER TABLESPACE "BASE" ADD DATAFILE '/data/oradata/ORCLDEV/BASE02.dbf'  size 20480M AUTOEXTEND OFF;

删除表空间

drop tablespace xx including contents and datafiles；

修改临时表空间位置

-- 查看当前库表空间位置
select * from dba_tablespaces;
-- 根据实际情况修改临时表空间名称和位置
create temporary tablespace temp tempfile '/files/temp01.dbf' size 10m autoextend on;
alter tablespace temp add tempfile 'files/temp02.dbf' size 10M autoextend on;
alter database default temporary tablespace "TEMP"
startup force
drop tablespace tmp including contents and datafiles;

三、表空间使用率查询

set line 2000
select t.tablespace_name name,
       TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99,999,990.900') Size_M,
       TO_CHAR(NVL(d.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') || '/' || TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999999.999') Used_M,
       TO_CHAR(NVL((d.bytes - NVL(f.bytes, 0)) / d.bytes * 100, 0), '990.00') || '%' Used
  from dba_tablespaces t,
       (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) d,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) f
 where t.tablespace_name = d.tablespace_name(+)
   and t.tablespace_name = f.tablespace_name(+)
   and not (t.extent_management like 'LOCAL' and t.contents like 'TEMPORARY')
union all
select d.tablespace_name name,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') Size_M,
       TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' || TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') Used_M,
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') || '%' Used
  from dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) bytes
          from v$temp_extent_pool
         group by tablespace_name) t
 where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = t.tablespace_name(+)
   and d.extent_management like 'LOCAL'
   and d.contents like 'TEMPORARY'
 order by 4 desc;

四、创建用户

1、创建用户

create user xx identified by xx default tablespace xx temporary tablespace xx;

2、授权

grant connect ,resource to xx;
grant dba to xx;   给xx用户dba权限

修改密码

alter user xx identified by xx;

解锁用户

alter user xx account unlock;

删除用户并清空数据信息

drop user xxx cascade;

查看无法删除的用户，有链接session未断开导致

select username,sid,serial#,paddr from v$session where username ='xxx'

或者根据如下命令，直接生成删除session的sql语句

select 'alter system kill session '''||sid||','||serial#||''' immediate,' from gv$session where username = 'xx';

五、数据泵导入导出

首先登录oracle定义逻辑目录路径

create directory mydata as '逻辑目录路径';
例如：
create directory mydata as '/home/oracle/mydata';
# 路径读写权限授权给将导出导入的用户,例base用户
grant read,write on directory mydata to base;
# 如果存在，则先删除，在创建，删除命令：drop directory mydata;

导出脚本export.sh

#!/bin/bash
current_date=`date "+%Y%m%d"`
current_time=`date "+%H%M%S"`
mkdir -p $current_date
dataBases=("acctcenter" "acctdayend" "admin" "asset")

for i in ${dataBases[@]}; do
    expdp system/system@127.0.0.1:1521/orcl directory=mydata  logfile=full2013.log dumpfile=${i}-${current_date}-${current_time}.dmp schemas=${i}
done

tar -czf $current_date/orcl-${current_date}-${current_time}.tar.gz *-${current_date}-${current_time}.dmp
rm -f *-${current_date}-${current_time}.dmp

导入脚本import.sh

#!/bin/bash
dataBases=("acctcenter" "acctdayend" "admin" "asset")

for i in ${dataBases[@]}; do
    impdp system/system@127.0.0.1:1521/orcl directory=mydata dumpfile=${i}-20210513-101308.dmp  table_exists_action=replace
done

基本小命令:

# 修改oracle连接数，默认最大2000
alter system set processes=2000 scope=spfile;
# 查看表空间位置所在目录
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

查看数据库字符集编码

select * from NLS_DATABASE_PARAMETERS;
-- 或者
select userenv('language') from dual;

六、Oracle建议配置：

# 关闭密码过期设置，防止因密码修改，导致应用配置修改
alter profile DEFAULT limit password_life_time unlimited;
# 关闭审计功能，提供性能(需要重启生效)，生产环境建议关闭。
ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;

七、创建只读、修改账号

create USER QUERY identified by 密码;
grant resource,connect to QUERY;
grant unlimited tablespace to  QUERY;
grant select any table to QUERY;
grant alter any table to QUERY;
grant update any table to QUERY;
grant insert any table to QUERY;
revoke select any table from CAPITAL;   回收权限
acctcenter下的表授权给admin查看
grant select on ACCTCENTER.ACCT_ACCOUNT_INFO to ADMIN;

九、查询总的等待情况

select b.sql_hash_value, a.sid,b.osuser, a.event, a.wait_class,a.p1,a.p2,a.p3
  from v$session_wait a, v$session b
 where a.wait_class != 'Idle'
   and b.sid = a.sid
   order by a.sid;

十、查询锁表记录及删除锁记录

查询锁表记录

# 方式一
SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid; 
# 方式二
select sess.sid,
 sess.serial#,
lo.oracle_username,
 lo.os_user_name,
   ao.object_name,
 lo.locked_mode
  from v$locked_object lo,
  dba_objects ao,
 v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.SID;

删除锁定记录

ALTER system KILL session 'SID,serial#';

十一、归档日志处理

关闭归档日志

-- 查看是否是归档模式
archive log list;
-- 关闭归档
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
-- 查看是否是归档模式
archive log list;

清理归档日志

-- 删除归档日志，需oracle用户
rman target /
crosscheck archivelog all;
delete noprompt archivelog all;

十二、数据库关闭内存大页(虚拟机部署无需开通)

1、关闭/etc/sysctl.conf 里的内存大页配置
vm.nr_hugepages = 3151
2、查看数据库参数
sqlplus / as sysdba
create pfile from spfile;
quit;
cd $ORACLE_HOME/dbs
cat initorcl.ora
3、删除initorcl.ora文件内的大页配置项
*.user large_pages='ONLY'
4、在执行如下命令
sqlplus / as sysdba
create spfile from pfile
5、重启
reboot