{"id":705,"date":"2008-09-08T18:53:57","date_gmt":"2008-09-08T10:53:57","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=705"},"modified":"2008-09-13T23:21:42","modified_gmt":"2008-09-13T15:21:42","slug":"oracle%e5%b8%b8%e8%a6%8b%e5%95%8f%e9%a1%8c%e9%9b%86%e4%ba%8c","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2008\/09\/08\/oracle%e5%b8%b8%e8%a6%8b%e5%95%8f%e9%a1%8c%e9%9b%86%e4%ba%8c\/","title":{"rendered":"Oracle\u5e38\u898b\u554f\u984c\u96c6(\u4e8c)"},"content":{"rendered":"


\n71. \u5167\u6838\u53c3\u6578\u7684\u61c9\u7528<\/strong><\/p>\n

shmmax<\/p>\n

\u542b\u7fa9\uff1a\u9019\u500b\u8a2d\u7f6e\u4e26\u4e0d\u6c7a\u5b9a\u7a76\u7adfOracle\u8cc7\u6599\u5eab\u6216\u8005\u4f5c\u696d\u7cfb\u7d71\u4f7f\u7528\u591a\u5c11\u5be6\u9ad4\u8a18\u61b6\u9ad4\uff0c\u53ea\u6c7a\u5b9a\u4e86\u6700\u591a\u53ef\u4ee5\u4f7f\u7528\u7684\u8a18\u61b6\u9ad4\u6578\u76ee\u3002\u9019\u500b\u8a2d\u7f6e\u4e5f\u4e0d\u5f71\u97ff\u4f5c\u696d\u7cfb\u7d71\u7684\u5167\u6838\u8cc7\u6e90\u3002<\/p>\n

\u8a2d\u7f6e\u65b9\u6cd5\uff1a0.5*\u5be6\u9ad4\u8a18\u61b6\u9ad4<\/p>\n

\u4f8b\u5b50\uff1aSet shmsys:shminfo_shmmax=10485760<\/p>\n

shmmin<\/p>\n

\u542b\u7fa9\uff1a\u5171\u7528\u8a18\u61b6\u9ad4\u7684\u6700\u5c0f\u5927\u5c0f\u3002<\/p>\n

\u8a2d\u7f6e\u65b9\u6cd5\uff1a\u4e00\u822c\u90fd\u8a2d\u7f6e\u6210\u72321\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet shmsys:shminfo_shmmin=1\uff1a<\/p>\n

shmmni<\/p>\n

\u542b\u7fa9\uff1a\u7cfb\u7d71\u4e2d\u5171\u7528\u8a18\u61b6\u9ad4\u6bb5\u7684\u6700\u5927\u500b\u6578\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet shmsys:shminfo_shmmni=100<\/p>\n

shmseg<\/p>\n

\u542b\u7fa9\uff1a\u6bcf\u500b\u7528\u6236\u9032\u7a0b\u53ef\u4ee5\u4f7f\u7528\u7684\u6700\u591a\u7684\u5171\u7528\u8a18\u61b6\u9ad4\u6bb5\u7684\u6578\u76ee\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet shmsys:shminfo_shmseg=20\uff1a<\/p>\n

semmni<\/p>\n

\u542b\u7fa9\uff1a\u7cfb\u7d71\u4e2dsemaphore identifierer\u7684\u6700\u5927\u500b\u6578\u3002<\/p>\n

\u8a2d\u7f6e\u65b9\u6cd5\uff1a\u628a\u9019\u500b\u8b8a\u6578\u7684\u503c\u8a2d\u7f6e\u7232\u9019\u500b\u7cfb\u7d71\u4e0a\u7684\u6240\u6709Oracle\u7684\u5be6\u4f8b\u7684init.ora\u4e2d\u7684\u6700\u5927\u7684\u90a3\u500bprocesses\u7684\u90a3\u500b\u503c\u52a010\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet semsys:seminfo_semmni=100<\/p>\n

semmns<\/p>\n

\u542b\u7fa9\uff1a\u7cfb\u7d71\u4e2demaphores\u7684\u6700\u5927\u500b\u6578\u3002<\/p>\n

\u8a2d\u7f6e\u65b9\u6cd5\uff1a\u9019\u500b\u503c\u53ef\u4ee5\u901a\u904e\u4ee5\u4e0b\u65b9\u5f0f\u8a08\u7b97\u5f97\u5230\uff1a\u5404\u500bOracle\u5be6\u4f8b\u7684initSID.ora\u88cf\u908a\u7684processes\u7684\u503c\u7684\u7e3d\u548c\uff08\u9664\u53bb\u6700\u5927\u7684Processes\u53c3\u6578\uff09\uff0b\u6700\u5927\u7684\u90a3\u500bProcesses\u00d72\uff0b10\u00d7Oracle\u5be6\u4f8b\u7684\u500b\u6578\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet semsys:seminfo_semmns=200<\/p>\n

semmsl:<\/p>\n

\u542b\u7fa9\uff1a\u4e00\u500bset\u4e2dsemaphore\u7684\u6700\u5927\u500b\u6578\u3002<\/p>\n

\u8a2d\u7f6e\u65b9\u6cd5\uff1a\u8a2d\u7f6e\u6210\u723210\uff0b\u6240\u6709Oracle\u5be6\u4f8b\u7684InitSID.ora\u4e2d\u6700\u5927\u7684Processes\u7684\u503c\u3002<\/p>\n

\u4f8b\u5b50\uff1aSet semsys:seminfo_semmsl=-200<\/p>\n

72. \u600e\u6a23\u67e5\u770b\u54ea\u4e9b\u7528\u6236\u64c1\u6709SYSDBA\u3001SYSOPER\u8a31\u53ef\u6b0a\uff1f<\/strong><\/p>\n

SQL>conn sys\/change_on_install<\/p>\n

SQL>select * from V_$PWFILE_USERS;<\/p>\n

73. \u5982\u4f55\u55ae\u7368\u5099\u4efd\u4e00\u500b\u6216\u591a\u500b\u8868\uff1f<\/strong><\/p>\n

exp \u7528\u6236\/\u5bc6\u78bc tables=(\u88681,\u2026,\u88682)<\/p>\n

74. \u5982\u4f55\u55ae\u7368\u5099\u4efd\u4e00\u500b\u6216\u591a\u500b\u7528\u6236\uff1f<\/strong><\/p>\n

exp system\/manager owner=(\u7528\u62361,\u7528\u62362,\u2026,\u7528\u6236n) file=\u5c0e\u51fa\u6587\u4ef6<\/p>\n

75. \u5982\u4f55\u5c0dCLOB\u6b04\u4f4d\u9032\u884c\u5168\u6587\u6aa2\u7d22\uff1f<\/strong><\/p>\n

SELECT * FROM A WHERE dbms_lob.instr(a.a,’K’,1,1)>0;<\/p>\n

76. \u5982\u4f55\u986f\u793a\u7576\u524d\u9023\u63a5\u7528\u6236<\/strong><\/p>\n

SHOW USER<\/p>\n

77. \u5982\u4f55\u67e5\u770b\u8cc7\u6599\u6a94\u6848\u653e\u7f6e\u7684\u8def\u5f91<\/strong><\/p>\n

col file_name format a50<\/p>\n

SQL> select tablespace_name,file_id,bytes\/1024\/1024,file_name from<\/p>\n

dba_data_files order by file_id;<\/p>\n

78. \u5982\u4f55\u67e5\u770b\u73fe\u6709\u56de\u6efe\u6bb5\u53ca\u5176\u72c0\u614b<\/strong><\/p>\n

SQL> col segment format a30<\/p>\n

SQL> SELECT<\/p>\n

SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM<\/p>\n

DBA_ROLLBACK_SEGS<\/p>\n

79. \u5982\u4f55\u6539\u8b8a\u4e00\u500b\u6b04\u4f4d\u521d\u59cb\u5b9a\u7fa9\u7684Check\u7bc4\u570d\uff1f<\/strong><\/p>\n

SQL> alter table xxx drop constraint constraint_name;<\/p>\n

\u4e4b\u5f8c\u518d\u5275\u5efa\u65b0\u7d04\u675f:<\/p>\n

SQL> alter table xxx add constraint constraint_name check();<\/p>\n

80. Oracle\u5e38\u7528\u7cfb\u7d71\u6587\u4ef6\u6709\u54ea\u4e9b\uff1f<\/strong><\/p>\n

\u901a\u904e\u4ee5\u4e0b\u8996\u5716\u986f\u793a\u9019\u4e9b\u6587\u4ef6\u8cc7\u8a0a\uff1av$database,v$datafile,v$logfile v$controlfile<\/p>\n

v$parameter;<\/p>\n

81. \u5167\u9023\u63a5INNER JOIN<\/strong><\/p>\n

Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no;<\/p>\n

82. \u5982\u4f55\u5916\u9023\u63a5<\/strong><\/p>\n

Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);<\/p>\n

Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;<\/p>\n

