oracle操作语句

--建立 tablespace
create tablespace daat
datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/LANGGE/orcl_langgelila_01.dbf' size 100M reuse ;
--create tablespace tempInkfish
-- datafile '$ORACLE_HOME/dbs/orcl_tempInkfish_01.dbf' size 5M reuse
-- temporary;
--创建表空间，若需要的表空间大，可将其分为多个文件
create tablespace USERS
datafile 'C:/APP/ADMINISTRATOR/ORADATA/PRONES/PRONES001.DBF' size 5000m
autoextend on
next 500m
maxsize 30000m;

alter tablespace USERS
add datafile 'C:/oracle/oradata/PRONES/USERS_003.DBF' size 500m
autoextend on
next 500m
maxsize 30000m;

--创建用户
create user PRONES identified by PRONES
default tablespace USERS;

--分配权限
grant connect, resource,dba to PRONES
revoke unlimited tablespace from chinapaytm
alter user st quota 0 on zstest
alter user st quota unlimited on st
--grant create session to zoupeipei
--grant select on v$session to inkfish;
--grant select on v$sesstate to inkfish;
--grant select on v$statname to inkfish;
--存储过程增加建表权限
grant create any table to ACTONLINE;

导入导出
exp langgelila/langgelila@LANGGESERVER file=d:/lglldb/exp0605-02.dmp
imp PRONES/PRONES@PRONES file=C:\pronesOracleData\PRONESV20_DUMP_DATA.DMP full=y ignore=y log=C:\oracle\oradata\PRONES\uses.txt
exp daat/daat@ORCL102 file=d:/ams_bus_goods_0922_4500.dmp tables=ams_bus_goods_0922_4500 statistics=none

imp PRONES/PRONES@PRONES file=F:\PRONESV20_DUMP_DATA.DMP tables=(T_LOT_TRACE_TR,T_PO_TR) log=C:\imp3.txt

--shift+end选中一行
imp PRONES/PRONES@PRONES file=F:\PRONESV20_DUMP_DATA.DMP tables=(T_REQ_TR,T_SCH_WK_RSLT_STP,T_SM_PUR_AUDIT,T_SM_PUR_TR,T_SM_SALES_TR,T_SO_AUDIT,T_SO_TR,T_SO_UPD_STP,T_SPLY_AUDIT,T_SPLY_RSLT_TR,T_STD_CST_CALC_WK2,T_STD_CST_ERR_WK,T_STD_CST_MS,T_STD_CST_MS_BK,T_STD_CST_MS_BK2014,T_STOCK_ADJ_AUDIT,T_STOCK_ADJ_TR,T_STOCK_REG_STP,T_STOCK_TAKING_TR,T_STT_BALANCE_WK,T_STT_UPD_STP,T_SYS_CONDITION_MS,T_TERM_AUDIT,T_TERM_MS,T_TRADE_AUDIT,T_TRADE_AUDIT_L,T_TRADE_MS,T_TRADE_MS_ADS,T_TRADE_MS_L,T_TRADE_MS_SSSV20,T_TRF_MS,T_TRNF_RSLT_AUDIT,T_TRNF_RSLT_STP,T_TRNF_RSLT_TR,T_UNIT_MS,T_UNSCH_ACP_STP,T_UNSCH_SHIP_STP,T_UNSCH_WK_RSLT_STP,T_UP_AUDIT,T_UP_MS,T_UP_MS_03,T_UP_MS_04,T_UP_MS_113006,T_UP_MS_SSSV20,T_UP_UPD_STP,T_USER_DESKTOP_MS,T_VENDER_PAY_MS,T_VOUC_UPD_STP,T_WAREHOUSE_AUDIT,T_WAREHOUSE_MS,T_WK_PLAN_MS,VAD010_M_PRT) log=C:\imp4.txt

--查看用户权限
select * from user_sys_privs;
--更改用户密码
alter user prones identified by PRONES
--SELECT * FROM v$session

--alter session set nls_language='SIMPLIFIED CHINESE'

select * from V$NLS_PARAMETERS

