投稿
  1. 极客之音首页
  2. 数据库

oracle–常见操作

小半 • • 数据库 • 阅读 228

oracle常见操作

oracle是我们生产环境使用最频繁的数据库之一，因此将之前日常使用的相关操作记录下，备忘和方便使用。

1、连接

1）常规

oracle安装部署好后，cmd方式，输入sqlplus命令进行登录，格式sqlplus 用户名/密码@host:port/实例名

需要DBA角色登录要加 as sysdba

sqlplus system/123@127.0.0.1:1521/orcl

2）不使用用户名密码

有时候因为各种原因，需要不使用用户名密码直接连接oracle，一般情况下，按照如下命令登录即可

sqlplus /nolog
conn / as sysdba

有时候会提示无法登录，具体操作参考第四章1部分

2、表空间

临时表空间创建temporary tablespace

create temporary tablespace spacename_TEMP tempfile 'E:oradataspacename_TEMP.dbf' size 50m  autoextend on  next 50m maxsize 20480m  extent management local;

表空间创建tablespace

create tablespace spacename  logging  datafile 'E:oradataspacename.dbf' size 50m  autoextend on  next 50m maxsize 20480m  extent management local;
    --不指定文件路径
    --创建默认PDB表空间（会创建默认大小为100M，自增长为1M的表空间）
    create tablespace cookdba;
    Tablespace created.
    --创建自定义PDB表空间
    create tablespace lastdba datafile size 500m autoextend on next 100m;
    Tablespace created.
    Elapsed: 00:00:14.93

--批量修改用户表空间脚本
select 'alter table '||owner||'.'||object_name ||' move tablespace '|| owner ||';' from dba_objects where owner in ('CHARISMA') and object_type ='TABLE';
select 'alter index '||owner||'.'||object_name ||' rebuild tablespace '|| owner ||';' from dba_objects where owner in ('CHARISMA') and object_type ='INDEX';

表空间使用情况管理及扩展

--1、查看表在那个表空间
  select tablespace_name,table_name from user_talbes where table_name='spacetest';
--2、获取用户的默认表空间
  select username,DEFAULT_TABLESPACE from dba_users where username='spacetest';
--3、查看表空间所有的文件  
  select * from dba_data_files where tablespace_name='WORKINFO';
--4、查看表空间使用情况：
    select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'999,999.999') total_bytes,
