正焕
文章
15
分类
2
评论
1

Oracle 学习笔记

| 学习笔记 •
字数总计 8996 | 阅读时长 22分钟 | 阅读量 81

Oracle入门

中文字符集环境变量

NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

当前连接oracle客户端IP

select sys_context('userenv', 'ip_address') from dual

查看当前容器

select sys_context('USERENV', 'CON_NAME') from dual;

查看版本

select * from v$version;

clob模糊查询

dbms_lob.instr(raw_data, '陈独秀', 1, 1) > 0;
SELECT * FROM table_name WHERE dbms_lob.instr(clob_column, 'search_string') > 0;

创建表空间 ORACLE_DATA

create tablespace ORACLE_DATA datafile '/home/oracle/app/oracle/oradata/mydata/ORACLE_DATA_1.DBF' size 128M autoextend on next 32m;
# 扩展表空间 HISDATA
alter tablespace ORACLE_DATA add datafile '/home/oracle/app/oracle/oradata/mydata/ORACLE_DATA_2.DBF' size 128M autoextend on next 32m;

创建用户 gyzl , 密码 gyzl123

create user gyzl
identified by gyzl123
default tablespace ORACLE_DATA
temporary tablespace TEMP;
-- Grant/Revoke role privileges 
grant resource to gyzl;
grant dba to gyzl;
grant connect to gyzl;
-- Grant/Revoke system privileges 
grant unlimited tablespace to gyzl;

连接串

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.202.67)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=his;Password=his

用户密码不过期

--查看用户的proifle是哪个，一般是DEFAULT
SELECT username,PROFILE FROM dba_users;
--查看对应的概要文件(如DEFAULT)的密码有效期设置
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
--将概要文件(如DEFAULT)的密码有效期由默认的180天修改成"无限制"
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

sqlplus 连接远程数据库

sqlplus user/password@//ip:port/orcl as sysdba

Oracle高级

查询所有表实际行数

declare
  l_count  number := 0;
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null); --输出不会出错
  for x in (select ut.table_name from dba_tables ut where ut.OWNER = 'HIS'  order by ut.TABLE_NAME) loop
    begin
        execute immediate 'select count(0) from ' || x.table_name into l_count;
        dbms_output.put_line(x.table_name||'----'||l_count);
    exception
      when others then
        dbms_output.put_line('ERROR_'||x.table_name);
    end;
  end loop;
end;

导出指定表指定行数

exp test/test@127.0.0.1/orcl file=t.dmp tables=(table1,table2) query="'where rownum<=10000'"

所有空表初始化表空间通过exp才能导出

declare
begin
  for x in (select 'alter table ' ||table_name ||' allocate extent' sss from user_tables where segment_created='NO' ) loop
    begin
      execute immediate x.sss;
    end;
  end loop;
end;

查询每个表实际占用空间

select ut.table_name,ut.OWNER,ut.tablespace_name,num_rows,us.BYTES from dba_tables ut,(select segment_name,sum(bytes)/1024/1024 bytes 
from user_segments group by segment_name) us 
where ut.TABLE_NAME = us.segment_name
order by num_rows desc nulls last ;

修改oracle字符集

sqlplus / as sysdba
select userenv('language') from dual;
shutdown immediate;
startup mount;
alter system enable restricted session;
alter system set JOB_QUEUE_PROCESSES=0;
alter system set AQ_TM_PROCESSES=0;
alter database open;
alter database character set INTERNAL_USE ZHS16GBK;
shutdown immediate;
startup;

查询用户下每张表备注以及字段备注

select ATC.OWNER,
       atC.TABLE_NAME,
       utc.comments,
       ATC.COLUMN_NAME,
       ATC.DATA_TYPE,
       ATC.DATA_LENGTH,
       ATC.NULLABLE,
       ucc.comments
  from (select ATC.OWNER,
               atC.TABLE_NAME,
               ATC.COLUMN_NAME,
               ATC.DATA_TYPE,
               ATC.DATA_LENGTH,
               ATC.NULLABLE
          from all_tab_columns ATC
         where ATC.owner in ('HIS')) atc
  left outer join user_col_comments ucc
    on atc.table_name = ucc.table_name
   and atc.column_name = ucc.column_name
  left outer join user_tab_comments utc
    on atc.table_name = utc.table_name
 order by atc.table_name, atc.column_name;

数据闪回

select * from table1 as of timestamp to_timestamp('2010-01-09 10:00:45','YYYY-MM-DD HH24:MI:SS')

设置密码输错无限次数

select * from dba_profiles;
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

临时表查询

with tableTemp as(select * from dual)
select from table1 a,tableTemp b where a.id=b.id

多行拼接成一行

select to_char(wm_concat(table1.name)) from table1

锁表解锁

Lock_mode
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

--普通锁
SELECT 'alter system kill session '''||s.sid||','||s.serial#||''';' kill_session, 
       s.sid,
       s.serial#,
       o.object_name,
       s.machine,
       l.LOCKED_MODE,
       l.os_user_name,
       s.PROGRAM,
       s.ACTION,
       s.CLIENT_INFO,
       s.LOGON_TIME
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id = o.object_id(+)
   AND l.session_id = s.sid(+)
 ORDER BY to_number(s.SID);

--RAC服务锁
select decode(l.request, 0, 'Holder:   ', '  Waiter: ') || l.inst_id || ':' ||
       l.sid sess,
       l.id1,
       l.id2,
       l.lmode,
       l.request,
       l.type,
       l.ctime,
       a.event,
       s.sql_text,
       a.sql_id,
       a.program,
       a.CLIENT_INFO,
       'alter system kill session ''' || a.sid || ',' || a.serial# || ',@' ||
       a.inst_id || ''' immediate ;' kill_session
  from gv$lock l, gv$session a, v$sqlarea s, gv$process p
 where (l.id1, l.id2, l.type) in
       (select id1, id2, type from gv$lock where request > 0)
   and l.sid = a.sid --and l.SID in ('1782')
   and l.inst_id = a.inst_id
   and a.inst_id = p.inst_id
   and a.sql_id = s.sql_id(+)
   and a.paddr = p.addr(+)
 order by l.id1, l.request, l.ctime;

配置监听

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
	(SID_DESC =
       (GLOBAL_DBNAME = orcl)
       (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)   
       (SID_NAME = orcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-8CD225QHBAK)(PORT = 1521))
    )
	(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.202.38)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\Administrator

触发器

登录的时候存ip

/*登录的时候存ip==>gv$session.client_info*/
create or replace trigger trg_login_on after logon on database
begin
  dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;

存储过程

重置 seq 为 MinValue

create or replace procedure prc_reset_seq(v_seqname varchar2) as
/*重置 seq 为 MinValue, 不是Cycle的只能重置为MinValue + increment*/
  n number(10);
begin
  execute immediate 'select ' || v_seqname || '.nextval from dual' into n;
  execute immediate 'alter sequence ' || v_seqname || ' increment by ' || -n;
  begin
    execute immediate 'select ' || v_seqname || '.nextval from dual' into n;
  exception when others then--不是Cycle会出错
    execute immediate 'alter sequence ' || v_seqname || ' increment by ' || (1-n);-- -(n-1)  or -n+1
    execute immediate 'select ' || v_seqname || '.nextval from dual' into n;
  end;
  execute immediate 'alter sequence ' || v_seqname || ' increment by 1';
end prc_reset_seq;

文章作者： 正焕
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 海边的曼切斯特！
学习笔记
喜欢就支持一下吧