oracle常规操作

create table as select * from 语句会缺失索引

 

--database link
create public database link LINK_DEV_FOSSDBSB
connect to stl identified by fosssit
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fossdbsb)
)
)';

-----删除DBLINK
DROP PUBLIC DATABASE LINK LINK_DEV_FOSSDBSB;

-----通过DBLINK查询数据
SELECT * FROM STL.T_STL_OTHER_REVENUE @LINK_DEV_FOSSDBSB t where rownum<=100

insert into table_name select * from STL.T_STL_OTHER_REVENUE @LINK_DEV_FOSSDBSB t where rownum<=100

 

账号被锁:

the account is locked

dba 登录

alter user username account unlock

 

 

 

日期函数

oracle to_date(),to_timestamp 函数中的模板和待转换目标汇总的符号标志必须保持一致,否则会

出现此原因是由于

基于现有表创建新表

create table ${newTableName} as select * from ${originalTableName}

取差集
(select * from a minus select * from b)
取交集
select * from a
intersect
select * from b

声明字段数组

DECLARE 
  -- only 2 fileds 
  TYPE t_record_org IS RECORD( 
    site_code   t_base_org.site_code%type/*,
    user_name xxuser.user_name%type*/); 

  TYPE t_org IS TABLE OF t_record_org INDEX BY BINARY_INTEGER; 

  v_arry_org t_org; 
BEGIN 
  SELECT site_code BULK COLLECT INTO v_arry_org FROM t_base_org;
  FOR i IN 1 .. v_arry_org.COUNT LOOP 
    dbms_output.put_line(v_arry_org(i).site_code); 
  END LOOP; 
END;

 删除临时表,出错处理

（1）---查找还在使用临时表的会话

 SELECT sid, serial#   FROM v$session                                                     

WHERE sid = (SELECT sid FROM v$lock                                                                                          

WHERE id1 = (SELECT object_id  FROM user_objects                                                                                                                     

WHERE object_name = upper('你的临时表名')));

（2）--使用上面查出的sid和serial#，杀掉会话

 ALTER system kill session 'sid,serial#';

这样操作之后你再修改你的临时表或者drop都没有报错了。