在現實的應用中大家可能經常會遇到在db2中如何實現oracle的某些功能﹐
在此我簡單地總結一下﹐實現某一功能可能會有很多種方法﹐在此就沒有全部列出﹐
歡迎大家繼續﹐以便和大家共享﹐共同探討﹐共同近步﹗(以下主要以oracle
8i,9i和db2 7.x為例)。
1.如何查看數据庫的版本的oracle和db2的寫法
oracle 可以這樣實現﹕
sql> connect system/manager124@test;
已連線.
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle9i enterprise edition release 9.2.0.1.0 - production
pl/sql release 9.2.0.1.0 - production
core 9.2.0.1.0 production
tns for 32-bit windows: version 9.2.0.1.0 - production
nlsrtl version 9.2.0.1.0 - production
db2 可以這樣實現﹕
在命令窗口執行 db2level
d: qllib\bin>db2level
db210851 instance "db2" uses db2 code release "sql07020" with level identifier
"03010105" and informational tokens "db2 v7.1.0.40","n010415" and "wr21254".
2.如何快速清空一個大表的oracle和db2的寫法
oracle 可以這樣實現﹕
sql>truncate table table_name;
db2 可以這樣實現﹕
alter table table_name active not logged initially with empty table;
3.如何查看表空間的使用狀況的oracle和db2的寫法
oracle 可以這樣實現﹕
select tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, 99.99) || % as
pct_free
from ( select tablespace_name,sum(bytes)/1024/1024 as sum_m from dba_data_files group by tablespace_name),
( select tablespace_name as fs_ts_name, max(bytes)/1024/1024 as max_m, count(blocks) as count_blocks,
sum(bytes/1024/1024) as sum_free_m from dba_free_space group by tablespace_name )
where tablespace_name = fs_ts_name
db2 可以這樣實現﹕
list tablespace containers for 你的表空間號 show detail;
4.如何從一時間點取出日期的各部分的常用的oracle和db2的寫法
oracle 可以這樣實現﹕
1>.取時間點的年份的寫法:
select to_char(sysdate,yyyy) from dual;
2>.取時間點的月份的寫法:
select to_char(sysdate,mm) from dual;
3>.取時間點的日的寫法:
select to_char(sysdate,dd) from dual;
4>.取時間點的時的寫法:
select to_char(sysdate,hh24) from dual;
5>.取時間點的分的寫法:
select to_char(sysdate,mi) from dual;
6>.取時間點的秒的寫法:
select to_char(sysdate,ss) from dual;
7>.取時間點的毫秒的寫法:(9i以上)
select substr(systimestamp,20,6) from dual;
8>.取時間點的日期的寫法:
select trunc(sysdate) from dual;
9>.取時間點的時間的寫法:
select to_char(sysdate,hh24:mi:ss) from dual;
10>.日期,時間形態變為字符形態
select to_char(sysdate) from dual;
11>.將字符串轉換成日期或時間形態:
select to_date(2003/08/01) from dual;
12>.返回參數的星期几的寫法:
select to_char(sysdate,d) from dual;
13>.返回參數一年中的第几天的寫法:
select to_char(sysdate,ddd) from dual;
14>.返回午夜和參數中指定的時間值之間的秒數的寫法:
select to_char(sysdate,sssss) from dual;
15>.返回參數中一年的第几周的寫法:
select to_char(sysdate,ww) from dual;
db2 可以這樣實現﹕
1>.取時間點的年份的寫法:
select year(current timestamp) from sysibm.sysdummy1;
2>.取時間點的月份的寫法:
select month(current timestamp) from sysibm.sysdummy1;
3>.取時間點的日的寫法:
select day(current timestamp) from sysibm.sysdummy1;
4>.取時間點的時的寫法:
select hour(current timestamp) from sysibm.sysdummy1;
5>.取時間點的分的寫法:
select minute(current timestamp) from sysibm.sysdummy1;
6>.取時間點的秒的寫法:
select second(current timestamp) from sysibm.sysdummy1;
7>.取時間點的毫秒的寫法:
select microsecond(current timestamp) from sysibm.sysdummy1;
8>.取時間點的日期的寫法:
select date(current timestamp) from sysibm.sysdummy1;
9>.取時間點的時間的寫法:
select time(current timestamp) from sysibm.sysdummy1;
10>.日期,時間形態變為字符形態:
select char(current date) from sysibm.sysdummy1;
select char(current time) from sysibm.sysdummy1;
select char(current date+12 hours) from sysibm.sysdummy1;
11>.將字符串轉換成日期或時間形態:
select timestamp(2002-10-20-12.00.00.000000) from sysibm.sysdummy1;
select timestamp(2002-10-20 12:00:00) from sysibm.sysdummy1;
select date(2002-10-20) from sysibm.sysdummy1;
select date(10/20/2002) from sysibm.sysdummy1;
select time(12:00:00) from sysibm.sysdummy1;
select time (12.00.00) from sysibm.sysdummy1;
12>.返回參數的星期几的寫法:
select dayname(current timestamp) from sysibm.sysdummy1;
select dayofweek(current timestamp) from sysibm.sysdummy1;
select dayofweek_iso(current timestamp) from sysibm.sysdummy1;
13>.返回參數一年中的第几天的寫法:
select dayofyear(current timestamp) from sysibm.sysdummy1;
14>.返回午夜和參數中指定的時間值之間的秒數的寫法:
select midnight_seconds(current timestamp) form sysibm.sysdummy1;
15>.返回參數中一年的第几周的寫法:
select week(current timestamp) form sysibm.sysdummy1;
在此我簡單地總結一下﹐實現某一功能可能會有很多種方法﹐在此就沒有全部列出﹐
歡迎大家繼續﹐以便和大家共享﹐共同探討﹐共同近步﹗(以下主要以oracle
8i,9i和db2 7.x為例)。
1.如何查看數据庫的版本的oracle和db2的寫法
oracle 可以這樣實現﹕
sql> connect system/manager124@test;
已連線.
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle9i enterprise edition release 9.2.0.1.0 - production
pl/sql release 9.2.0.1.0 - production
core 9.2.0.1.0 production
tns for 32-bit windows: version 9.2.0.1.0 - production
nlsrtl version 9.2.0.1.0 - production
db2 可以這樣實現﹕
在命令窗口執行 db2level
d: qllib\bin>db2level
db210851 instance "db2" uses db2 code release "sql07020" with level identifier
"03010105" and informational tokens "db2 v7.1.0.40","n010415" and "wr21254".
2.如何快速清空一個大表的oracle和db2的寫法
oracle 可以這樣實現﹕
sql>truncate table table_name;
db2 可以這樣實現﹕
alter table table_name active not logged initially with empty table;
3.如何查看表空間的使用狀況的oracle和db2的寫法
oracle 可以這樣實現﹕
select tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, 99.99) || % as
pct_free
from ( select tablespace_name,sum(bytes)/1024/1024 as sum_m from dba_data_files group by tablespace_name),
( select tablespace_name as fs_ts_name, max(bytes)/1024/1024 as max_m, count(blocks) as count_blocks,
sum(bytes/1024/1024) as sum_free_m from dba_free_space group by tablespace_name )
where tablespace_name = fs_ts_name
db2 可以這樣實現﹕
list tablespace containers for 你的表空間號 show detail;
4.如何從一時間點取出日期的各部分的常用的oracle和db2的寫法
oracle 可以這樣實現﹕
1>.取時間點的年份的寫法:
select to_char(sysdate,yyyy) from dual;
2>.取時間點的月份的寫法:
select to_char(sysdate,mm) from dual;
3>.取時間點的日的寫法:
select to_char(sysdate,dd) from dual;
4>.取時間點的時的寫法:
select to_char(sysdate,hh24) from dual;
5>.取時間點的分的寫法:
select to_char(sysdate,mi) from dual;
6>.取時間點的秒的寫法:
select to_char(sysdate,ss) from dual;
7>.取時間點的毫秒的寫法:(9i以上)
select substr(systimestamp,20,6) from dual;
8>.取時間點的日期的寫法:
select trunc(sysdate) from dual;
9>.取時間點的時間的寫法:
select to_char(sysdate,hh24:mi:ss) from dual;
10>.日期,時間形態變為字符形態
select to_char(sysdate) from dual;
11>.將字符串轉換成日期或時間形態:
select to_date(2003/08/01) from dual;
12>.返回參數的星期几的寫法:
select to_char(sysdate,d) from dual;
13>.返回參數一年中的第几天的寫法:
select to_char(sysdate,ddd) from dual;
14>.返回午夜和參數中指定的時間值之間的秒數的寫法:
select to_char(sysdate,sssss) from dual;
15>.返回參數中一年的第几周的寫法:
select to_char(sysdate,ww) from dual;
db2 可以這樣實現﹕
1>.取時間點的年份的寫法:
select year(current timestamp) from sysibm.sysdummy1;
2>.取時間點的月份的寫法:
select month(current timestamp) from sysibm.sysdummy1;
3>.取時間點的日的寫法:
select day(current timestamp) from sysibm.sysdummy1;
4>.取時間點的時的寫法:
select hour(current timestamp) from sysibm.sysdummy1;
5>.取時間點的分的寫法:
select minute(current timestamp) from sysibm.sysdummy1;
6>.取時間點的秒的寫法:
select second(current timestamp) from sysibm.sysdummy1;
7>.取時間點的毫秒的寫法:
select microsecond(current timestamp) from sysibm.sysdummy1;
8>.取時間點的日期的寫法:
select date(current timestamp) from sysibm.sysdummy1;
9>.取時間點的時間的寫法:
select time(current timestamp) from sysibm.sysdummy1;
10>.日期,時間形態變為字符形態:
select char(current date) from sysibm.sysdummy1;
select char(current time) from sysibm.sysdummy1;
select char(current date+12 hours) from sysibm.sysdummy1;
11>.將字符串轉換成日期或時間形態:
select timestamp(2002-10-20-12.00.00.000000) from sysibm.sysdummy1;
select timestamp(2002-10-20 12:00:00) from sysibm.sysdummy1;
select date(2002-10-20) from sysibm.sysdummy1;
select date(10/20/2002) from sysibm.sysdummy1;
select time(12:00:00) from sysibm.sysdummy1;
select time (12.00.00) from sysibm.sysdummy1;
12>.返回參數的星期几的寫法:
select dayname(current timestamp) from sysibm.sysdummy1;
select dayofweek(current timestamp) from sysibm.sysdummy1;
select dayofweek_iso(current timestamp) from sysibm.sysdummy1;
13>.返回參數一年中的第几天的寫法:
select dayofyear(current timestamp) from sysibm.sysdummy1;
14>.返回午夜和參數中指定的時間值之間的秒數的寫法:
select midnight_seconds(current timestamp) form sysibm.sysdummy1;
15>.返回參數中一年的第几周的寫法:
select week(current timestamp) form sysibm.sysdummy1;