83. \u5982\u4f55\u57f7\u884c\u8173\u672cSQL\u6587\u4ef6<\/strong><\/p>\n

SQL>@$PATH\/filename.sql;<\/p>\n

84. \u5982\u4f55\u5feb\u901f\u6e05\u7a7a\u4e00\u500b\u5927\u8868<\/strong><\/p>\n

SQL>truncate table table_name;<\/p>\n

85. \u5982\u4f55\u67e5\u6709\u591a\u5c11\u500b\u6578\u64da\u5eab\u5be6\u4f8b<\/strong><\/p>\n

SQL>SELECT * FROM V$INSTANCE;<\/p>\n

86. \u5982\u4f55\u67e5\u8a62\u6578\u64da\u5eab\u6709\u591a\u5c11\u8868<\/strong><\/p>\n

SQL>select * from all_tables;<\/p>\n

87. \u5982\u4f55\u6e2c\u8a66SQL\u8a9e\u53e5\u57f7\u884c\u6240\u7528\u7684\u6642\u9593<\/strong><\/p>\n

SQL>set timing on ;<\/p>\n

SQL>select * from tablename;<\/p>\n

\u5927\u5bb6\u5728\u61c9\u7528ORACLE\u7684\u6642\u5019\u53ef\u80fd\u6703\u9047\u5230\u5f88\u591a\u770b\u8d77\u4f86\u4e0d\u96e3\u7684\u554f\u984c, \u7279\u5225\u5c0d\u65b0\u624b\u4f86\u8aaa, \u4eca\u5929\u6211\u7c21\u55ae\u628a\u5b83\u7e3d\u7d50\u4e00\u4e0b, \u767c\u5e03\u7d66\u5927\u5bb6,<\/p>\n

