Oracle 笔记总结

--创建用户
create user NF_NFZC identified by NF_NFZC;
--修改口令
alter user NF_NFZC identified by 123456;
--删除用户
drop user NF_NFZC;
--用户授权
grant connect, resource to 用户名;
grant connect, resource to NF_NFZC;
--撤销权限
语法： revoke connect, resource from 用户名;
列子： revoke connect, resource from NF_NFZC;
--创建角色
语法： create role 角色名;
例子： create role testRole;
--授权角色
语法： grant select on class to 角色名;
列子： grant select on class to testRole;
--删除角色
语法： drop role 角色名;
例子： drop role testRole;
--授权dba角色
grant dba to NF_NFZC 

==========================================查看字符集=============================================================
 select userenv('language') from dual;
==========================================表空间================================================================
set linesize 300;
set pagesize 300;
set timing on;
--Look TableSpace Use

--查看表空间方法一：
select ff.*, ff.maxsize_gb-ff.used_gb as sygb from (
select tablespace_name,
       round(used_space*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
       round(tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
       round(used_percent,2) as "PCT%" 
from dba_tablespace_usage_metrics
) ff where ff."PCT%" >60 order by ff."PCT%" desc;


--查看表空间方法二：
select tbs_used_info.tablespace_name,
       tbs_used_info.alloc_mb,
       tbs_used_info.used_mb,
       tbs_used_info.max_mb,
       tbs_used_info.free_of_max_mb,
       tbs_used_info.used_of_max as "used_of_max_pct%" 
  from (select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024 / 1024) alloc_mb,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024 / 1024) used_mb,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 /
                     a.maxbytes) used_of_max,
               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) /
                      1024 / 1024 / 1024) free_of_max_mb,
               round(a.maxbytes /  1024 / 1024 / 1024) max_mb
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name, sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info   where  tbs_used_info.used_of_max>10
 order by tbs_used_info.used_of_max desc;
-- Look TableSpace Path 查看表空间路径
 select * from dba_data_files f where f.TABLESPACE_NAME='TS_SSSL_DAT_2018';
-- Extend TablesSpace  扩展表空间
ALTER TABLESPACE TS_SSSL_DAT_2018 ADD DATAFILE '+DSDATA/snltnfzc/datafile/ts_sssl_dat_35.dbf' SIZE 30G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

--创建表空间
CREATE TABLESPACE SBZS_DATA  
LOGGING  
DATAFILE '/u01/app/oracle/oradata/XE/SBZS_DATA001.DBF' 
SIZE 50M  
AUTOEXTEND ON  
NEXT 50M MAXSIZE 20480M  
EXTENT MANAGEMENT LOCAL; 

CREATE TABLESPACE TS_HX_DJ_DATA  
LOGGING  
DATAFILE '/u01/app/oracle/oradata/XE/TS_HX_DJ_DATA001.DBF' 
SIZE 50M  
AUTOEXTEND ON  
NEXT 50M MAXSIZE 20480M  
EXTENT MANAGEMENT LOCAL; 


CREATE TABLESPACE NFZC_DATA  
LOGGING  
DATAFILE '/u01/app/oracle/oradata/XE/NFZC_DATA001.DBF' 
SIZE 50M  
AUTOEXTEND ON  
NEXT 50M MAXSIZE 20480M  
EXTENT MANAGEMENT LOCAL; 
---查看表空间
select username,default_tablespace from dba_users;
---如果想要修改用户的永久表空间可以执行命令:
alter user user default tablespace tablespaceName;
例如：alter user SBZS default tablespace SBZS_DATA --其中第二个user为要操作的用户，tablespaceName为将要设置的默认表空间名称。
---如果想修改新添加的用户的默认表空间可以执行如下命名：
alter database default tablespace tablespaceName，这样新建立的用户的默认表空间就为tablespaceName
--根据存储过程内容查找存储过程名称
select * from all_source f where f.type='PROCEDURE' and f.text like '%%';
--更改表空间为SBZS_DATA 
alter table SBZS.DJ_NSRXX move tablespace SBZS_DATA;
--针对某个用户设置默认表空间
alter user user_name default tablespace tbs_name;





           

oracle锁表以及解锁

--1、查看被锁住的表

SELECT S.SID,

       S.USER#,

       s.SQL_ID,

       S.USERNAME,

       OBJECT_NAME,

       MACHINE,

       S.SID,

       S.SERIAL#

  FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S

 WHERE L.OBJECT_ID = O.OBJECT_ID

   AND L.SESSION_ID = S.SID;

--表解锁 alter system kill session 'sid,serial#';

alter system kill session '3553,68';

--根据sql_id查询sql语句

select *

  from v$sqltext t

 where t.SQL_ID = '7zmmadz89u547'

 order by to_number(piece);