ORACLE解决方式

以下是在工作中遇到的问题总结下来的解决方式，后面会持续更新

1.表空间不足的解决方式

查看所有表空间的使用情况

SELECT A.TABLESPACE_NAME "表空间名称",
       total/(1024*1024) "表空间大小(M)",
       free/(1024*1024) "表空间剩余大小(M)",
       (total-free)/(1024*1024) "表空间使用大小(M)",
       total/(1024*1024*1024) "表空间大小(G)",
       free/(1024*1024*1024) "表空间剩余大小(G)",
       (total-free) /(1024*1024*1024) "表空间剩余大小(G)",
       ROUND((total-free)/total,4)*100 "使用率%",
from (select tablespace_name,sum(bytes) free from dba_free_space
        group by tablespace_name)a,
      select tablespace_name,sum(bytes) total from dba_data_files
        group by tablespace_name)b
      where a.tablespace_name = b.tablespace_name

 查看临时表空间使用情况

select tablespace_name,
        file_name,
        user_bytes/bytes,
        bytes/1024/1024 "file_size(M)",
        autoextensible
from dba_temp_files
where tablespace_name ='TEMP'

查看报错表所在的表空间

select TABLESPACE_NAME,table_name FROM ALL_TABLES WHERE TABLE_NAME =''

查看表空间是否自增，以及表空间存放路径

select file_name,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files

 启用表空间存储自增

alter database datafile '/ordata/DB/datafile/USER04.dbf' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXIZE UNLIMITED

2.清空回收站

drop 表之后没有真正的删除，表结构还是会存在于回收站中

-- 查询当前用户的回收站对象
select * from user_recylebin

-- 使用dba账户进行登录，否则没权限，查询数据库回收站所有对象
select * from dba_recylebin

purge dba_recyclebin;    --dba用户执行清理dba_recyclebin
-- 可以选择在删除表的时候同时做清理
drop table 表名 purge

3.创建分区

按月进行分区

create table test(
  id number(20) not null,
  data_date date)
partition by range(data_date)
interval(numtoyminterval(1,'month'))
(
  partition p0 value less than(to_date('2016-05-01','yyyy-mm-dd'))
  )
  

4.将已有的分区表空间迁移

alter table 表名 move table_space 表空间名
-- 移动表分区所属表空间之后需要重建索引
alter index 索引名 rebuild partition 分区名

5.操作系统表空间不够，临时需要收缩数据文件释放空间

查看数据文件可收缩情况

select  a.tablespace_name, -- 表空间名
          file_name, -- 数据文件名
          c.value / 1024 "blk.size(kb)", -- block_size
          ceil((nvl(hwm,1) * c.value)/1024 / 1024) "smallest(Mb), -- hwm" --数据文件可缩小的最小大小
          ceil(blocks*c.value /1024/1024) "currsize(Mb)", -- 数据文件当前大小
          ceil(blocks*c.value /1024/1024) -  ceil((nvl(hwm,1) * c.value)/1024 / 1024) "savings(Mb)", --文件可收缩的空间（currsize-smallest）
from dba_data_files a,(select file_id,max(block_id+block -1) hwm from dba_extents group by file_id) b,
(select value from v$parameter where name ='dba_block_size') c
where a.file_id = b.file_id(+)
      and a.status !='INVALID'
      ORDER BY 6

 查看表空间相关的表文件存在的位置

select file_name,tablespace_name,autoextensible from dba_data_files

在命令行界面执行以下语句，释放表空间

alter database datafile 需要操作的表空间文件名 resize 查询出来的Smallest
alter database datafile "/ordata/db/datafile/user02.dbf" resize 22988M

6.触发器在修改本表的同时进行查询本表的数据，出现事务冲突的解决方式

情形：A表的触发器中的insert 语句 或 update 语句中用到了from A表

解决方式：需要在declare的后面加上以下语句，向系统说明这是自定义触发器，需要在insert和update语句完成后加上commit；

PRAGMA AUTONOMOUS_TRANSACTION;
触发器中存在新增和修改需要手动加上commit

7.查看正在执行的事务

select * from v$process a,v$session b,v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value

8.杀掉正在执行的进程

alter system kill session spid,serial#
alter system kill session '390,54070'

9.创建表空间与用户

注意：数据文件最大只能是31G，如果超过31G则需要多添加几个数据文件

-- 超过数据文件最大值，则使用该语句添加数据文件扩展空间

alter tablespace users add datafile 输入数据文件名 SIZE 500M AUTOEXTEND ON  NEXT 500M MAXSIZE 31G
alter tablespace users add datafile '/oradata/db/datafile/user04.dbf' SIZE 500M AUTOEXTEND ON  NEXT 500M MAXSIZE 31G

创建表空间