\u5e0c\u671b\u5c0d\u5927\u5bb6\u6709\u5e6b\u52a9! \u548c\u5927\u5bb6\u4e00\u8d77\u63a2\u8a0e, \u5171\u540c\u9032\u6b65!<\/p>\n

\u5c0dORACLE\u9ad8\u624b\u4f86\u8aaa\u662f\u4e0d\u7528\u770b\u7684.<\/p>\n

88. CHR()\u7684\u53cd\u51fd\u6578\u662f<\/strong><\/p>\n

ASCII()<\/p>\n

SELECT CHAR(65) FROM DUAL;<\/p>\n

SELECT ASCII(‘A’) FROM DUAL;<\/p>\n

89. \u5b57\u4e32\u7684\u9023\u63a5<\/strong><\/p>\n

SELECT CONCAT(COL1,COL2) FROM TABLE ;<\/p>\n

SELECT COL1||COL2 FROM TABLE ;<\/p>\n

90. \u600e\u9ebd\u628aselect\u51fa\u4f86\u7684\u7d50\u679c\u5c0e\u5230\u4e00\u500b\u6587\u5b57\u6a94\u6848\u4e2d\uff1f<\/strong><\/p>\n

SQL>SPOOL C:\\ABCD.TXT;<\/p>\n

SQL>select * from table;<\/p>\n

SQL >spool off;<\/p>\n

91. \u600e\u6a23\u4f30\u7b97SQL\u57f7\u884c\u7684I\/O\u6578<\/strong><\/p>\n

SQL>SET AUTOTRACE ON ;<\/p>\n

SQL>SELECT * FROM TABLE;<\/p>\n

OR<\/p>\n

SQL>SELECT * FROM v$filestat ;<\/p>\n

\u53ef\u4ee5\u67e5\u770bIO\u6578<\/p>\n

92. \u5982\u4f55\u5728sqlplus\u4e0b\u6539\u8b8a\u6b04\u4f4d\u5927\u5c0f<\/strong><\/p>\n

alter table table_name modify (field_name varchar2(100));<\/p>\n

\u6539\u5927\u884c\uff0c\u6539\u5c0f\u4e0d\u884c\uff08\u9664\u975e\u90fd\u662f\u7a7a\u7684\uff09<\/p>\n

