Oracle命令汇总

1.查看Oracle版本?
select * from v$version;

2.查看user session的最大值和当前值?
select * from v$license;

3.怎么获取有哪些用户在使用数据库?
SELECT * FROM V$SESSION;

4.查询数据库有多少表?
SELECT count(*) from all_tables where owner='DB_NAME';
SELECT count(*) FROM USER_ALL_TABLES
select count(*) from ALL_ALL_TABLES where owner='DB_NAME'

5.查看数据库的参数值?
SELECT * FROM V$PARAMETER where name ='db_block_size'

6.怎样计算数据库内每个表占用的空间的大小？
select owner,table_name,NUM_ROWS,EMPTY_BLOCKS,BLOCKS*AAA/1024/1024 "Size M",LAST_ANALYZED from dba_tables where owner='XXX ' order by num_rows desc;
Here： AAA is the value of db_block_size；
XXX is the database name you want to check

7.查询表的字段详细信息?
select * from all_tab_columns where table_name ='TB_NAME'
desc TB_NAME

8.如何查看系统被锁的事务时间？
select * from v$locked_object

9.查询锁的状况的对象有?
V$LOCK， V$LOCKED_OBJECT， V$SESSION， V$SQLAREA， V$PROCESS

10.查询锁的表的方法:
SELECT S.SID SESSION_ID,S.USERNAME,DECODE(LMODE,0,'None',1,'Null',2,'Row - S(SS) ',3,' Row - X(SX) ',4,' Share',5,'S / Row - X(SSX) ',6,' Exclusive',TO_CHAR(LMODE)) MODE_HELD,DECODE(REQUEST,0,'None',1,'Null',2,'Row - S(SS) ',3,' Row - X(SX) ',4,' Share',5,'S / Row - X(SSX) ',6,' Exclusive',TO_CHAR(REQUEST)) MODE_REQUESTED,O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')' ,S.TYPE LOCK_TYPE,L.ID1 LOCK_ID1,L.ID2 LOCK_ID2
FROM V$LOCK L,SYS.DBA_OBJECTS O,V$SESSION S
WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID

11.如何解锁?
select sid,serial# from v$session User_Info;
ALTER SYSTEM KILL SESSION ‘SID，SERIR#’；

12.怎样查得数据库的SID ?
select name from v$database

13.如何在Oracle服务器上通过SQLPLUS查看本机IP地址和时间 ?
select sys_context('userenv','ip_address') from dual
select systimestamp from dual

14.USERENV(OPTION)返回当前的会话信息。
OPTION='ISDBA'若当前是DBA角色，则为TRUE，否则FALSE。
OPTION='LANGUAGE'返回数据库的字符集。
OPTION='SESSIONID'为当前会话标识符。
OPTION='ENTRYID'返回可审计的会话标识符。
OPTION='LANG'返回会话语言名称的ISO简记。
OPTION='INSTANCE'返回当前的实例。
SELECT USERENV('LANGUAGE') FROM DUAL;

select HANDLECODE from TB_WARNINFO where trim(' ' from HANDLECODE) is not null

create table "TB_CP_BAK" as select * from "TB_CP" where '1' = '1' 复制表

select a.username,a.osuser,a.status,a.machine,a.terminal,a.STATUS,b.sql_text,sid,serial#,a.logon_time from v$session a,v$sqltext b where a.sql_address=b.address 查询数据库使用者信息

select * from ALL_PROCEDURES where owner ='UNIMUSIC'
select text from user_source where name='SP_MANAGE_OPERSERIES'; 查询存储过程源代码

（SELECT * FROM A）MINUS（SELECT * FROM B）

oracle更改数据库字符集编码!
一.准备修改。首先必须用sys连接。

1.修改sys密码.用其他dba权限的账户登陆后输入：alter user sys identified by 新密码;

2.连接刚刚修改的sys（要在登陆状态下）,CONNECT SYS／密码 as sysdba;

二.开始修改:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use UTF-8;
SQL> shutdown immediate;
SQL> startup


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/javatim/archive/2008/07/16/2661187.aspx

查重
select orderuser from Tb_UserOrderInfo group by orderuser having(count(*)>1)

oracle中的tab键表达
select 'a'||chr(9)||'b' from dual;