Category Archives: Oracle

windows下oracle手工创建和删除数据库

Posted on by

创建oracle_sid为feifei的数据库

1、设置oracle_sid：set oracle_sid=feifei
2、建立pfile文件：文件名为：initfeifei.ora
内容为：

feifei.__db_cache_size=79691776
feifei.__java_pool_size=4194304
feifei.__large_pool_size=4194304
feifei.__shared_pool_size=100663296
feifei.__streams_pool_size=12582912
audit_file_dest='C:\oracle\product\10.2.0/admin/feifei/adump'
background_dump_dest='C:\oracle\product\10.2.0/admin/feifei/bdump'
compatible='10.2.0.1.0'
control_files='C:\oracle\product\10.2.0\oradata\feifei\control01.ctl','C:\oracle\product\10.2.0\oradata\feifei\control02.ctl','C:\oracle\product\10.2.0\oradata\feifei\control03.ctl'
core_dump_dest='C:\oracle\product\10.2.0/admin/feifei/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='feifei'
db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=feifeiXDB)'
job_queue_processes=10
log_archive_dest_1='location=C:\oracle\product\10.2.0\archive'
open_cursors=300
pga_aggregate_target=20971520
processes=150
remote_login_passwordfile='EXCLUSIVE'
resource_limit=TRUE
sga_target=209715200
sort_area_size=65536#test
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='C:\oracle\product\10.2.0/admin/feifei/udump'
utl_file_dir='c:\temp'

note：如果不是很熟悉可以从其他数据库拷贝

3、建立pfile文件中的相关的文件夹
4、建立oracle服务进程，并创建oracle密码文件
oradim -new -sid %ORACLE_SID% -intpwd MYSECRETPASSWORD -startmode M
5、执行sqlplus /nolog和connect sys/MYSECRETPASSWORD as sysdba(conn / as sysdba)(两种不同的认证方式)
6、执行startup nomount命令
7、执行create database命令

create database feifei
 logfile   group 1 ('C:\oracle\product\10.2.0\oradata\feifei\redo1.log') size 10M,
 group 2 ('C:\oracle\product\10.2.0\oradata\feifei\redo2.log') size 10M,
 group 3 ('C:\oracle\product\10.2.0\oradata\feifei\redo3.log') size 10M
 character set          WE8ISO8859P1
 national character set utf8
 datafile 'C:\oracle\product\10.2.0\oradata\feifei\system.dbf'
 size 50M
 autoextend on
 next 10M maxsize unlimited
 extent management local
 sysaux datafile 'C:\oracle\product\10.2.0\oradata\feifei\sysaux.dbf'
 size 10M
 autoextend on
 next 10M
 maxsize unlimited
 undo tablespace UNDOTBS1
 datafile 'C:\oracle\product\10.2.0\oradata\feifei\undo.dbf'
 size 10M
 default temporary tablespace temp
 tempfile 'C:\oracle\product\10.2.0\oradata\feifei\temp.dbf'

note：可以把create database写在notepad中，然后通过@path执行
8、添加需要的表空间

create tablespace Usersdatafile  'C:\oracle\product\10.2.0\oradata\feifei\user01.dbf'
 size 50M
 autoextend on
 next 10M maxsize unlimited

9、执行相关的sql脚本，主要有：
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql(主要是数据字典)
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql(主要是pl/sql相关操作)
C:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql(主要是sqlplus相关操作)
至此手工创建数据库基本完成，当然在生产系统中，创建数据库要比这个复杂的多，主要集中在创建的pfile文件和create database命令的复杂

删除oracle_sid=feifei数据库
1、注销掉windows服务的oracle进程oradim -delete -sid feifei
2、删除oracle创建的相关文件主要是：
1）C:\oracle\product\10.2.0\oradata\feifei下的文件
2）C:\oracle\product\10.2.0\admin\feifei下的文件
3）C:\oracle\product\10.2.0\db_1\database下文件名有feifei的文件

常见数据库对日期时间格式化

Posted on by

1、mysql

