7.31 Oracle 临时表
-- Create table
create global temporary table T_TEMP_TEST_RATE
(
name VARCHAR2(512),
rate NUMBER,
revenue NUMBER
)
on commit preserve rows;
global:临时表
oracle临表两种
on commit delete row; --默认选项commit候数据删除,事务级临时表
on commit preserve row; --commit候数据保留结束自删除,会话级临时表
ddl语句(create table ddl)发oracle隐式提交事务(commit)刚刚插入临表数据自删除;
参考:
http://www.cnblogs.com/kerrycode/archive/2014/03/26/3627334.html
select * from v$lock_type where type='TO' ;
TO :Temp Object
7.32 Trace日志
/opt/oracle/product/diag/rdbms/testdb/testdb/trace/testdb_ora_68201.trc
7.33 Insert 语句
从一张表里查询数据插入另一张表:
insert into t_sys_info_test_liu valuses select * from t_sys_info_test_w;
带有函数的需要嵌套:
insert into A (id,name,age,class)
select SEQ_A.nextval,b_name,b_age,b_class
from
(select
b_name,b_age,b_class
from b
group by b_name,b_age,b_class
-- Create table
create global temporary table T_TEMP_TEST_RATE
(
name VARCHAR2(512),
rate NUMBER,
revenue NUMBER
)
on commit preserve rows;
global:临时表
oracle临表两种
on commit delete row; --默认选项commit候数据删除,事务级临时表
on commit preserve row; --commit候数据保留结束自删除,会话级临时表
ddl语句(create table ddl)发oracle隐式提交事务(commit)刚刚插入临表数据自删除;
参考:
http://www.cnblogs.com/kerrycode/archive/2014/03/26/3627334.html
select * from v$lock_type where type='TO' ;
TO :Temp Object
7.32 Trace日志
/opt/oracle/product/diag/rdbms/testdb/testdb/trace/testdb_ora_68201.trc
7.33 Insert 语句
从一张表里查询数据插入另一张表:
insert into t_sys_info_test_liu valuses select * from t_sys_info_test_w;
带有函数的需要嵌套:
insert into A (id,name,age,class)
select SEQ_A.nextval,b_name,b_age,b_class
from
(select
b_name,b_age,b_class
from b
group by b_name,b_age,b_class
)
7.25 删除正在连接数据库的用户
-- 1、首先切换到 SYS 用户
-- 2、查询用户各进程相对应的 sid、serial#。注意:这里的 username 必须大写
SELECT sid,serial#,username FROM v$session WHERE username = 'TEST2011';
-- 下面是查询结果
SID SERIAL# USERNAME
---------- ---------- ------------------------------
120 61422 TEST2011
131 62682 TEST2011
133 45409 TEST2011
148 38527 TEST2011
157 21137 TEST2011
-- 3、根据上面的查询结果,将用户占用的所有进程杀掉。注意: sid 与 serial# 是一一对应的
ALTER SYSTEM KILL SESSION '120, 61422';