Oracle 备忘 SQL EMCTL 等

### OTHERS ###

// + 1/3 是调整到东八时区
SELECT round(sum(statValue)/ to_char(last_day(add_months(sysdate,1)),'dd'),1) as statValue FROM jiveStat
WHERE statID = 108 AND communityID = 1 AND
TO_CHAR((TO_TIMESTAMP('1970-01-01 00:00:00','yyyy-mm-dd HH24:Mi:SS')+ statDate/86400000+1/3),'yyyy-mm-dd HH24:Mi:SS')  >='2009-04-01 00:00:00'
AND 
TO_CHAR((TO_TIMESTAMP('1970-01-01 00:00:00','yyyy-mm-dd HH24:Mi:SS')+ statDate/86400000+1/3),'yyyy-mm-dd HH24:Mi:SS') < '2009-05-01 00:00:00'

select versions_starttime, versions_endtime, versions_xid, 
versions_operation, CLEARSPACE.JIVEIMAGE 
from JIVEIMAGE versions between timestamp 
to_date('26/03/2009 01:00:00', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME



select versions_starttime, versions_endtime, versions_xid, 
versions_operation, jiveimage 
from jiveimage versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME;


######### FINAL VERSION
SELECT USERNAME,FIRSTNAME,LASTNAME,CREATIONDATE_DT,EMAIL,a.USERID,MOBILEPHONE,OFFICEPHONE,HOMEPHONE,PROVINCE,CITY,ADDRESS,POSTALCODE,VIN FROM 
(SELECT DISTINCT jiveUser.USERID,jiveUser.FIRSTNAME,jiveUser.LASTNAME,TO_CHAR((TO_TIMESTAMP('1970-01-01 00:00:00','yyyy-mm-dd HH24:Mi:SS')+jiveUser.CREATIONDATE/86400000+1/3),'yyyy-mm-dd HH24:Mi:SS') AS CREATIONDATE_DT,jiveUser.CREATIONDATE,jiveUser.USERNAME,jiveUser.EMAIL,jiveUserProp.PROPVALUE AS VIN
FROM jiveUser
LEFT JOIN
jiveUserProfile
ON jiveUser.userid = jiveUserProfile.userid
LEFT JOIN
jiveUserProp 
ON jiveUser.userid = jiveUserProp.userid
WHERE  jiveUserProp.name='myBmwVin' AND jiveUser.CREATIONDATE > 1239336060000 ORDER BY jiveUser.CREATIONDATE ASC) a
LEFT JOIN
(SELECT distinct userid,
max(DECODE(fieldID, 1003,VALUE,null)) AS MobilePhone,
max(DECODE(fieldID, 1004,VALUE,null)) AS OfficePhone,
max(DECODE(fieldID, 1005,VALUE,null)) AS HomePhone,
max(DECODE(fieldID, 1006,VALUE,null)) AS Province,
max(DECODE(fieldID, 1007,VALUE,null)) AS City,
max(DECODE(fieldID, 1008,VALUE,null)) AS Address,
max(DECODE(fieldID, 1009,VALUE,null)) AS PostalCode
FROM jiveUserProfile  group by userid order by userid asc 
) b
ON a.userid = b.userid
ORDER BY userid ASC;

###emctl ###

emca -r
emctl status dbconsole
emca -config dbcontrol db
emctl stop dbconsole
emctl config emkey -repos -sysman_pwd
emctl secure dbconsole -sysman_pwd
emctl start dbconsole
less /opt/oracle/products/10.2.0/db_1/oracletest2/sysman/log/emoms.log
emctl start dbconsole
less /opt/oracle/products/10.2.0/db_1/oracletest2/sysman/log

emca -config dbcontrol db -repos recreate

####重新配置oracle dbconsole

1. reset the password for sys, sysman, and dbsnmp
2. set ORACLE_HOME and ORACLE_SID
3. emca -config dbcontrol db -repos recreate
4. emca -deconfig dbcontrol db -repos drop


1) Set the environment variable ORACLE_SID to YOUR_DB_SID
2) /opt/oracle/products/10.2.0/db_1/bin/emctl stop dbconsole
3) /opt/oracle/products/10.2.0/db_1/bin/emctl config emkey -repos -sysman_pwd < Password for SYSMAN user > 
4) /opt/oracle/products/10.2.0/db_1/bin/emctl secure dbconsole -sysman_pwd < Password for SYSMAN user > 
5) /opt/oracle/products/10.2.0/db_1/bin/emctl start dbconsole




常用的命令语法：

emca -repos create创建一个EM资料库

emca -repos recreate重建一个EM资料库

emca -repos drop删除一个EM资料库

emca -config dbcontrol db配置数据库的 Database Control

emca -deconfig dbcontrol db删除数据库的 Database Control配置

emca -reconfig ports 重新配置db control和agent的端口

emctl start console启动EM console服务，使用前需要先设置ORACLE_SID环境变量

emctl stop console停止EM console服务，使用前需要先设置ORACLE_SID环境变量

注：通过查看$ORACLE_HOME/install/portlist.ini 文件可以知道当前dbcontrol正在使用的端口，默认dbcontrol http端口1158，agent端口3938。如果要重新配置端口，可以使用如下命令：

emca -reconfig ports -dbcontrol_http_port 1159

emca -reconfig ports -agent_port 3939

使用命令行工具emctl可以启动/停止console服务，察看服务状态等。


####关于字符集

AL32UTF8

select name,value$ from props$ where name like '%NLS%';


ALTER DATABASE CHARACTER SET AL32UTF16;

select   *   from   props$   where   name   =   'NLS_CHARACTERSET';   

####查询结果写入csv

set feed off pause off pages 0 head off veri off line 500
set term off time off  trims on
set sqlprompt ''
set colsep ,
spool abcdef.csv
select column1,column2,column3 from table
spool off