在DB2中如何???FOracle的相?P功能(二)
作者?sCCBZZP
在?F??的??用中大家可能??常??遇到在DB2中如何???FOracle的某些功能?o
在此我???蔚乜??Y一下?o???F某一功能可能??有很多?N方法?o在此就?]有全部列出?o
?g迎大家?^?m?o以便和大家共享?o共同探???o共同近步?u(以下主要以Oracle
8I,9I和DB2 7.X?槔?)。
1.如何查看?稻??斓陌姹镜?Oracle和DB2的??法
Oracle 可以?@?????F?s
SQL> connect system/manager124@test;
已?B??.
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 可以?@?????F?s
在命令窗口?绦? db2level
D:SQLLIBBIN>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 可以?@?????F?s
SQL>truncate table table_name;
DB2 可以?@?????F?s
alter table table_name active not logged initially with empty table;
3.如何查看表空?g的使用???r的Oracle和DB2的??法
Oracle 可以?@?????F?s
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 可以?@?????F?s
list tablespace containers for 你的表空?g?? show detail;
4.如何?囊??r?g?c取出日期的各部分的常用的Oracle和DB2的??法
Oracle 可以?@?????F?s
1>.取?r?g?c的年份的??法:
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
2>.取?r?g?c的月份的??法:
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
3>.取?r?g?c的日的??法:
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
4>.取?r?g?c的?r的??法:
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
5>.取?r?g?c的分的??法:
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
6>.取?r?g?c的秒的??法:
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
7>.取?r?g?c的毫秒的??法:(9I以上)
select SUBSTR(systimestamp,20,6) from dual;
8>.取?r?g?c的日期的??法:
SELECT TRUNC(SYSDATE) FROM DUAL;
9>.取?r?g?c的?r?g的??法:
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
10>.日期,?r?g形?B???樽址?形?B
SELECT TO_CHAR(SYSDATE) FROM DUAL;
11>.?⒆址?串?D?Q成日期或?r?g形?B:
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>.返回午夜和???抵兄付ǖ??r?g值之?g的秒?档???法:
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
15>.返回???抵幸荒甑牡诩钢艿???法:
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
DB2 可以?@?????F?s
1>.取?r?g?c的年份的??法:
SELECT YEAR(current timestamp) FROM SYSIBM.SYSDUMMY1;
2>.取?r?g?c的月份的??法:
SELECT MONTH(current timestamp) FROM SYSIBM.SYSDUMMY1;
3>.取?r?g?c的日的??法:
SELECT DAY(current timestamp) FROM SYSIBM.SYSDUMMY1;
4>.取?r?g?c的?r的??法:
SELECT HOUR(current timestamp) FROM SYSIBM.SYSDUMMY1;
5>.取?r?g?c的分的??法:
SELECT MINUTE(current timestamp) FROM SYSIBM.SYSDUMMY1;
6>.取?r?g?c的秒的??法:
SELECT SECOND(current timestamp) FROM SYSIBM.SYSDUMMY1;
7>.取?r?g?c的毫秒的??法:
SELECT MICROSECOND(current timestamp) FROM SYSIBM.SYSDUMMY1;
8>.取?r?g?c的日期的??法:
SELECT DATE(current timestamp) FROM SYSIBM.SYSDUMMY1;
9>.取?r?g?c的?r?g的??法:
SELECT TIME(current timestamp) FROM SYSIBM.SYSDUMMY1;
10>.日期,?r?g形?B???樽址?形?B:
SELECT char(current date) FROM SYSIBM.SYSDUMMY1;
SELECT char(current time) FROM SYSIBM.SYSDUMMY1;
SELECT char(current date+12 hours) FROM SYSIBM.SYSDUMMY1;
11>.?⒆址?串?D?Q成日期或?r?g形?B:
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>.返回午夜和???抵兄付ǖ??r?g值之?g的秒?档???法:
SELECT MIDNIGHT_SECONDS(current timestamp) FORM SYSIBM.SYSDUMMY1;
15>.返回???抵幸荒甑牡诩钢艿???法:
SELECT WEEK(current timestamp) FORM SYSIBM.SYSDUMMY1;
待?m...
|