--update V$NLS_PARAMETERS set value='SIMPLIFIED CHINESE' where parameter='NLS_CHARACTERSET'
--删除用户（cascade删除用户名下的所有表，删除该用户导入的表）
drop user PRONES cascade

--查看TABLESPACE
select tablespace_name,file_name from dba_data_files
select * from dba_data_files
--删除TABLESPACE
DROP TABLESPACE ACTONLINE INCLUDING CONTENTS AND DATAFILES
--删除table
DROP TABLE TABLENAME

--查看数据库版本
select * from v$version;

--查出oracle server端的字符集/
select userenv('language') from dual;
select * from nls_database_parameters;

 

select to_char(1290153469381/ 1000 / 60 / 60 / 24 + TO_DATE('1970-01-01 08:00:00', 'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd hh24:mi:ss')时间
from dual
--2009-02-02 11:05:48
--1249056000000 '2009-08-1 0:0:0'
--1251734400000 '2009-09-1 0:0:0'
SELECT (to_date('2009-11-26 0:00:00','yyyy.mm.dd hh24:mi:ss') - to_date('1970.01.01 08:00:00','yyyy.mm.dd hh24:mi:ss')) * 24 * 60 * 60 * 1000 cdate FROM dual;
--1230220800000 '2008-12-26 0:0:0'
--1233417600000 '2009-02-1 0:0:0'
--1235836800000 '2009-03-1 0:0:0'
--1238515200000 '2009-04-1 0:0:0'
--1254189600000 2009-09-29 10:00:00
--1252339200000 '2009-09-8 0:00:00'
--1252512000000 '2009-09-10 0:00:00'
--1230739200000 '2009-01-01 0:00:00'
--1262275200000 '2010-01-01 0:00:00'
SELECT (sysdate - to_date('1970.01.01 08:00:00','yyyy.mm.dd hh24:mi:ss')) * 24 * 60 * 60 * 1000 cdate FROM dual;

select sysdate from dual

insert into zs_job_test
select info1.totpoint,info1.time,info1.issuer_name,
nvl(info2.totuser,0),nvl(info3.dayuser,0) from
(
select sum(mmc.credit_point) totpoint,to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') time,
msi.issuer_name,to_char(msi.issuer_id) issuer_id from
mcms_membership_card mmc,mcms_service_issuer msi,mcms_end_user meu
where msi.issuer_id=mmc.issuer_id
and mmc.user_id=meu.user_id
and meu.status!=2
and mmc.issuer_id in (2100,1700)
group by msi.issuer_name,msi.issuer_id
)info1 left join
(
select count(distinct mmc.user_id) totuser,mmc.issuer_id
from mcms_membership_card mmc,mcms_end_user meu
where mmc.user_id=meu.user_id
and meu.status!=2
group by mmc.issuer_id
)info2 on info1.issuer_id=info2.issuer_id
left join
(
select count(distinct t.user_mobile) dayuser,substr(t.syn_comment,0,4)issuer_id
from mcms_client_syn t ,mcms_end_user meu
where t.operation_type=51 and t.status is null
and t.user_mobile=meu.user_id
and meu.status!=2
and t.syn_time between
(sysdate - to_date('1970.01.02 08:00:00','yyyy.mm.dd hh24:mi:ss')) * 24 * 60 * 60 * 1000
and
(sysdate - to_date('1970.01.01 08:00:00','yyyy.mm.dd hh24:mi:ss')) * 24 * 60 * 60 * 1000
group by substr(t.syn_comment,0,4)
)info3
on info1.issuer_id=info3.issuer_id;
--commit;
select count(distinct eu.user_id) 总激活用户数
from mcms_client_syn cs,mcms_end_user eu
where cs.user_mobile=eu.user_id and eu.status!='2'

 

select issuer_name,totpoint,totuser,dayuser,time from zs_job_test t
where t.time like '2009.01.14%'
order by issuer_name,time

select * from zs_job_test for update

select t.send_content,t.* from csm_msg_sms_send t order by t.send_add_time desc
select t.* from csm_msg_email_send t order by t.send_time desc

CREATE UNIQUE INDEX IDX_NAME
ON TABLE_NAME(COL_A, COL_B, ..., COL_N)