create tablespace <TableSpaceName>
logging
Datafile '' --文件名，找到该数据库存放数据文件的目录，给个数据文件的名字
Size 500m
Autoextend on
NEXT 500M MAXSIZE 31G
EXTENT MANAGEMENT LOCAL;
例:
create tablespace ws_ts
logging
Datafile '/oradata/datafile/ws_ts.dbf' 
Size 500m
Autoextend on
NEXT 500M MAXSIZE 31G
EXTENT MANAGEMENT LOCAL;

创建临时表空间

create temporary tablespace <TableSpaceName_temp>
tempfile '' --文件名，找到该数据库存放数据文件的目录，给个数据文件的名字
Size 500m
Autoextend on
NEXT 500M MAXSIZE 31G
EXTENT MANAGEMENT LOCAL;
例：
create temporary tablespace ws_ts_temp
tempfile '/oradata/datafile/ws_ts_temp.dbf' 
Size 500m
Autoextend on
NEXT 500M MAXSIZE 31G
EXTENT MANAGEMENT LOCAL;

创建用户并关联相关表空间以及profile

create user <username> identified by <password> default tablespace<TableSpaceName> temporary tablespace <TemporaryTableSpaceName>  profile appuser;
例：
create user ws identified by ws1234 default tablespace ws_ts temporary tablespace ws_ts_temp  profile appuser;

生成用户权限

GRANT RESOURCE,CONNECT,DBA TO 用户名
alter user 用户名 quta unlimited on 用户所属表空间名
GRANT CREATE SYNONYM TO 用户名 --(此行需根据应用需要酌情添加)
例：
GRANT RESOURCE,CONNECT,DBA TO ws
alter user ws quta unlimited on ws_ts
GRANT CREATE SYNONYM TO ws

10.修改字段类型，遇到虚拟字段异常

a）查询虚拟列的情况

select t.column_name,t.data_default,t.hidden_column
from user_tab_cols t
where t.table_name ='' --出现虚拟字段的表名
在查询结果中选择虚拟列的DATA_DEFAULT，点击查看，找到包含需要修改的字段名

b)删除虚拟列

begin 
  dbms_stats.drop_exetended_stats('','','');
  end;
  依次输入三个参数：用户名，表名，data_default里面的哈希值

c)修改字段类型

alter table 表名 modify 字段名 字段类型

d)如需添加虚拟列，则使用以下sql

select dbms_stats.create_extended_stats('','','') from dual
 依次输入三个参数：用户名，表名，字段名

11.查找使用某段sql的数据对象

select * from dbs_source d
where regexp_like(lower(d.text),lower('inset.*ws_info'));-输入需要查询的sql

12.查询已经删除的数据

A -- 天

B -- 小时

C --分钟

select * from 表名 as timestemp sysdate -A/B

13.恢复已经删除的数据

insert into table_name
select * from from table_name as of timestamp to_timestamp('删除时间点','yyyy-mm-dd')

14.授权dba用户下的包给指定用户

-- 授权dba用户下的汉字转拼音package给到指定用户
GRANT excute on sys.gethzpy to wss

15.批量删除数据存储过程

注释的部分是按照区间进行循环删除

数据删除慢的原因及处理方式
1.存储空间不够（临时表空间不够，需要定时清理归档日志和联机日志）
2.索引太多（每删除一批数据，索引都会重新计算）
3.每次删除的条数过多，会导致删除数据的时间依次累加，因此可以写个循环分批次进行删除（可以按照不同条数删除进行测试，看一次删除多少条用时最少，以下是我测试之后发现每次删除500条用时最少）
create or replace procedure proc_loop_delete(start_date in varchar2 -- 开始日期
                                             -- end_date in varchar2 -- 结束日期
                                            )
is 
 i_large number;
 v_date varchar(10);
begin
    v_date :=start_date;
    -- while to_date(v_date,'yyyy-mm-dd') <=to_date(end_date,'yyyy-mm-dd') loop
    i_lage :=500;
    while i_large =500 loop
      delete from test where date_time = to_date(v_date,'yyyy-mm-dd') and rownum <=500;
      i_large :=sql%rowcount;
      commit;
end loop;
  -- v_date :=to_char(to_date(V_DATE,'yyyy-mm-dd') +1,'yyyy-mm-dd');
  -- end loop;
end proc_loop_delete;

16.查看分区表

本用户下
select * from user_part_tables
数据库所有用户的分区表
select * from dba_part_tables
当前用户可访问的
select * from all_part_tables where def_tablespace_name ='' --表空间名

17.查看数据库密码是否过期

select username,account_tatus,expiry_date from dba_users where user_name ='JES'
以下是查询出来的结果，如果显示EXPIRED则表示密码过期
-- OPEN 正常
-- EXPIRED 密码过期