oracle常用语法

 

查看机器名、程序占用的连接数

select b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null 
group by b.MACHINE , b.PROGRAM order by count(*) desc;

创建分区

alter table table_name add 

    partition M201306 values less than ('20130701')

    tablespace TS_DTL

    pctfree 10

    initrans 30

    maxtrans 255

    storage

    (

        initial 4M

        minextents 1

        maxextents unlimited

    );

 

----查看用户连接数

select username,count(username) from v$session where username is not null group by username;

 

 ----查看temp表空间占用率

select c.tablespace_name,

to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,

to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,

to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from (select tablespace_name,sum(bytes) bytes

from dba_temp_files GROUP by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool GROUP by tablespace_name) d

where c.tablespace_name = d.tablespace_name;

 

 

**********查表空间大小**********

SELECT a.tablespace_name "表空间名", 
round(total/(1024 * 1024 * 1024),4) "表空间大小", 
round(free/(1024 * 1024 * 1024),4) "表空间剩余大小", 
round((total - free)/(1024 * 1024 * 1024),4) "表空间使用大小", 
round(total / (1024 * 1024 * 1024),4) "表空间大小(G)", 
round(free / (1024 * 1024 * 1024),4) "表空间剩余大小(G)", 
round((total - free) / (1024 * 1024 * 1024),4) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name 
order by round((total - free) / total, 4) * 100 desc

 

 

 **********查表空间实际大小**********

SELECT a.tablespace_name "表空间名",
round(a.bytes / 1024 / 1024 / 1024, 2) "当前总空间(G)",
round(d.max_tbspace/1024/1024/1024,2) "最大总空间(G)",
round(b.bytes / 1024 / 1024 / 1024, 2) "已使用空间(G)",
round((d.max_tbspace-b.bytes) / 1024 / 1024 / 1024, 2) "剩余空间(G)",
round((b.bytes * 100) / d.max_tbspace, 2) "使用率(%)"
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c,
(select tablespace_name,sum(case when dd.AUTOEXTENSIBLE='YES' then MAXBYTES else dd.BYTES end) as max_tbspace from DBA_DATA_FILES dd
group by dd.tablespace_name) d
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
and a.tablespace_name=d.tablespace_name
order by a.tablespace_name;

 

**********表空间是否自增长**********

select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc;

 Oracle 11g 监听很慢，由于监听日志文件太大引起的问题（Windows 下）

可能原因是监听日志过大（超过4G）。
　　停止监听，删除监听日志（需要删除4个目录的日志），开启监听，连接很慢问题解决。

　　Oracle 11g 监听日志目录位置如下：

　　alert 日志目录：%ORACLE_HOME%\diag\rdbms\%sid%\%sid%\alert，%ORACLE_HOME%\diag\tnslsnr\%机器名%\listenrt\alert

　　trace 日志目录：%ORACLE_HOME%\diag\rdbms\%sid%\%sid%\trace，%ORACLE_HOME%\diag\tnslsnr\%机器名%\listenrt\trace

 

 

**********修改SGA**********

SQL> show parameter sga;

SQL> alter system set sga_max_size=4096M scope=spfile;
System altered.
SQL> alter system set sga_target=4096M scope=spfile;
System altered.

 

**********有条件的导出**********

exp emr_sj/emr@10.10.150.2/orcl tables=(RCD_RECORD_DATA) file=c:\xxx.dmp query='where id= '

 

**********不同表空间导入： ignore=y ,先建好表再imp**********

imp hlyy/hlyy@orcl ignore=y file=C:\Users\wangw\Desktop\Temp\emr\xxx.dmp fromuser=emr_sj touser=hlyy

**********oracle11g空表处理**********

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
然后将执行结果复制到另一个SQL窗口，并执行。

 

**********导入导出**********

导出： exp test/test@server_name file=c:\备份文件.dmp owner=user
导入： imp test/test@server_name file=c:\备份文件.dmp fromuser=test touser=test

 

********创建序列************

CREATE SEQUENCE emp_sequence --序列名
INCREMENT BY 1 -- 每次加几个 
START WITH 1 -- 从1开始计数 
NOMAXVALUE -- 不设置最大值 
NOCYCLE -- 一直累加，不循环 
CACHE 10;

 

**********创建数据表空间 **********

create tablespace yuhang_data 

logging 
datafile 'D:\oracledata\yuhang_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;

 

 

**********加表空间**********

alter tablespace CDRDATA add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CDR\CDRDATA7.DBFF' size 30G autoextend on next 30M;