Oracle常用操作

本人日常也有对Oracle数据库的维护，这里记录一下一些常用的oracle操作

Oracle用户管理

创建用户授予权限

create user abc identified by abc default tablespace material1;
grant connect,resource to abc;
grant dba to abc;

删除用户

drop material cascade;

获取oracle表空间状态信息

很多时候我们都需要及时了解oracle的表空间状态信息，避免出现表空间充满表空间文件进而导致数据库宕机的情况，查询语句如下

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

    表空间名    表空间大小(M)    已使用空间(M)    使用比 空闲空间(M) 最大块(M)
1   ABC     105179.97   104037.22     98.91%    1142.75 987
2   SYSAUX  7168    3110.69   43.40%    4057.31 2584
3   SYSTEM  7168    1375.87   19.19%    5792.13 2899
4   UNDOTBS1    13220   198.12     1.50%    13021.88    3817
5   UNDOTBS2    10240   198.44     1.94%    10041.56    3835
6   USERS   5   1.31      26.20%    3.69    3.31

这样我们就能及时了解到Oracle表空间的情况，及时处理表空间的问题

Oracle表空间管理

Oracle 10g

Windows

CREATE TABLESPACE MATERIAL1
DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

Linux

CREATE TABLESPACE MATERIAL1
DATAFILE '/u01/app/oracle/oradata/orcl/MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

Oracle RAC环境

CREATE TABLESPACE MATERIAL1
DATAFILE '+ORADATA/ORCL/MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

Oracle 11g

CREATE TABLESPACE MATERIAL1
DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf' SIZE 500M
UNIFORM SIZE 128k;
ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;

设定数据文件自动扩展

ALTER DATABASE DATAFILE 'D:\app\Administrator\oradata\orcl\MATERIAL1.dbf' 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M; 
ALTER DATABASE DATAFILE 'D:\app\jackycheng\oradata\orcl\MATERIAL1.dbf' 
AUTOEXTEND ON NEXT 100M 
MAXSIZE 10000M; 

Oracle密码超期

1、先以dba身份进入sqlplus

sqlplus / as sysdba;
2、将密码有效期由默认的180天修改成“无限制”，修改之后不需要重启动数据库，会立即生效
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

3、修改原有帐号的密码

alter user 用户名 identified by 原密码;

5、使用修改后的用户登录，如果报“ORA-28000:用户已被锁”，解锁用户

alter user db_user account unlock;

将指定用户下的所有表的select权限授予给另一个用户

通常我们在做一些数据共享的时候可能会把某个用户下的表授权给一个特定的仅有select权限的用户。如何授权其实大家都知道的通过grant语句操作就行了，这个操作针对少量表的时候还可以，但是当表数量成百上千张的时候一条一条的编写就显得不适用了，因此我们需要通过其他方式来方便的解决。
还好的是Oracle提供一些工具让我们可以偷懒，我们看下面这个语句

select ‘grant select on sourceUser.’ || tname || ’ to targetUser;’ from tab;

重点就是tab这个视图，通过这个视图就能获取到指定用户下的所有表名，前提是要使用具有system或者sysdba权限的用户登录执行这个sql语句。执行好后我们就能看到查询结果

grant select on ccense.ACCESS_APPOINTMENTINFO to ICDC_SICNU;
grant select on ccense.ACCESS_APPOINTMENTRIGHT to ICDC_SICNU;
grant select on ccense.ACCESS_DEALDATA to ICDC_SICNU;
grant select on ccense.ACCESS_MENUTYPE to ICDC_SICNU;
grant select on ccense.ACCESS_MULITCARDGRANTINFO to ICDC_SICNU;
grant select on ccense.ACCESS_MULITCARDRULE to ICDC_SICNU;
grant select on ccense.ACCESS_OPENTIMETOTERM to ICDC_SICNU;
grant select on ccense.ACCESS_OPENTIMETOUSER to ICDC_SICNU;
grant select on ccense.ACCESS_PASSWORDBIND to ICDC_SICNU;
grant select on ccense.ACCESS_PASSWORDGROUP to ICDC_SICNU;
grant select on ccense.ACCESS_PASSWORDINFO to ICDC_SICNU;
grant select on ccense.ACCESS_PERIODGROUP to ICDC_SICNU;
grant select on ccense.ACCESS_PERIODINFO to ICDC_SICNU;
grant select on ccense.ACCESS_PROHIBITIVEAPPOINT to ICDC_SICNU;
grant select on ccense.ACCESS_QRCODE to ICDC_SICNU;

然后我们将这些sql语句复制执行就完成了授权操作，这样是不是比较方便了呀。