任务型sql

大家好，又见面了，我是全栈君。

一、创建表空间与用户，因为数据文件没有指定路径，所以需要修改数据文件路径，才有了下面的需求。

create tablespace wo datafile ‘wo.dbf’ size 20m;
create user wo identified by “123abc” default tablespace wo;
grant connect,resource to wo;

针对非系统表空间，对sysaux,users表空间同样适用
select * from v$datafile;
select tablespace_name,file_name,online_status from dba_data_files where tablespace_name=’WO’;
alter tablespace WO offline;
host mv /u01/app/oracle/product/11.2/db_1/dbs/wo.dbf /u01/app/oracle/oradata/LCP1/datafile/
alter tablespace WO rename datafile ‘/u01/app/oracle/product/11.2/db_1/dbs/wo.dbf’ to ‘/u01/app/oracle/oradata/LCP1/datafile/wo.dbf’;
select tablespace_name,file_name,online_status from dba_data_files where tablespace_name=’WO’;
alter tablespace WO online;

针对系统表空间，对UNDOTBS1、TEMP表空间也适用。该方法需要数据库处于mount状态
shutdown immediate
startup mount
host mv /u01/app/oracle/product/11.2/db_1/dbs/system01.dbf /u01/app/oracle/oradata/LCP1/datafile/
alter tablespace WO rename datafile ‘/u01/app/oracle/product/11.2/db_1/dbs/system01.dbf’ to ‘/u01/app/oracle/oradata/LCP1/datafile/system01.dbf’;
alter database open;
select tablespace_name,file_name,online_status from dba_data_files where tablespace_name=’SYSTEM’;

二、查看用户权限

select privilege from dba_sys_privs where grantee=’PLATFORM_USER’
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee=’PLATFORM_USER’);

三、

获取表：

select table_name from user_tables; //当前用户拥有的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner=’用户名’

user_tables：
table_name,tablespace_name,last_analyzed等
dba_tables：
ower,table_name,tablespace_name,last_analyzed等
all_tables：
ower,table_name,tablespace_name,last_analyzed等
all_objects：
ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段：
select * from user_tab_columns where Table_Name=’用户表’;
select * from all_tab_columns where Table_Name=’用户表’;
select * from dba_tab_columns where Table_Name=’用户表’;
user_tab_columns：
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
all_tab_columns ：
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
dba_tab_columns：
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

获取表注释：
select * from user_tab_comments
user_tab_comments：table_name,table_type,comments
相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
获取字段注释：
select * from user_col_comments
user_col_comments：table_name,column_name,comments
相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。

select * from user_col_comments where table_name=’TIS_FT_USER’;
select * from user_tab_comments where TABLE_NAME = UPPER(’tis_sys_message’);

四、oracle11g AUD$维护。五步走：

1.检查SYSTEM表空间使用情况

select　　
b.tablespace_name “表空间”,
b.bytes/1024/1024 “大小M”,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 “已使用M”,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) “利用率”
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
and b.tablespace_name=’SYSTEM’
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

2.从dba_segments中找出占用SYSTEM表空间中排名前10位的大对象。

select *
from (select segment_name,sum(bytes)/1024/1024 MB
      from dba_segments
      where tablespace_name=’SYSTEM’
      group by segment_name
      order by 2 desc)
where rownum<10;
查出aud$占用的很大的空间。

3.准备truncate aud$表
SQL> show parameter AUDIT_TRAIL
SQL> truncate table aud$;

4.再次检查system表空间使用情况

5.为了避免system表空间爆满对数据的影响，把aud$从system表空间迁移到普通表空间AUD_FILE。
    SQL> BEGIN
      2   DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
      3   AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      4   AUDIT_TRAIL_LOCATION_VALUE => ‘AUD_FILE’);
      5   END;
      6 /

    SQL> col owner for a5;
    SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
      2    FROM DBA_TABLES
      3   WHERE TABLE_NAME = ‘AUD$’
      4     AND OWNER = ‘SYS’;

    OWNER TABLE_NAME    TABLESPACE_NAME
    —– —————————— ——————————
    SYS   AUD$                           TD_FILB

五、系统表空间潢的问题。

如果system表空间不是自动扩展，空间用满甚至会出现数据库无法登陆。使用任何用户登录均报出异常。

系统表空间正常情况下只存放了数据字典之类的东西，所以占用的空间一般在500M以下。如果你的系统表空间占用比较多的空间，可能有以下几方面的原因：
1）没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间
2）开启了审计,请检查此表的大小AUD$
你可以运行以下查询来检查一下系统表空间哪些表比较大：
   select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
                where tablespace_name=’SYSTEM’ group by segment_name)
                where sx>100 order by sx desc;

查看该表纪录数:
select count(*) sum from AUD$;

解决方法大概可分为以下几种:
一、为system表空间另外新增一个数据文件。
    alter tablespace system add datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ resize 1024M;
