经常用到的sql

T_tmp表为例子

---创建表根据另一张表的结构（无数据）

create table T_tmp as select * from T where 1=2;

---删除表里的字段

alter table T_tmp drop column ename;

---截断表里的数据

truncate table T_tmp;

-----查看哪些表被锁
select p.spid,
       p.USERNAME,
       a.sid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name,
       a.MACHINE,
       c.status
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;

---杀进程

alter system kill session  'SID,SERIAL#';

---查看最大的表

select owner, segment_name, sum(bytes) / 1024 / 1024 / 1024 GB
  from dba_segments
 group by owner, segment_name
having sum(bytes) > 800000000;

 

---查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--日志的大小该如何设置看历史：
select to_char(first_time,'yyyy/mm/dd:hh24'),count(*) from v$log_history group by to_char(first_time,'yyyy/mm/dd:hh24');

每小时产生的日志的个数：
--SQL> select to_char(first_time,'yyyy/mm/dd:hh24'),count(*) from v$log_history group by to_char(first_time,'yyyy/mm/dd:hh24');

TO_CHAR(FIRST   COUNT(*)
------------- ----------
2012/06/15:18          1
2012/06/15:17          1
2012/06/16:12          2
2012/06/16:13          4

--每天产生的日志个数：
SQL> select to_char(first_time,'yyyy/mm/dd'),count(*) from v$log_history group by to_char(first_time,'yyyy/mm/dd');

TO_CHAR(FI   COUNT(*)
---------- ----------
2012/06/15          2
2012/06/16          6
--获取自增ID及主键ID方法
select  f_getnid() from dual；

 ----添加一个列

alter table T_tmp add (USERID  VARCHAR2(64));   

---- 添加多个字段

alter table T_tmp  add (DISABLE_OPID  VARCHAR2(200),
DISABLE_DATE TIMESTAMP,
CREATE_OPID VARCHAR2(100),
CREATE_DATE TIMESTAMP,
REC_STATUS VARCHAR2(6));


----修改表名

rename  T_tmp to tt;

----修改列

alter table 表名 modify 字段名 类型;

例如：alter table T_tmp  modify ename NUMBER(8,2);

 

---创建表空间

create tablespace QQ

logging
datafile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SHWT.dbf'
size 24m
autoextend on
next 12m maxsize unlimited
extent management local;

-----批处理 在dblink 的基础上（使用的是users表空间）
select 'create table '|| TNAME ||' tablespace users as select * from '|| TNAME ||'@test;' from tab;

---创建dblink

create database link test connect to 源的用户 identified by "源的密码" using '目标tnsnames.ora里的标识符';

  -------------查看存储过程 
            select distinct us.name from user_source us where us.TEXT like '%TT%'