oracle

管理员操作数据库

> sqlplus / as sysdba  --本地system登陆数据
> shutdown immediate;  --立即关闭数据库
> startup;             --启动数据库
> startup force;       --强制重启数据库
> exit;                --退出sqlplus

当使用linux时请使用

• su - oracle 切换oracle用户
  

• lsnrctl stop 停止监听
  

• lsnrctl start 启动监听

用户

> select  *　from dba_users;                                --查找用户
> create user username identified by password;              --创建用户 
> grant connect to username;                                --授予连接权限
> grant resource to username;                               --授予开发权限
> grant dba to username;                                    --授予最高权限
> grant resource,connect,dba to username;                   --授予所有权限
> drop user name cascade;                                   --删除用户
> select username,account_status,lock_date from dba_users;  --查看被锁用户
> alter user username account unlock;                       --解锁用户
> alter user username account lock;                         --锁定用户

创建用户时如需默认表空间,示例:

> create user username identified by password default tablespace tablespacename;

深入了解

GRANT授权
GRANT 赋予一个用户，一个组或所有用户访问权限
GRANT privilege [, ...] ON object [, ...]    TO { PUBLIC | GROUP group | username }

privilege的权限有:  
		  SELECT 访问声明的表/视图的所有列/字段．
		  INSERT 向声明的表中插入所有列字段．
		  UPDATE 更新声明的所有列/字段．
		  DELETE 从声明的表中删除所有行．
		  RULE   在表/视图上定义规则 （参见 CREATE RULE 语句）．
		  ALL    赋予所有权限．
object赋予权限的对象名.可能的对象是:  
                                      table     (表)
                                      view      (视图)
                                      sequence  (序列)
                                      index     (索引)
PUBLIC      代表是所有用户的简写.
GROUP group 将要赋予权限的组 
username    将要赋予权限的用户名．

示例: GRANT SELECT,DELETE,UPDATE,INSERT ON TABLENAME TO USERNAME;  --授权用户对表的增删改查
      GRANT ALL PRIVILEGES ON TABLENAME to USERNAME;               --授权用户对表的所有权限

如果成功,输出 CHANGE
如果所声明的对象不可用或不可能对声明的组或用户赋予权限．
输出 ERROR: ChangeAcl: class "object" not found
描述：GRANT允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限．对象创建后,
	  除了创建者外，除非创建者赋予（GRANT）权限，其他人没有访问对象的权限．
	  一旦用户有某对象的权限，他就可以使用那个特权．不需要给创建者赋予（GRANT）对象的权限，
	  创建者自动拥有对象的所有权限，包括删除它的权限.


REVOKE回收权限
REVOKE INSERT ON TABLENAME FROM USERNAME         回收USERNAME在TABLENAME上的插入权限
REVOKE ALL PRIVILEGES ON TABLENAME FROM USERNAME 回收用户USERNAME对TABLENAME的所有权限

表空间

> select *from DBA_TABLESPACES where TABLESPACE_NAME ='TABLESPACENAME'; 查看表空间
> drop tablespace TABLESPACENAME including contents and datafiles cascade constraint; 删除表空间
> CREATE TABLESPACE TABLESPACENAME DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\oracle\TABLESPACENAME.DBF'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; windows创建表空间
> CREATE TABLESPACE TABLESPACENAME DATAFILE '/u01/oracle/oradata/wlzy/ECMSSTATS.DBF'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;  linux创建表空间 表空间大小自动增长

数据库所有表空间使用率
> SELECT to_date(sysdate),
      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;

表空间大小
> select
 b.file_name 物理文件名,
 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
 group by b.tablespace_name,b.file_name,b.bytes
 order by b.tablespace_name

备份和还原

查找数据文件

select * from dba_data_files; 
select * from dba_tablespaces;
select * from dba_directories 查看备份目录

导出

> expdp username/password  directory=manualbackup dumpfile=dumpname.dmp schemas=username;
> expdp username/password tables=tablename dumpfile=dumpname.dmp directory=manualbackup; 导出数据库中一个表的数据
> expdp username/password directory=manualbackup dumpfile=dumpname.dmp tables=tablename query=’where ID=123’; 导出数据库中一个表中的一行数据

导入

> impdp ecmsintf/ecms  directory= manualbackup dumpfile=ecms_dp_20190401.dmp schemas=ecms TABLE_EXISTS_ACTION=replace 导入数据库
> impdp ecms/ecms directory=manualbackup dumpfile=ELECTRICITY_BILL_FILE.dmp tables=ELECTRICITY_BILL_FILE remap_schema=ecms:ecms; 导入数据库中一个表的数据

oracle备份分为exp和expdp

杂谈

创建表来自另外一个表
Create table a as select field from b

在表中增加列
alter table a add 字段名 类型

删除表中的列
alter table a drop cloumnc 列名

更新表内容
update a set 列名  = null

创建索引
create index 索引名 on 表(字段) tablespace 表空间

去重函数 distinct
相加函数 sum
case when 符合条件则为1 否则为0

查看符合条件的有多少条
select sum(case when 条件 then 1 else 0 end) from a


新增表列对应的值
insert into 表名 (列名) values (值)


select * from (select a.*,rownum rn from (select * from table_name)a where rownum <= 40) where rn >=21



select jcwdm from (select row_number() over(partition by jcwdm order by jcwdm) rn,a.* from tablename a
) where rn =1

 DBLink

create database link cnjzzsp
    connect  to admin identified password
     using '(DESCRIPTION=
         (ADDRESS_LIST=
               (ADDRESS=(PROTOCOL = TCP)(HOST =10.16.163.41)(PORT=1521))
)
(CONNECT_DATA =
        (SERVICE =Oracle)
   ) 
)'; 

 查看锁表进程SQL语句： 
      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; 

如有记录则表示有lock，记录下SID和serial# ，将记录的ID替换下面的xx,xxx，即可解除LOCK 
      alter system kill session 'xx,xxx'

空闲率
select sum(m.all_g),sum(n.free_g),
sum(n.free_g)/sum(m.all_g) from
(select tablespace_name,sum(bytes)/1024/1024 as all_g from dba_data_files group by tablespace_name) m,
(select tablespace_name,sum(bytes)/1024/1024 as free_g from dba_free_space group by tablespace_name) n
where m.tablespace_name = n.tablespace_name

使用率
select m.tablespace_name,m.all_g,
n.free_g ,n.free_g/m.all_g from
(select tablespace_name,sum(bytes)/1024/1024 as all_g from dba_data_files group by tablespace_name) m,
(select tablespace_name,sum(bytes)/1024/1024 as free_g from dba_free_space group by tablespace_name) n
where m.tablespace_name = n.tablespace_name

数据库命令

> oracle SGA使用情况
> select * from v$sga;
固定SGA(MB)  Fixed Size 
数据缓冲区(MB) Database Buffers
日志缓冲区(MB) Redo Buffers
> select * from v$logfile;  日志表
> select count(*) from v$session; --数据库当前配置会话数
> select * from dba_jobs  数据库所有的job
> select * from dba_jobs_running 正在运行的job