93. \u5982\u4f55\u67e5\u8a62\u67d0\u5929\u7684\u8cc7\u6599<\/strong><\/p>\n

select * from table_name where<\/p>\n

trunc(\u65e5\u671f\u6b04\u4f4d)\uff1dto_date(‘2003-05-02′,’yyyy-mm-dd’);<\/p>\n

94. sql \u8a9e\u53e5\u5982\u4f55\u63d2\u5165\u5168\u5e74\u65e5\u671f\uff1f<\/strong><\/p>\n

create table BSYEAR (d date);<\/p>\n

insert into BSYEAR<\/p>\n

select to_date(‘20030101′,’yyyymmdd’)+rownum-1<\/p>\n

from all_objects<\/p>\n

where rownum <= to_char(to_date(‘20031231′,’yyyymmdd’),’ddd’);<\/p>\n

95. \u5982\u679c\u4fee\u6539\u8868\u540d<\/strong><\/p>\n

alter table old_table_name rename to new_table_name;<\/p>\n

96. \u5982\u4f55\u53d6\u5f97\u547d\u4ee4\u7684\u8fd4\u56de\u72c0\u614b\u503c\uff1f<\/strong><\/p>\n

sqlcode=0<\/p>\n

97. \u5982\u4f55\u77e5\u9053\u7528\u6236\u64c1\u6709\u7684\u8a31\u53ef\u6b0a<\/strong><\/p>\n

SELECT * FROM dba_sys_privs ;<\/p>\n

98. \u5f9e\u7db2\u4e0a\u4e0b\u8f09\u7684ORACLE9I\u8207\u5e02\u5834\u4e0a\u8ce3\u7684\u6a19\u6e96\u7248\u6709\u4ec0\u9ebd\u5340\u5225\uff1f<\/strong><\/p>\n

\u5f9e\u529f\u80fd\u4e0a\u8aaa\u6c92\u6709\u5340\u5225\uff0c\u53ea\u4e0d\u904eoracle\u516c\u53f8\u6709\u660e\u6587\u898f\u5b9a\uff1b\u5f9e\u7db2\u7ad9\u4e0a\u4e0b\u8f09\u7684oracle\u7523\u54c1\u4e0d\u5f97\u7528\u65bc \u5546\u696d\u7528\u9014\uff0c\u5426\u5247\u4fb5\u6b0a\u3002<\/p>\n

99. \u600e\u6a23\u5224\u65b7\u8cc7\u6599\u5eab\u662f\u904b\u884c\u5728\u6b78\u6a94\u6a21\u5f0f\u4e0b\u9084\u662f\u904b\u884c\u5728\u975e\u6b78\u6a94\u6a21\u5f0f\u4e0b\uff1f<\/strong><\/p>\n

\u9032\u5165dbastudio\uff0c\u6b77\u7a0b–\u3009\u8cc7\u6599\u5eab—\u3009\u6b78\u6a94\u67e5\u770b\u3002<\/p>\n

100. sql>startup pfile\u548cifile,spfiled\u6709\u4ec0\u9ebd\u5340\u5225\uff1f<\/strong><\/p>\n

pfile\u5c31\u662fOracle\u50b3\u7d71\u7684\u521d\u59cb\u5316\u53c3\u6578\u6587\u4ef6\uff0c\u6587\u672c\u683c\u5f0f\u7684\u3002<\/p>\n

ifile\u985e\u4f3c\u65bcc\u8a9e\u8a00\u88cf\u7684include\uff0c\u7528\u65bc\u628a\u53e6\u4e00\u500b\u6587\u4ef6\u5f15\u5165<\/p>\n

spfile\u662f9i\u88cf\u65b0\u589e\u7684\u4e26\u4e14\u662f\u9ed8\u8a8d\u7684\u53c3\u6578\u6587\u4ef6\uff0c\u4e8c\u9032\u4f4d\u683c\u5f0f<\/p>\n

startup\u5f8c\u61c9\u8a72\u53ea\u53ef\u63a5pfile<\/p>\n

101. \u5982\u4f55\u641c\u7d22\u51fa\u524dN\u689d\u8a18\u9304\uff1f<\/strong><\/p>\n

SELECT * FROM empLOYEE WHERE ROWNUM < n<\/p>\n

ORDER BY empno;<\/p>\n

