Oracle常用

++++++++++++++++++++++++++++++++++++++++++++++++

表备份：

1.create tablename_bak_date as select * from tablename;         --create .. as select * from ..
2.select * into tablename_bak_date from tablename;--select * into .. from ..

查看用户是否被锁

select * from dba_users where username=''

根据列名查表名

SELECT * From Dba_Tab_Columns t WHERE t.COLUMN_NAME = '';

Oracle自带任务查询：

select * from dba_scheduler_jobs;

统计信息查询：

非分区表：

selecta.table_name,a.last_analyzed,a.num_rows
   from user_tables a
   where table_namein (...)

分区表（检查查询条件所涉及到的分区的收集情况）：

selecta.table_name,a.partition_name,a.last_analyzed,a.num_rows
  from user_tab_partitions a
 where table_namein (...)

复合分区表（存在子分区）

selecta.table_name,a.partition_name,a.subpartition_name,
       a.last_analyzed,a.num_rows
  from user_tab_subpartitions a
 wheretable_namein (...)

根据pid查询相关程序

 SELECT a.username,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       a.status,
       c.piece,
       c.sql_text
  FROM v$session a, v$process b, v$sqltext c
 WHERE b.spid = '11823'
   AND b.addr = a.paddr
   AND a.sql_address = c.address(+)
 ORDER BY c.piece; 

查询正在执行的sql

select a.program, b.spid, c.sql_text, c.SQL_ID
   from v$session a, v$process b, v$sqlarea c
  where a.paddr = b.addr
    and a.sql_hash_value = c.hash_value
    and a.username is not null;

删除表

drop table user.tablename

重命名表

alter table aaa rename to bbb