Oracle 最大連線數
2013-09-14 12:09
Oracle 的最大連接數是在參數SESSIONS中,可以用
select value from v$parameter where name = 'sessions'
SQL 實例
看由各Session 的各Client下SQL指令個數
2013-09-14 12:06
select B.MACHINE,A.sid,A.CNT,to_char(B.LOGON_TIME,'yyyymmdd HH24MIss') as LDATE ,A.SQL_TEXT
from (
select sid, SQL_TEXT,count(*) as CNT
from v$open_cursor
group by sid,SQL_TEXT order by CNT
) A , v$session B
where A.sid = B.sid
--and B.MACHINE = 'cclsunap2'
order by B.MACHINE,CNT
Oracle sysdate 運算
2013-08-22 15:08
###################################################################################
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
#ORACLE_TERM=xterm;
#export...
將 Oracle table 中的資料變成文字檔
2013-08-22 03:03
[oracle@voipdb1 report]$ cat allcdr.sh
###################################################################################
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export...
顯示已建立的 DB Link
2013-08-20 17:40
SQL> column OBJECT_NAME format a20
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME ...
查 CONSTRAINT
2013-08-06 15:10
SQL> set pages 5000
SQL> col owner for a10
SQL> select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where OWNER='VOIP' order by 3,4,1
OWNER CONSTRAINT_NAME C...
Oracle Tablespace 空間查詢
2013-07-31 16:01
SQL> select a.TABLESPACE_NAME, to_char(a.BYTES/(1024*1024),'999,999') "Size(MB)", to_char(round((a.BYTES-b.BYTES)/(1024*1024),0),'999,999') "Used(MB)", to_char(b.BYTES/(1024*1024),'999,999') "Avail(MB)" from (select TABLESPACE_NAME, sum(BYTES) "BYTES" from...
查 redo log Group 和 Member
2013-07-31 15:55
SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# ...
改 NLS_CHARACTERSE 方法
2013-07-29 23:29
要更改NLS_CHARACTERSET的值, 例如要改成 AL32UTF8 , 步驟如下:
以 sysdba 角色進入 sqlplus 之後執行以下
shutdown immediate;
STARTUP MOUNT;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE...
查 flash recovery usage
2013-07-29 21:48
SQL> set newpage 2
SQL> set pagesize 500 linesize 200
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------...