Oracle常用的SQL积累

1.查看正在执行的存储过程 :通过视图v$db_object_cache

select owner,name from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;

2.查看存储过程的内容：

SELECT    TEXT   FROM user_source   WHERE NAME = '存储过程名'   ORDER BY line;

3. 我想要查看之前执行过的sql语句或者存储过程执行的时间，要怎么办呢？

——可以通过oracle数据字典的一个视图 v$sql。这个可以看到执行过的sql...

但是这里有比较特别的地方，就是同一个语句或者存储过程，如果执行多次，不是多条记录，而是一条记录，只是executions在增加，LAST_ACTIVE_TIME 也是在更新，所以要求得执行时间使用ELAPSED_TIME/executions/1000

——总的时间/次数/1000000（微秒转为秒）

SELECT SQL_TEXT, LAST_ACTIVE_TIME,SQL_FULLTEXT ,LAST_LOAD_TIME,executions,
ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/executions
FROM V$SQL  WHERE INSTR(SQL_TEXT,'过程名')>0   ORDER BY LAST_ACTIVE_TIME  DESC

(V$SQL里面有很多的字段，比较难分清楚，ELAPSED_TIME是执行时间，有误差是因为执行多次取平均)（另外，Java程序等调用的存储过程，这里是没有记录的，O(∩_∩)O）

下面链接这里有V$SQL具体字段的解释(*^▽^*)，然后发现，比较麻烦的是很多时间用的是NUMBER类型，比如：所以上面的语句用的是ELAPSED_TIME/1000000/executions得到的秒

V$SQL

ELAPSED_TIME

NUMBER

该游标用于解析/执行/抓取的已用时间（以微秒为单位）

4.刷新一个表的统计信息,通过调用DBMS_STATS包

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => '数据库用户名',
                                 tabname => '表名',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                 cascade=>TRUE
                                 );
END;
/

5.查看表的索引状态

 select status,T.* from user_indexes T  where table_name='表名'

索引空间 ，表空间 ，剩余空间

select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' 
and segment_name like '%表名%' group by segment_name;

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments
 where segment_type='TABLE' --and segment_name like '%表名%' 
 group by segment_name;
 
select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

6.查看被锁/占用的对象（表...）

SELECT L.SESSION_ID      SID,
       S.SERIAL#,
       L.LOCKED_MODE     锁模式,
       L.ORACLE_USERNAME 登录用户,
       L.OS_USER_NAME    登录机器用户名,
       S.MACHINE         机器名,
       S.TERMINAL        终端用户名,
       O.OBJECT_NAME     被锁对象名,
       S.LOGON_TIME      登录数据库时间
  FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
 ORDER BY SID, S.SERIAL#;

杀死某个占用的进程 

alter system kill session '589,15771';--(其中24,111分别是上面查询出的sid,serial#

7.删除一个表重复的数据

前提：一个表里面，存在重复的数据，记录id相同，但是jgbs不同

操作：（多个重复字段同样的操作，这里单字段多字段都可以，多字段如：id和name）

--1.根据id分组，查出重复的id
select id from 表名 group by id  having count(id)>1
--2.根据id分组，查出重复的id,得到每个重复id记录中rowid最小（或最大）的一个
select min(rowid) from 表名  group by id having count(id)>1  
--3.删除重复id中除了rowid最小的其他的记录
delete   from  表名 where 
id in (select id from 表名 group by id  having count(id)>1 ) 
and rowid not in (select min(rowid) from 表名  group by id having count(id)>1 ) 

另外的方法，删除重复的列中不是最小rowid的列，保留一条记录

--方法一： 分组，每个组里面最小的一条记录，但是执行慢
delete  from 表名 y where y.rowid not in (select min(n.rowid) from 表名 n group by n.wzbs  ) 
--方法二： [更快]
delete from 表名 y where y.rowid > (select min(n.rowid) from 表名 n where n.wzbs = y.wzbs)
 --遍历Y表，找到N表中相同的记录，找出最小的一条，其他的删除[Y表N表是同一个表] 

8.查看一个表的创建时间

select created from dba_objects where object_name = '表名' and owner = '用户名'

9.查看一个表有无对应的外键 B是有外键的表 ，A的某个字段作为B的外键

SELECT A.OWNER,
       A.TABLE_NAME      PRIMARY_TABLE_NAME,
       A.CONSTRAINT_NAME PRIMARY_TABLE_KEY_NAME,
       B.OWNER,
       B.TABLE_NAME      FOREIGN_TABLE,
       B.CONSTRAINT_NAME FOREIGN_TABLE_FOREIGN_KEY_NAME,
       B.STATUS          FOREIGN_TABLE_FOREIGN_KEY_STAT
  FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
 WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
   AND B.CONSTRAINT_TYPE = 'R'
   AND A.TABLE_NAME = '表名'
   AND A.OWNER = '用户名'
   AND B.OWNER = '用户名'
 ORDER BY 1, 2, 3, 4;

10. 备份表；从一个数据库用户复制表的结构和数据；

复制之后数据和表字段都有，但是，字段备注和主键，索引等不在。

create table 备份表名 as select * from 表名；

只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;
--或者：
create table table_name_new like table_name_old；

11.扩展表空间【a.为表空间添加数据文件 b.设置表空间自动增长】

---/***执行插入的时候：报错：ORA-01653: 表 A 无法通过 128 (在表空间... 中) 扩展***/ 
--1.查询表空间信息 
select * from DBA_TABLESPACES where tablespace_name = '表空间名' 
--2.查询剩余表空间 bytes转换成M 
SELECT tablespace_name,sum(bytes)/1024/1024 FROM DBA_FREE_SPACE where tablespace_name = '表空间名' group by tablespace_name
 --3.表空间的 数据文件信息[一个表空间有多个表空间文件] 
select * from DBA_DATA_FILES where tablespace_name = '表空间名'
--4.添加一个数据文件[表空间数据文件 10M好像太小] 数据文件路径名称,如：/data/oracle/DATA01
alter tablespace 表空间名  add datafile '数据文件路径名称' size 10m autoextend on next 5M maxsize 1000M; 
--修改表空间的大小[不要改太小了] 20000M
alter database datafile '数据文件路径名称' resize 4000m 
--5.或者设置表空间自动增长？扩大数据文件的大小？ 
--查询当前数据库中表空间是否为自动扩展 		  
select tablespace_name,file_name,autoextensible from dba_data_files   where tablespace_name = '表空间名';

12. 分区

---删除分区数据【只删数据不删除分区】
ALTER TABLE 表名 TRUNCATE PARTITION  分区名;
---删除分区【包括数据和分区】
ALTER TABLE tableName DROP PARTITION partionName;

13.约束

14.不同数据库通过链接复制表结构、表数据

--第一步：在要建表的数据库用户下，建立链接
CREATE database link_name  --//链接名称
CONNECT to  username     --//被连接的数据库的用户名
IDENTIFIED by password --//被连接的数据库的密码
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 地址)(PORT = 端口号))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SID或服务名)))';--//被连接的数据库配置


--第二步：创建表包括复制数据,通过 @链接名 
CREATE TABLE TABLE_NEW_A AS SELECT * FROM TABLE_A@link_name WHERE  1=1;
-- 复制表数据
INSERT INTO TABLE_NEW_A  SELECT * FROM TABLE_A@link_name T ;

--第三步：查询然后删除链接
select * from dba_db_links;

drop database link link_name;