102. \u5982\u4f55\u77e5\u9053\u6a5f\u5668\u4e0a\u7684Oracle\u652f\u63f4\u591a\u5c11\u4e26\u767c\u7528\u6236\u6578<\/strong><\/p>\n

SQL>conn internal ;<\/p>\n

SQL>show parameter processes ;<\/p>\n

103. db_block_size\u53ef\u4ee5\u4fee\u6539\u55ce<\/strong><\/p>\n

\u4e00\u822c\u4e0d\u53ef\u4ee5\ufe50\u4e0d\u5efa\u8b70\u9019\u6a23\u505a\u7684\u3002<\/p>\n

104. \u5982\u4f55\u7d71\u8a08\u5169\u500b\u8868\u7684\u8a18\u9304\u7e3d\u6578<\/strong><\/p>\n

select (select count(id) from aa)+(select count(id) from bb) \u7e3d\u6578<\/p>\n

from dual;<\/p>\n

105. \u600e\u6a23\u7528Sql\u8a9e\u53e5\u5be6\u73fe\u67e5\u627e\u4e00\u5217\u4e2d\u7b2cN\u5927\u503c\uff1f<\/strong><\/p>\n

select * from<\/p>\n

(select t.*,dense_rank() over (order by sal) rank from employee)<\/p>\n

where rank = N;<\/p>\n

106. \u5982\u4f55\u5728\u7d66\u73fe\u6709\u7684\u65e5\u671f\u52a0\u4e0a2\u5e74\uff1f<\/strong><\/p>\n

select add_months(sysdate,24) from dual;<\/p>\n

107. USED_UBLK\u7232\u8ca0\u503c\u8868\u793a\u4ec0\u9ebd\u610f\u601d<\/strong><\/p>\n

It is “harmless”.<\/p>\n

108. Connect string\u662f\u6307\u4ec0\u9ebd<\/strong><\/p>\n

\u61c9\u8a72\u662ftnsnames.ora\u4e2d\u7684\u670d\u52d9\u540d\u5f8c\u9762\u7684\u5167\u5bb9<\/p>\n

109. \u600e\u6a23\u64f4\u5927REDO LOG\u7684\u5927\u5c0f\uff1f<\/strong><\/p>\n

\u5efa\u7acb\u4e00\u500b\u81e8\u6642\u7684redolog\u7d44\uff0c\u7136\u5f8c\u5207\u63db\u65e5\u8a8c\uff0c\u522a\u9664\u4ee5\u524d\u7684\u65e5\u8a8c\uff0c\u5efa\u7acb\u65b0\u7684\u65e5\u8a8c\u3002<\/p>\n

110. tablespace \u662f\u5426\u4e0d\u80fd\u5927\u65bc4G<\/strong><\/p>\n

\u6c92\u6709\u9650\u5236.<\/p>\n

111. \u8fd4\u56de\u5927\u65bc\u7b49\u65bcN\u7684\u6700\u5c0f\u6574\u6578\u503c<\/strong><\/p>\n

SELECT CEIL(N) FROM DUAL;<\/p>\n

112. \u8fd4\u56de\u5c0f\u65bc\u7b49\u65bcN\u7684\u6700\u5c0f\u6574\u6578\u503c<\/strong><\/p>\n

SELECT FLOOR(N) FROM DUAL;<\/p>\n

113. \u8fd4\u56de\u7576\u524d\u6708\u7684\u6700\u5f8c\u4e00\u5929<\/strong><\/p>\n

SELECT LAST_DAY(SYSDATE) FROM DUAL;<\/p>\n

114. \u5982\u4f55\u4e0d\u540c\u7528\u6236\u9593\u6578\u64da\u5c0e\u5165<\/strong><\/p>\n

IMP SYSTEM\/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW<\/p>\n

ROWS=Y INDEXES=Y ;<\/p>\n

115. \u5982\u4f55\u627e\u8cc7\u6599\u5eab\u8868\u7684\u4e3b\u9375\u6b04\u4f4d\u7684\u540d\u7a31<\/strong><\/p>\n

SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE=’P’ and<\/p>\n

table_name=’TABLE_NAME’;<\/p>\n

116. \u5169\u500b\u7d50\u679c\u96c6\u4e92\u52a0\u7684\u51fd\u6578<\/strong><\/p>\n

SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;<\/p>\n

SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;<\/p>\n

SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;<\/p>\n