to_char(b.bytes/1024/1024,'999,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'999,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%'use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'999,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'999,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'999,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name

 --5、临时表空间剩余情况          
 select * from dba_temp_free_space;
 select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;



--5、扩展表空间 
  alter database datafile 'D:ORACLEPRODUCTORADATATESTUSERS01.DBF' resize 50m;
  --自动增长  
  alter database datafile 'D:ORACLEPRODUCTORADATATESTUSERS01.DBF' autoextend on next 50m maxsize 500m;    
  --增加数据文件
   alter tablespace yourtablespacename add datafile 'd:newtablespacefile.dbf' size 5m;   
         
         --12c 扩展临时表空间
         alter tablespace gj_temp add TEMPFILE  size 1024m autoextend on next 200m;
--6、临时表空间报错
ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段
处理方案：
　　引起临时表空间增大主要使用在以下几种情况：
  1、order by or group by (disc sort占主要部分);
  2、索引的创建和重创建;
  3、distinct操作;
  4、union & intersect & minus sort-merge joins;
  5、Analyze 操作;
  6、有些异常也会引起TEMP的暴涨。
  7、用户执行imp/exp 导入导出操作时，会使用大量的temporary段
  8、用户在rebuild index时
  9、执行create table ...... as 语句时
  10、移动用户下的数据到别的表空间时

1） 通过扩展临时表空间文件：
  --12c 扩展临时表空间
         alter tablespace gj_temp add TEMPFILE  size 1024m autoextend on next 200m;
但是这样会导致临时表空间文件越来越大，没有解决根本问题；
2）尝试使用oracle表空间收缩，同样解决不了实质问题
查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
查询临时表空间状态:
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
方法1.重启实例 一般生产环境不适合
实例重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。
方法2.查看一下谁在用临时段
SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr;
杀掉对应会话
SQL> Alter system kill session 'sid,serial#';
把TEMP表空间回缩一下  -- 实测有问题
SQL> Alter tablespace TEMP coalesce;
收缩临时表空间
ALTER  TABLESPACE  GJ_TEMP SHRINK  SPACE;
收缩 并保留4000M，但是一般不会成功
alter tablespace GJ_TEMP shrink space keep 4000M;
3）新建临时表空间，修改用户临时表空间，删除原先临时表空间
--create temporary tablespace GJ_TEMPN tempfile size 1024m autoextend on next 100m;
--alter user workinfogj temporary tablespace GJ_TEMPN;
--drop tablespace GJ_TEMP including contents and datafiles;

3、创建用户 

create user spacetest identified by 123456  default tablespace spacename  temporary tablespace spacename_TEMP;

4、授权(根据实际情况) 

grant connect,resource,dba to spacetest; 
--表批量授权脚本
select 'grant select on '||table_name ||' to spacetest ;' 
from user_tables 
where tablespace_name='spacename' and table_name not like 'XX%';
--视图批量授权脚本
select 'grant select on '||view_name ||' to spacetest ;' from user_views;

5、备份及还原

备份

单次备份

exp spacetest/123456@127.0.0.1:1521/orcl file=E:backupspacetest_20190100.dmp tables=(TABLE1,TABLE2)

定时任务配套备份脚本

exp spacetest/123456@127.0.0.1:1521/orcl BUFFER=64000 file=E:backupspacetest_%date:~0,4%%date:~5,2%%date:~8,2%.dmp tables=(TABLE1,TABLE2) OWNER=spacetest log=E:backuplogspacetest_%date:~0,4%%date:~5,2%%date:~8,2%.log

还原

imp spacetest/123456@127.0.0.1:1521/orcl file=E:backupimpspacetest_20190100.dmp log=E:backupimplogspacetest_imp.log FROMUSER=spacetest TOUSER=spacetest buffer=5120000 indexes=n commit=n ignore=y grants=n FEEDBACK=10000

6、字符集查询 

select userenv('language') from dual;
---例如：结果为SIMPLIFIED CHINESE_CHINA.ZHS16GBK

7、日志查询

查询某张表的日志

select t.LAST_ACTIVE_TIME,t.* from v$sqlarea t
where t.sql_text like '%APPROVE_ITEM_WORKFLOW_FORM%'
order by t.LAST_ACTIVE_TIME  desc;

附字段说明

SQL_TEXT      ``//当前正在执行的游标的sql文本的前1000个字符
SQL_FULLTEXT    ``//CLOB类型 整个sql文本，不用借助于V$SQL_TEXT视图来查看整个文本
SQL_ID      ``//库缓存中的SQL父游标的标志
SHARABLE_MEM      ``//子游标使用的共享内存的大小,bytes
PERSISTENT_MEM      ``//子游标生存时间中使用的固定内存的总量,bytes
RUNTIME_MEM      ``//在子游标执行过程中需要的固定内存大小，bytes
SORTS      ``//子游标发生的排序数量
LOADED_VERSIONS     ``// 显示上下文堆是否载入，1是，0否
USERS_OPENING     ``// 执行这个sql的用户数
FETCHES     ``// sql取数据的次数
EXECUTIONS      ``//自从被载入共享池后，sql执行的次数 
FIRST_LOAD_TIME     ``// 父游标产生的时间戳
PARSE_CALLS      ``//解析调用的次数 
DISK_CALLS       ``//读磁盘的次数
DIRECT_WRITES      ``//直接写的次数
BUFFER_GETS      ``//直接从buffer中得到数据的次数
APPLICATION_WAIT_TIME     ``// 应用等待时间，毫秒
CONCURRENCY_WAIT_TIME      ``//并发等待时间，毫秒
USER_IO_WAIT_TIME      ``//用户IO等待时间
ROWS_PROCESSED SQL      ``//解析sql返回的总行数
OPTIMIZER_MODE      ``//优化器模式
OPTIMIZER_COST      ``//优化器对于sql给出的成本
PARSING_USER_ID      ``//第一个创建这个子游标的用户id
HASH_VALUES      ``//解析产生的哈希值
CHILD_NUMBER      ``//该子游标的数量
SERVICE      ``//服务名
CPU_TIME      ``//该子游标解析，执行和获取数据使用的CPU时间，毫秒
ELAPSED_TIME      ``//sql的执行时间，毫秒
INVALIDATIONS      ``//该子游标的无效次数
MODULE    ``//第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的模块名
ACTION   ``//第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的动作名 
IS_OBSOLETE      ``//标记该子游标过期与否，当子游标过大时会发生这种情况
is_bind_sensitive      ``//不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为Y，否则会被设置为N。
is_bind_aware      ``//表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为Y，如果不是，这个字段会被设置为N。如果是设置为N，这个游标将被废弃，不再可用。
is_shareable      ``//表明游标能否被共享。如果可以，这个字段会被设置为Y，否则，会被设置为N。如果被设置为N，这个游标将被废弃，不再可用。

v$sqlarea的字段定义和v$sql基本一致，不同的是VSQLAREA是在父游标级别上统计的sql信息，vsql的汇总表，进行了group by hash_value,sql_id的汇总。


原文始发于微信公众号（云户）：oracle–常见操作

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/25916.html

赞 (1)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！