二、更改system表空间的数据文件SYSTEM.dbf分配空间。
    alter database datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ autoextend on;
    alter database datafile ‘D:\oracleXE\oradata\XE\system_01.dbf’ resize 1024M;
三、truncate掉AUD$表并关闭审计功能(我是使用的这种,效果立竿见影,不过如果不关闭此功能, 需要定期清理此表)：
             SQL> alter system set audit_trail=none scope=spfile;
             SQL> showdown immediate;
             SQL> startup;
四、将AUD$默认表空间由system移出。

六、oracle审计相关操作

http://blog.itpub.net/29119536/viewspace-1340277/

http://blog.sina.com.cn/s/blog_7071ca800102v55v.html

http://blog.csdn.net/hijk139/article/details/7387811

七、删除归档日志

http://blog.chinaunix.net/uid-17240229-id-306718.html

把归档日志的物理文件删除后，我们就可以正常登入ORACLE了，但是还没完全把归档日志删除干净，ORACLE的controlfile中仍然记录着这些archivelog的信息，在oracle的OEM管理器中有可视化的日志展现出，当我们手工清除archive目录下的文件后，这些记录并没有被我们从controlfile中清除掉，接下去我们要做的就是这个工作。
我们利用RMAN进行删除操作，

两种方式：

1、先查看使用率，再删除物理文件，再用rman删除。
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
[oracle@stdg archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/STDGGT/archivelog
[oracle@stdg archivelog]$ rm -rf 2016_11_22
[oracle@stdg archivelog]$ rman target /
RMAN> list archivelog all;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-6’;
上面直接不能删除，需要先用下面的命令，然后才能删除。
RMAN>　crosscheck archivelog all;
RMAN> delete expired archivelog all;
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
用上面的sql查看文件数量与容量大小相应的减少了。

2、先查看使用率，直接用rman删除。
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
[oracle@stdg archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/STDGGT/archivelog
[oracle@stdg archivelog]$ rman target /
RMAN> list archivelog all;
RMAN> delete archivelog until time ‘sysdate-5’;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-6’;
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

SYSDATE-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库
DELETE ARCHIVELOG from TIME ‘SYSDATE-7’; 删除从7天前到现在的全部日志,慎用
UNIX/LINUX下也可以通过FIND找到7天前的归档数据，使用EXEC子操作删除
find /oraarchive -xdev -mtime +7 -name “*.dbf” -exec rm -f {} ;
这样做仍然会在RMAN里留下未管理的归档文件
仍需要在RMAN里执行下面2条命令
crosscheck archivelog all;
delete expired archivelog all;
所以还不如上面的方法好用，不过用FIND的好处就是，可以在条件上，和EXEC子项上做很多操作，实现更复杂的功能

八、利用dba_segments视图查询oracle数据库对象的空间占用情况

假设需要查询oracle数据库中某个表的空间占用情况，那么，可以利用dba_segments这个视图。比如，如下图1所示，查询的就是cmx_arif_history这张表的空间占用大小，之所以用sum函数进行累加，是因为该表占用了好几个分区，所以查询出来有很多记录，需要通过sum函数进行求和累加。

http://blog.csdn.net/cuker919/article/details/8514253

开启闪回

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
alter database flashback on;


关闭闪回及归档
shutdown immediate;
startup mount
alter database flashback off
alter database noarchivelog;
alter database open;
archive log list;

误操作后进行闪回操作

下午收到同事的报告，一张表被错误更新，没有加where导致表的某个字段被全部更新。
数据已经提交。还好通知的及时，只要数据还在回滚段，就可以查的到，想到了闪回查询,下面是具体的操作步骤

update t1 set id=1;
select systimestamp from dual;
drop table t2 purge;
exit
sqlplus test
数据已经丢失
select * from t1;
此时进行闪回
create table t2 as
select * from  t1  as of timestamp to_timestamp('2017-04-26 22:40:00','yyyy-mm-dd hh24:mi:ss');
验证数据对不对，如果对的话就重命名表的操作
rename t1 to t3;
rename t2 to t1;
数据成功恢复。
如果不对，就
drop table t2 purge;
继续闪回
create table t2 as
select * from  t1  as of timestamp to_timestamp('2017-04-26 22:40:00','yyyy-mm-dd hh24:mi:ss');
直到对为止


1.查询最近更新数据之前的数据（以便确定是不是Commit之前的数据）
select * from account as of timestamp to_timestamp('2017-04-26 22:00:00', 'yyyy-mm-dd hh24:mi:ss');
2.闪回操作前启用行移动功能（不启用不可以闪回）
alter table account enable row movement;
3.执行闪回语句
flashback table account to timestamp TO_TIMESTAMP('20140422 15:10:00','YYYYMMDD HH24:MI:SS');