117. \u5169\u500b\u7d50\u679c\u96c6\u4e92\u6e1b\u7684\u51fd\u6578<\/strong><\/p>\n

SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;<\/p>\n

118. \u5982\u4f55\u914d\u7f6eSequence<\/strong><\/p>\n

\u5efasequence seq_custid<\/p>\n

create sequence seq_custid start 1 incrememt by 1;<\/p>\n

\u5efa\u8868\u6642:<\/p>\n

create table cust<\/p>\n

{ cust_id smallint not null,<\/p>\n

…}<\/p>\n

insert \u6642:<\/p>\n

insert into table cust<\/p>\n

values( seq_cust.nextval, …)<\/p>\n

\u65e5\u671f\u7684\u5404\u90e8\u5206\u7684\u5e38\u7528\u7684\u7684\u5beb\u6cd5<\/strong><\/p>\n

119.\u53d6\u6642\u9593\u9ede\u7684\u5e74\u4efd\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’YYYY’) FROM DUAL;<\/p>\n

120.\u53d6\u6642\u9593\u9ede\u7684\u6708\u4efd\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’MM’) FROM DUAL;<\/p>\n

121.\u53d6\u6642\u9593\u9ede\u7684\u65e5\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’DD’) FROM DUAL;<\/p>\n

122.\u53d6\u6642\u9593\u9ede\u7684\u6642\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’HH24′) FROM DUAL;<\/p>\n

123.\u53d6\u6642\u9593\u9ede\u7684\u5206\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’MI’) FROM DUAL;<\/p>\n

124.\u53d6\u6642\u9593\u9ede\u7684\u79d2\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’SS’) FROM DUAL;<\/p>\n

125.\u53d6\u6642\u9593\u9ede\u7684\u65e5\u671f\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TRUNC(SYSDATE) FROM DUAL;<\/p>\n

126.\u53d6\u6642\u9593\u9ede\u7684\u6642\u9593\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’HH24:MI:SS’) FROM DUAL;<\/p>\n

127.\u65e5\u671f,\u6642\u9593\u5f62\u614b\u8b8a\u70ba\u5b57\u5143\u5f62\u614b<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE) FROM DUAL;<\/p>\n

128.\u5c07\u5b57\u4e32\u8f49\u63db\u6210\u65e5\u671f\u6216\u6642\u9593\u5f62\u614b:<\/strong><\/p>\n

SELECT TO_DATE(‘2003\/08\/01’) FROM DUAL;<\/p>\n

129.\u8fd4\u56de\u53c3\u6578\u7684\u661f\u671f\u5e7e\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’D’) FROM DUAL;<\/p>\n

130.\u8fd4\u56de\u53c3\u6578\u4e00\u5e74\u4e2d\u7684\u7b2c\u5e7e\u5929\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’DDD’) FROM DUAL;<\/p>\n

131.\u8fd4\u56de\u5348\u591c\u548c\u53c3\u6578\u4e2d\u6307\u5b9a\u7684\u6642\u9593\u503c\u4e4b\u9593\u7684\u79d2\u6578\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’SSSSS’) FROM DUAL;<\/p>\n

132.\u8fd4\u56de\u53c3\u6578\u4e2d\u4e00\u5e74\u7684\u7b2c\u5e7e\u5468\u7684\u5beb\u6cd5:<\/strong><\/p>\n

SELECT TO_CHAR(SYSDATE,’WW’) FROM DUAL;<\/p>\n

\u865b\u64ec\u6b04\u4f4d<\/strong><\/p>\n

133. CURRVAL \u548c nextval<\/strong><\/p>\n

\u7232\u8868\u5275\u5efa\u5e8f\u5217<\/p>\n

CREATE SEQUENCE EMPSEQ … ;<\/p>\n

SELECT empseq.currval FROM DUAL ;<\/p>\n

\u81ea\u52d5\u63d2\u5165\u5e8f\u5217\u7684\u6578\u503c<\/p>\n

INSERT INTO emp<\/p>\n

VALUES (empseq.nextval, ‘LEWIS’, ‘CLERK’,<\/p>\n

7902, SYSDATE, 1200, NULL, 20) ;<\/p>\n

134. ROWNUM<\/strong><\/p>\n

\u6309\u8a2d\u5b9a\u6392\u5e8f\u7684\u884c\u7684\u5e8f\u865f<\/p>\n