DATE_FORMAT(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中：

%M  月名字(January……December)
%W  星期名字(Sunday……Saturday)
%D  有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y  年, 数字, 4 位
%y  年, 数字, 2 位
%a  缩写的星期名字(Sun……Sat)
%d  月份中的天数, 数字(00……31)
%e  月份中的天数, 数字(0……31)
%m  月, 数字(01……12)
%c  月, 数字(1……12)
%b  缩写的月份名字(Jan……Dec)
%j  一年中的天数(001……366)
%H  小时(00……23)
%k  小时(0……23)
%h  小时(01……12)
%I  小时(01……12)
%l  小时(1……12)
%i  分钟, 数字(00……59)
%r  时间,12 小时(hh:mm:ss [AP]M)
%T  时间,24 小时(hh:mm:ss)
%S  秒(00……59)
%s  秒(00……59)
%p  AM或PM
%w  一个星期中的天数(0=Sunday ……6=Saturday ）
%U  星期(0……52), 这里星期天是星期的第一天
%u  星期(0……52), 这里星期一是星期的第一天
%%  一个文字“%”

2、sql server

sql server转换使用convert来实现


Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 28 ????? 1431  3:26:31:180PM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

3、oracle

oracle转换日期或者时间用to_char(datetime, format)

format的参数有

HH     一天的小时数 (01-12)
HH12     一天的小时数 (01-12)
HH24     一天的小时数 (00-23)
MI     分钟 (00-59)
SS     秒 (00-59)
SSSS     午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.     正午标识（大写）
am or a.m. or pm or p.m.     正午标识（小写）
Y,YYY     带逗号的年（4 和更多位）
YYYY     年（4和更多位）
YYY     年的后三位
YY     年的后两位
Y     年的最后一位
BC or B.C. or AD or A.D.     年标识（大写）
bc or b.c. or ad or a.d.     年标识（小写）
MONTH     全长大写月份名（9字符）
Month     全长混合大小写月份名（9字符）
month     全长小写月份名（9字符）
MON     大写缩写月份名（3字符）
Mon     缩写混合大小写月份名（3字符）
mon     小写缩写月份名（3字符）
MM     月份 (01-12)
DAY     全长大写日期名（9字符）
Day     全长混合大小写日期名（9字符）
day     全长小写日期名（9字符）
DY     缩写大写日期名（3字符）
Dy     缩写混合大小写日期名（3字符）
dy     缩写小写日期名（3字符）
DDD     一年里的日子(001-366)
DD     一个月里的日子(01-31)
D     一周里的日子(1-7；SUN=1)
W     一个月里的周数
WW     一年里的周数
CC     世纪（2 位）
J     Julian 日期（自公元前4712年1月1日来的日期）
Q     季度
RM     罗马数字的月份（I-XII；I=JAN）－大写
rm     罗马数字的月份（I-XII；I=JAN）－小写

精彩SQL语句

Posted on by

1、oracle求每月第一天和最后一天

SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
 Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
 Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
 LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;

2、sql server对日期的常规处理

SELECT REPLACE(CONVERT(varchar(10),GETDATE(),120),N'-0','-')  短日期格式：yyyy-m-d
SELECT STUFF(STUFF(CONVERT(char(8),GETDATE(),112),5,0,N'年'),8,0,N'月')+N'日'  长日期格式：yyyy年mm月dd日 --A. 方法1
SELECT DATENAME(Year,GETDATE())+N'年'+DATENAME(Month,GETDATE())+N'月'+DATENAME(Day,GETDATE())+N'日'   长日期格式：yyyy年mm月dd日 --A. 方法2
SELECT DATENAME(Year,GETDATE())+N'年'+CAST(DATEPART(Month,GETDATE()) AS varchar)+N'月 '+DATENAME(Day,GETDATE())+N'日'  短日期格式：yyyy年m月d日
SELECT CONVERT(char(11),GETDATE(),120)+CONVERT(char(12),GETDATE(),114)  完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
Select CONVERT(varchar(100), GETDATE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16

3、oracle中400w条记录的表中随机删除20条记录

--使用for
declare 
begin
     for c in (select * from (  select rowid rd from t2  order by dbms_random.value)m where rownum<=20) 
      loop
        delete from t2 where rowid=c.rd;
      end loop;
  commit;
end;
--不使用for
declare abc number;
arid rowid;
cursor cursor_sal is
 select * from (select rowid rd from t2  order by dbms_random.value)m where rownum<=20;
begin
      open cursor_sal;
      loop
        fetch cursor_sal into arid;
         exit when cursor_sal%notfound;
        delete from t2 where rowid=arid;
      end loop;
      close cursor_sal; 
  commit;
end;

oracle常用语句

Posted on by

1、set timing on开启sql执行计时
2、查看granule大小（granule大小是以SGA大小由系统设定的，当SGA小于1G时，granule大小为4M,当SGA大于1G的时候granule大小为16M）
select component,granule_size from v$sga_dynamic_components;
3、启动数据库受限模式
startup restrict;
如果是open状态转换为受限模式为：
alter system enable restricted session;
授予restricted session权限给用户即可访问受限模式
4、kill session

select sid,serial#,username from v$session;
alter system kill session 'sid,serial#';

5、查看未commit的事务
select addr,status from v$transaction;