SELECT * FROM emp WHERE ROWNUM < 10 ;<\/p>\n

135. ROWID<\/strong><\/p>\n

\u8fd4\u56de\u884c\u7684\u7269\u7406\u4f4d\u5740<\/p>\n

SELECT ROWID, ename FROM emp WHERE deptno = 20 ;<\/p>\n

136. \u5c07N\u79d2\u8f49\u63db\u7232\u6642\u5206\u79d2\u683c\u5f0f\uff1f<\/strong><\/p>\n

set serverout on<\/p>\n

declare<\/p>\n

N number := 1000000;<\/p>\n

ret varchar2(100);<\/p>\n

begin<\/p>\n

ret := trunc(n\/3600) || ‘\u5c0f\u6642’ ||<\/p>\n

to_char(to_date(mod(n,3600),’sssss’),’fmmi”\u5206 “ss”\u79d2”‘) ;<\/p>\n

dbms_output.put_line(ret);<\/p>\n

end;<\/p>\n

137. \u5982\u4f55\u67e5\u8a62\u505a\u6bd4\u8f03\u5927\u7684\u6392\u5e8f\u7684\u9032\u7a0b\uff1f<\/strong><\/p>\n

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid,<\/p>\n

a.serial#,<\/p>\n

a.username, a.osuser, a.status<\/p>\n

FROM v$session a,v$sort_usage b<\/p>\n

WHERE a.saddr = b.session_addr<\/p>\n

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;<\/p>\n

138. \u5982\u4f55\u67e5\u8a62\u505a\u6bd4\u8f03\u5927\u7684\u6392\u5e8f\u7684\u9032\u7a0b\u7684SQL\u8a9e\u53e5\uff1f<\/strong><\/p>\n

select \/*+ ORDERED *\/ sql_text from v$sqltext a<\/p>\n

where a.hash_value = (<\/p>\n

select sql_hash_value from v$session b<\/p>\n

where b.sid = &sid and b.serial# = &serial)<\/p>\n

order by piece asc ;<\/p>\n

139. \u5982\u4f55\u67e5\u627e\u91cd\u5fa9\u8a18\u9304\uff1f<\/strong><\/p>\n

SELECT * FROM TABLE_NAME<\/p>\n

WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D<\/p>\n

WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);<\/p>\n

140. \u5982\u4f55\u522a\u9664\u91cd\u5fa9\u8a18\u9304\uff1f<\/strong><\/p>\n

DELETE FROM TABLE_NAME<\/p>\n

WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D<\/p>\n

WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);<\/p>\n

141. \u5982\u4f55\u5feb\u901f\u7de8\u8b6f\u6240\u6709\u8996\u5716\uff1f<\/strong><\/p>\n

SQL >SPOOL VIEW1.SQL<\/p>\n

SQL >SELECT \u2018ALTER VIEW \u2018||TNAME||\u2019<\/p>\n

COMPILE;\u2019 FROM TAB;<\/p>\n

SQL >SPOOL OFF<\/p>\n

\u7136\u5f8c\u57f7\u884cVIEW1.SQL\u5373\u53ef\u3002<\/p>\n

SQL >@VIEW1.SQL;<\/p>\n

142. ORA-01555 SNAPSHOT TOO OLD\u7684\u89e3\u6c7a\u8fa6\u6cd5<\/strong><\/p>\n

\u589e\u52a0MINEXTENTS\u7684\u503c\uff0c\u589e\u52a0\u5340\u7684\u5927\u5c0f\uff0c\u8a2d\u7f6e\u4e00\u500b\u9ad8\u7684OPTIMAL\u503c\u3002<\/p>\n

143. \u4e8b\u52d9\u8981\u6c42\u7684\u56de\u6efe\u6bb5\u7a7a\u9593\u4e0d\u5920\uff0c\u8868\u73fe\u7232\u8868\u7a7a\u9593\u7528\u6eff\uff08ORA-01560\u932f\u8aa4\uff09\uff0c\u56de\u6efe\u6bb5\u64f4\u5c55\u5230\u9054\u53c3\u6578<\/strong><\/p>\n

MAXEXTENTS\u7684\u503c\uff08ORA-01628\uff09\u7684\u89e3\u6c7a\u8fa6\u6cd5.<\/p>\n

\u5411\u56de\u6efe\u6bb5\u8868\u7a7a\u9593\u6dfb\u52a0\u6587\u4ef6\u6216\u4f7f\u5df2\u6709\u7684\u6587\u4ef6\u8b8a\u5927\uff1b\u589e\u52a0MAXEXTENTS\u7684\u503c\u3002<\/p>\n

144. \u5982\u4f55\u52a0\u5bc6ORACLE\u7684\u5b58\u5132\u904e\u7a0b\uff1f<\/strong><\/p>\n

\u4e0b\u5217\u5b58\u5132\u904e\u7a0b\u5167\u5bb9\u653e\u5728AA.SQL\u6587\u4ef6\u4e2d<\/p>\n

create or replace procedure testCCB(i in number) as<\/p>\n

begin<\/p>\n

dbms_output.put_line(‘\u8f38\u5165\u53c3\u6578\u662f’||to_char(i));<\/p>\n

end;<\/p>\n

SQL>wrap iname=a.sql;<\/p>\n

PL\/SQL Wrapper: Release 8.1.7.0.0 – Production on Tue Nov 27<\/p>\n

22:26:48 2001<\/p>\n

Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.<\/p>\n

Processing AA.sql to AA.plb<\/p>\n

\u904b\u884cAA.plb<\/p>\n

SQL> @AA.plb ;<\/p>\n

145. \u5982\u4f55\u76e3\u63a7\u4e8b\u4f8b\u7684\u7b49\u5f85\uff1f<\/strong><\/p>\n

select event,sum(decode(wait_Time,0,0,1)) “Prev”,<\/p>\n

sum(decode(wait_Time,0,1,0)) “Curr”,count(*) “Tot”<\/p>\n

from v$session_Wait<\/p>\n

group by event order by 4;<\/p>\n

146. \u5982\u4f55\u56de\u6efe\u6bb5\u7684\u722d\u7528\u60c5\u6cc1\uff1f<\/strong><\/p>\n

select name, waits, gets, waits\/gets “Ratio”<\/p>\n

from v$rollstat C, v$rollname D<\/p>\n

where C.usn = D.usn;<\/p>\n

147. \u5982\u4f55\u76e3\u63a7\u8868\u7a7a\u9593\u7684 I\/O \u6bd4\u4f8b\uff1f<\/strong><\/p>\n

select B.tablespace_name name,B.file_name “file”,A.phyrds pyr,<\/p>\n

A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw<\/p>\n

from v$filestat A, dba_data_files B<\/p>\n

where A.file# = B.file_id<\/p>\n

order by B.tablespace_name;<\/p>\n

148. \u5982\u4f55\u76e3\u63a7\u6587\u4ef6\u7cfb\u7d71\u7684 I\/O \u6bd4\u4f8b\uff1f<\/strong><\/p>\n

select substr(C.file#,1,2) “#”, substr(C.name,1,30) “Name”,<\/p>\n

C.status, C.bytes, D.phyrds, D.phywrts<\/p>\n

from v$datafile C, v$filestat D<\/p>\n

where C.file# = D.file#;<\/p>\n

149. \u5982\u4f55\u5728\u67d0\u500b\u7528\u6236\u4e0b\u627e\u6240\u6709\u7684\u7d22\u5f15\uff1f<\/strong><\/p>\n

select user_indexes.table_name,<\/p>\n

user_indexes.index_name,uniqueness, column_name<\/p>\n

from user_ind_columns, user_indexes<\/p>\n

where user_ind_columns.index_name = user_indexes.index_name<\/p>\n

and user_ind_columns.table_name = user_indexes.table_name<\/p>\n

order by user_indexes.table_type, user_indexes.table_name,<\/p>\n

user_indexes.index_name, column_position;<\/p>\n

150. \u5982\u4f55\u76e3\u63a7 SGA \u7684\u547d\u4e2d\u7387\uff1f<\/strong><\/p>\n

select a.value + b.value “logical_reads”, c.value “phys_reads”,<\/p>\n

round(100 * ((a.value+b.value)-c.value) \/ (a.value+b.value))<\/p>\n

“BUFFER HIT RATIO”<\/p>\n

from v$sysstat a, v$sysstat b, v$sysstat c<\/p>\n

where a.statistic# = 38 and b.statistic# = 39<\/p>\n

and c.statistic# = 40;<\/p>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[41],"tags":[],"class_list":["post-705","post","type-post","status-publish","format-standard","hentry","category-oracle","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/705","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=705"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/705\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}