ORACLE 常用的SQL文法和資料對象

ORACLE 常用的SQL文法和資料對象

一.資料控制語句 (DML) 部分

1.INSERT (往資料表裡插入記錄的語句)

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);

INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名;

字元串類型的字段值必須用單引号括起來, 例如: ’GOOD DAY’

如果字段值裡包含單引号’ 需要進行字元串轉換, 我們把它替換成兩個單引号''.

字元串類型的字段值超過定義的長度會出錯, 最好在插入前進行長度校驗.

日期字段的字段值可以用目前資料庫的系統時間SYSDATE, 精确到秒

或者用字元串轉換成日期型函數TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)

TO_DATE()還有很多種日期格式, 可以參看ORACLE DOC.

年-月-日 小時:分鐘:秒 的格式YYYY-MM-DD HH24:MI:SS

INSERT時最大可操作的字元串長度小于等于4000個單位元組, 如果要插入更長的字元串, 請考慮字段用CLOB類型,

方法借用ORACLE裡自帶的DBMS_LOB程式包.

INSERT時如果要用到從1開始自動增長的序列号, 應該先建立一個序列号

CREATE SEQUENCE 序列号的名稱 (最好是表名+序列号标記) INCREMENT BY 1 START WITH 1

MAXVALUE 99999 CYCLE NOCACHE;

其中最大的值按字段的長度來定, 如果定義的自動增長的序列号 NUMBER(6) , 最大值為999999

INSERT 語句插入這個字段值為: 序列号的名稱.NEXTVAL

2.DELETE (删除資料表裡記錄的語句)

DELETE FROM表名 WHERE 條件;

注意：删除記錄并不能釋放ORACLE裡被占用的資料塊表空間. 它隻把那些被删除的資料塊标成unused.

如果确實要删除一個大表裡的全部記錄, 可以用 TRUNCATE 指令, 它可以釋放占用的資料塊表空間

TRUNCATE TABLE 表名;

此操作不可回退.truncate table

3.UPDATE (修改資料表裡記錄的語句)

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 條件;

如果修改的值N沒有指派或定義時, 将把原來的記錄内容清為NULL, 最好在修改前進行非空校驗;

值N超過定義的長度會出錯, 最好在插入前進行長度校驗..

注意事項:

A. 以上SQL語句對表都加上了行級鎖,

确認完成後, 必須加上事物處理結束的指令 COMMIT 才能正式生效,

否則改變不一定寫入資料庫裡.

如果想撤回這些操作, 可以用指令 ROLLBACK 複原.

B. 在運作INSERT, DELETE 和 UPDATE 語句前最好估算一下可能操作的記錄範圍,

應該把它限定在較小 (一萬條記錄) 範圍内,. 否則ORACLE處理這個事物用到很大的回退段.

程式響應慢甚至失去響應. 如果記錄數上十萬以上這些操作, 可以把這些SQL語句分段分次完成,

其間加上COMMIT 确認事物處理.

二.資料定義 (DDL) 部分

1.CREATE (建立表, 索引, 視圖, 同義詞, 過程, 函數, 資料庫連結等)

ORACLE常用的字段類型有

CHAR 固定長度的字元串

VARCHAR2 可變長度的字元串

NUMBER(M,N) 數字型M是位數總長度, N是小數的長度

DATE 日期類型

建立表時要把較小的不為空的字段放在前面, 可能為空的字段放在後面

建立表時可以用中文的字段名, 但最好還是用英文的字段名

建立表時可以給字段加上預設值, 例如 DEFAULT SYSDATE

這樣每次插入和修改時, 不用程式操作這個字段都能得到動作的時間

建立表時可以給字段加上限制條件

例如 不允許重複 UNIQUE, 關鍵字 PRIMARY KEY

2.ALTER (改變表, 索引, 視圖等)

改變表的名稱

ALTER TABLE 表名1 TO 表名2;

在表的後面增加一個字段

ALTER TABLE表名 ADD 字段名 字段名描述;

修改表裡字段的定義描述

ALTER TABLE表名 MODIFY字段名 字段名描述;

給表裡的字段加上限制條件

ALTER TABLE 表名 ADD CONSTRAINT 限制名 PRIMARY KEY (字段名);

ALTER TABLE 表名 ADD CONSTRAINT 限制名 UNIQUE (字段名);

把表放在或取出資料庫的記憶體區

ALTER TABLE 表名 CACHE;

ALTER TABLE 表名 NOCACHE;

3.DROP (删除表, 索引, 視圖, 同義詞, 過程, 函數, 資料庫連結等)

删除表和它所有的限制條件

DROP TABLE 表名 CASCADE CONSTRAINTS;

4.TRUNCATE (清空表裡的所有記錄, 保留表的結構)

TRUNCATE 表名;

三.查詢語句 (SELECT) 部分

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 條件;

字段名可以帶入函數

例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),

TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')

NVL(EXPR1, EXPR2)函數

解釋:

IF EXPR1=NULL

RETURN EXPR2

ELSE

RETURN EXPR1

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函數

解釋:

IF AA=V1 THEN RETURN R1

IF AA=V2 THEN RETURN R2

..…

ELSE

RETURN NULL

LPAD(char1,n,char2)函數

解釋:

字元char1按制定的位數n顯示，不足的位數用char2字元串替換左邊的空位

字段名之間可以進行算術運算

例如: (字段名1*字段名1)/3

查詢語句可以嵌套

例如: SELECT …… FROM

(SELECT …… FROM表名1, [表名2, ……] WHERE 條件) WHERE 條件2;

兩個查詢語句的結果可以做集合操作

例如: 并集UNION(去掉重複記錄), 并集UNION ALL(不去掉重複記錄), 差集MINUS, 交集INTERSECT

分組查詢

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1

[HAVING 條件] ;

兩個以上表之間的連接配接查詢

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE

表名1.字段名 = 表名2. 字段名 [ AND ……] ;

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE

表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;

有(+)号的字段位置自動補空值

查詢結果集的排序操作, 預設的排序是升序ASC, 降序是DESC

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]

ORDER BY字段名1, 字段名2 DESC;

字元串模糊比較的方法

INSTR(字段名, ‘字元串’)>0

字段名 LIKE ‘字元串%’ [‘%字元串%’]

每個表都有一個隐含的字段ROWID, 它标記着記錄的唯一性.

四.ORACLE裡常用的資料對象 (SCHEMA)

1.索引 (INDEX)

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );

ALTER INDEX 索引名 REBUILD;

一個表的索引最好不要超過三個 (特殊的大表除外), 最好用單字段索引, 結合SQL語句的分析執行情況,

也可以建立多字段的組合索引和基于函數的索引

ORACLE8.1.7字元串可以索引的最大長度為1578 單位元組

ORACLE8.0.6字元串可以索引的最大長度為758 單位元組

2.視圖 (VIEW)

CREATE VIEW 視圖名AS SELECT …. FROM …..;

ALTER VIEW視圖名 COMPILE;

視圖僅是一個SQL查詢語句, 它可以把表之間複雜的關系簡潔化.

3.同義詞 (SYNONMY)

CREATE SYNONYM同義詞名FOR 表名;

CREATE SYNONYM同義詞名FOR 表名@資料庫連結名;

4.資料庫連結 (DATABASE LINK)

CREATE DATABASE LINK資料庫連結名CONNECT TO 使用者名 IDENTIFIED BY 密碼 USING ‘資料庫連接配接字元串’;

資料庫連接配接字元串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA裡定義.

資料庫參數global_name=true時要求資料庫連結名稱跟遠端資料庫名稱一樣

資料庫全局名稱可以用以下指令查出

SELECT * FROM GLOBAL_NAME;

查詢遠端資料庫裡的表

SELECT …… FROM 表名@資料庫連結名;

五.權限管理 (DCL) 語句

1.GRANT 賦于權限

常用的系統權限集合有以下三個:

CONNECT(基本的連接配接), RESOURCE(程式開發), DBA(資料庫管理)

常用的資料對象權限有以下五個:

ALL ON 資料對象名, SELECT ON 資料對象名, UPDATE ON 資料對象名,

DELETE ON 資料對象名, INSERT ON 資料對象名, ALTER ON 資料對象名

GRANT CONNECT, RESOURCE TO 使用者名;

GRANT SELECT ON 表名 TO 使用者名;

GRANT SELECT, INSERT, DELETE ON表名 TO 使用者名1, 使用者名2;

2.REVOKE 回收權限

REVOKE CONNECT, RESOURCE FROM 使用者名;

REVOKE SELECT ON 表名 FROM 使用者名;

REVOKE SELECT, INSERT, DELETE ON表名 FROM 使用者名1, 使用者名2;

查詢資料庫中第63号錯誤：

select orgaddr,destaddr from sm_histable0116 where error_code='63';

查詢資料庫中開戶使用者最大送出和最大下發數： select MSISDN,TCOS,OCOS from ms_usertable；

查詢資料庫中各種錯誤代碼的總和：

select error_code,count(*) from sm_histable0513 group by error_code order

by error_code;

查詢報表資料庫中話單統計種類查詢。

select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111

select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype

oracle常用SQL語句

1、連接配接SQL*Plus system/manager

2、顯示目前連接配接使用者SQL> show user

3、檢視系統擁有哪些使用者SQL> select * from all_users;

4、建立使用者并授權SQL> create user a identified by a;（預設建在SYSTEM表空間下）SQL> grant connect,resource to a;

5、連接配接到新使用者SQL> conn a/a

6、查詢目前使用者下所有對象SQL> select * from tab;

7、建立第一個表SQL> create table a(a number);

8、查詢表結構SQL> desc a

9、插入新記錄SQL> insert into a values(1);

10、查詢記錄SQL> select * from a;

11、更改記錄SQL> update a set a=2;

12、删除記錄SQL> delete from a;

13、復原SQL> roll;SQL> rollback;

14、送出SQL> commit;

---------------------------------------------------------------

----------------------------------------------------------------

使用者授權:GRANT ALTER ANY INDEX TO "user_id "GRANT "dba " TO "user_id ";ALTER USER "user_id " DEFAULT ROLE ALL建立使用者:CREATE USER "user_id " PROFILE "DEFAULT " IDENTIFIED BY " DEFAULT TABLESPACE "USERS " TEMPORARY TABLESPACE "TEMP " ACCOUNT UNLOCK;GRANT "CONNECT " TO "user_id ";使用者密碼設定:ALTER USER "CMSDB " IDENTIFIED BY "pass_word "表空間建立:CREATE TABLESPACE "table_space " LOGGING DATAFILE 'C:\ORACLE\ORADATA\dbs\table_space.ora' SIZE 5M

------------------------------------------------------------------------

1、檢視目前所有對象

SQL > select * from tab;

2、建一個和a表結構一樣的空表

SQL > create table b as select * from a where 1=2;

SQL > create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

3、察看資料庫的大小，和空間使用情況

SQL > col tablespace format a20SQL > select b.file_id　　檔案ID,　　b.tablespace_name　　表空間,　　b.file_name　　　　　實體檔案名,　　b.bytes　　　　　　　總位元組數,　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,　　sum(nvl(a.bytes,0))　　　　　　　　剩餘,　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩餘百分比　　from dba_free_space a,dba_data_files b　　where a.file_id=b.file_id　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes　　order by b.tablespace_name　　/　　dba_free_space --表空間剩餘空間狀況　　dba_data_files --資料檔案空間占用情況 4、檢視現有復原段及其狀态

SQL > col segment format a30SQL > SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、檢視資料檔案放置的路徑

SQL > col file_name format a50SQL > select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6、顯示目前連接配接使用者

SQL > show user

7、把SQL*Plus當電腦

SQL > select 100*20 from dual;

8、連接配接字元串

SQL > select 列1 | |列2 from 表1;SQL > select concat(列1,列2) from 表1;

9、查詢目前日期

SQL > select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

10、使用者間複制資料

SQL > copy from user1 to user2 create table2 using select * from table1;

11、視圖中不能使用order by，但可用group by代替來達到排序目的

SQL > create view a as select b1,b2 from b group by b1,b2;

12、通過授權的方式來建立使用者

SQL > grant connect,resource to test identified by test;

SQL > conn test/test

13、查出目前使用者所有表名。

select unique tname from col;

-----------------------------------------------------------------------

alter table alist_table add address varchar2(100);

alter table alist_table modify address varchar2(80);

create table alist_table_copy as select ID,NAME,PHONE,EMAIL,QQ as QQ2, ADDRESS from alist_table;

drop table alist_table;rename alist_table_copy to alist_table

空值處理有時要求列值不能為空create table dept (deptno number(2) not null, dname char(14), loc char(13));

在基表中增加一列alter table deptadd (headcnt number(3));

修改已有列屬性alter table deptmodify dname char(20);注：隻有當某列所有值都為空時，才能減小其列值寬度。隻有當某列所有值都為空時，才能改變其列值類型。隻有當某列所有值都為不空時，才能定義該列為not null。例：alter table dept modify (loc char(12));alter table dept modify loc char(12);alter table dept modify (dname char(13),loc char(12));

查找未斷連接配接select process,osuser,username,machine,logon_time ,sql_textfrom v$session a,v$sqltext b where a.sql_address=b.address;

-----------------------------------------------------------------1.以USER_開始的資料字典視圖包含目前使用者所擁有的資訊, 查詢目前使用者所擁有的表資訊:select * from user_tables;2.以ALL_開始的資料字典視圖包含ORACLE使用者所擁有的資訊,查詢使用者擁有或有權通路的所有表資訊:select * from all_tables;

3.以DBA_開始的視圖一般隻有ORACLE資料庫管理者可以通路:select * from dba_tables;

4.查詢ORACLE使用者：conn sys/change_on_installselect * from dba_users;conn system/manager;select * from all_users;

5.建立資料庫使用者：CREATE USER user_name IDENTIFIED BY password;GRANT CONNECT TO user_name;GRANT RESOURCE TO user_name;授權的格式: grant (權限) on tablename to username;删除使用者(或表):drop user(table) username(tablename) (cascade);6.向建好的使用者導入資料表IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:\EXPDAT.DMP COMMIT = Y7.索引create index [index_name] on [table_name]( "column_name ")intersect運算

傳回查詢結果中相同的部分

exp:各個部門中有哪些相同的工種

selectjob

fromaccount

intersect

selectjob

fromresearch

intersect

selectjob

fromsales;

minus運算

傳回在第一個查詢結果中與第二個查詢結果不相同的那部分行記錄。

有哪些工種在财會部中有，而在銷售部中沒有？

exp:selectjobfromaccount

minus

selectjobfromsales;

1. oracle安裝完成後的初始密碼?

　internal/oracle

　　sys/change_on_install

　　system/manager

　　scott/tiger

　　sysman/oem_temp

2. oracle9ias web cache的初始預設使用者和密碼？

administrator/administrator

3. oracle 8.0.5怎麼建立資料庫?

用orainst。假如有motif界面，可以用orainst /m

4. oracle 8.1.7怎麼建立資料庫?

dbassist

5. oracle 9i 怎麼建立資料庫?

dbca

6. oracle中的裸裝置指的是什麼?

裸裝置就是繞過檔案系統直接通路的儲存空間

7. oracle如何區分 64-bit/32bit 版本？？？

$ sqlplus '/ as sysdba'

sql*plus: release 9.0.1.0.0 - production on mon jul 14 17:01:09 2003

(c) copyright 2001 oracle corporation. all rights reserved.

connected to:

oracle9i enterprise edition release 9.0.1.0.0 - production

with the partitioning option

jserver release 9.0.1.0.0 - production

sql> select * from v$version;

banner

----------------------------------------------------------------

oracle9i enterprise edition release 9.0.1.0.0 - production

pl/sql release 9.0.1.0.0 - production

core 9.0.1.0.0 production

tns for solaris: version 9.0.1.0.0 - production

nlsrtl version 9.0.1.0.0 - production

sql>

8. svrmgr什麼意思？

svrmgrl，server manager.

9i下沒有，已經改為用sqlplus了

sqlplus /nolog

變為歸檔日志型的

9. 請問如何分辨某個使用者是從哪台機器登陸oracle的?

select machine , terminal from v$session;

10. 用什麼語句查詢字段呢？

desc table_name 可以查詢表的結構

select field_name,... from ... 可以查詢字段的值

select * from all_tables where table_name like '%'

select * from all_tab_columns where table_name='??'

11. 怎樣得到觸發器、過程、函數的建立腳本？

desc user_source

user_triggers

12. 怎樣計算一個表占用的空間的大小？

select owner,table_name,

num_rows,

blocks*aaa/1024/1024 "size m",

empty_blocks,

last_analyzed

from dba_tables

where table_name='xxx';

here: aaa is the value of db_block_size ;

xxx is the table name you want to check

13. 如何檢視最大會話數？

select * from v$parameter where name like 'proc%';

sql>

sql> show parameter processes

name type value

------------------------------------ ------- ------------------------------

aq_tm_processes integer 1

db_writer_processes integer 1

job_queue_processes integer 4

log_archive_max_processes integer 1

processes integer 200

這裡為200個使用者。

select * from v$license;

其中sessions_highwater紀錄曾經到達的最大會話數

14. 如何檢視系統被鎖的事務時間？

select * from v$locked_object ;

15. 如何以archivelog的方式運作oracle。

init.ora

log_archive_start = true

restart database

16. 怎麼擷取有哪些使用者在使用資料庫

select username from v$session;

17. 資料表中的字段最大數是多少?

表或視圖中的最大列數為 1000

18. 怎樣查得資料庫的sid ?

select name from v$database;

也可以直接檢視 init.ora檔案

19. 如何在oracle伺服器上通過sqlplus檢視本機ip位址 ?

select sys_context('userenv','ip_address') from dual;

假如是登陸本機資料庫，隻能傳回127.0.0.1，呵呵

20. unix 下怎麼調整資料庫的時間？

su -root

date -u 08010000

21. 在oracle table中如何抓取memo類型字段為空的資料記錄?

select remark from oms_flowrec where trim(' ' from remark) is not null ;

22. 如何用bbb表的資料去更新aaa表的資料(有關聯的字段)

up2003-10-17 aaa set bns_snm=(select bns_snm from bbb where aaa.dpt_no=bbb.dpt_no) where bbb.dpt_no is not null;

23. p4計算機安裝方法

将symcjit.dll改為sysmcjit.old

24. 何查詢server是不是ops?

select * from v$option;

假如parallel server=true則有ops能

25. 何查詢每個使用者的權限?

select * from dba_sys_privs;

26. 如何将表移動表空間?

alter table table_name move tablespace_name;

27. 如何将索引移動表空間?

alter index index_name rebuild tablespace tablespace_name;

28. 在linux,unix下如何啟動dba studio?

oemapp dbastudio

29. 查詢鎖的狀況的對象有?

v$lock, v$locked_object, v$session, v$sqlarea, v$process ;

查詢鎖的表的方法:

select s.sid session_id, s.username, decode(lmode, 0, 'none', 1, 'null', 2, 'row-s (ss)', 3, 'row-x (sx)', 4, 'share', 5, 's/row-x (ssx)', 6, 'exclusive', to_char(lmode)) mode_held, decode(request, 0, 'none', 1, 'null', 2, 'row-s (ss)', 3, 'row-x (sx)', 4, 'share', 5, 's/row-x (ssx)', 6, 'exclusive', to_char(request)) mode_requested, o.ccbzzp||'.'||o.object_name||' ('||o.object_type||')', s.type lock_type, l.id1 lock_id1, l.id2 lock_id2 from v$lock l, sys.dba_objects o, v$session s where l.sid = s.sid and l.id1 = o.object_id ;

30. 如何解鎖?

alter system kill session ‘sid,serir#’;

31. sqlplus下如何修改編輯器?

define _editor="" -- 必須加上雙引号

來定義新的編輯器，也可以把這個寫在$oracle_home/sqlplus/admin/glogin.sql裡面使它永久有效。

32. oracle産生随機函數是?

dbms_random.random

33. linux下查詢磁盤競争狀況指令?

sar -d

33. linux下查詢cpu競争狀況指令?

sar -r

34. 查詢目前使用者對象?

select * from user_objects;

select * from dba_segments;

35. 如何擷取錯誤資訊?

select * from user_errors;

36. 如何擷取連結狀況?

select * from dba_db_links;

37. 檢視資料庫字元狀況?

select * from nls_database_parameters;

select * from v$nls_parameters;

38. 查詢表空間資訊?

select * from dba_data_files;

39. oracle的interal使用者要密碼?

修改 sqlnet.ora

sqlnet.authentication_services=(nts)

40. 出現java.exe的解決辦法?

一般是将oracleorahomexihttpserver改成手工啟動可以的

x是8或9

41. 如何給表、列加注釋？

sql>comment on table 表 is '表注釋';

注釋已建立。

sql>comment on column 表.列 is '列注釋';

注釋已建立。

sql> select * from user_tab_comments where comments is not null;

42. 如何檢視各個表空間占用磁盤情況？

sql> col tablespace format a20

sql> select

b.file_id 檔案id号,

b.tablespace_name 表空間名,

b.bytes 位元組數,

(b.bytes-sum(nvl(a.bytes,0))) 已使用,

sum(nvl(a.bytes,0)) 剩餘空間,

sum(nvl(a.bytes,0))/(b.bytes)*100 剩餘百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_id,b.bytes

order by b.file_id

43. 如把oracle設定為mts或專用模式？

#dispatchers="(protocol=tcp) (service=sidxdb)"

加上就是mts，注釋就是專用模式，sid是指你的執行個體名。

44. 如何才能得知系統目前的scn号 ?

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

45. 請問如何在oracle中取毫秒?

9i之前不支援,9i開始有timestamp.

9i可以用select systimestamp from dual;

__________________

風起雲湧，一鍋稀飯。

人生苦悶，開心就行。

向版主反映這個文章 | ip: 已記錄

10-13-03 01:20

開心果

資深會員

注冊日期: aug 2003

來自: 福建

發帖數: 525

大家在應用oracle的時候可能會碰到很多看起來不難的問題, 非凡對新手來說, 今天我簡單把它總結一下, 釋出給大家, 希望對大家有幫助! 和大家一起探讨, 共同進步!

對oracle高手來說是不用看的.

46. 如何在字元串裡加回車？

select 'welcome to visit'||chr(10)||'www.csdn.net' from dual ;

47. 中文是如何排序的？

oracle9i之前，中文是按照二進制編碼進行排序的。

在oracle9i中新增了按照拼音、部首、筆畫排序功能。設定nls_sort值

schinese_radical_m 按照部首（第一順序）、筆劃（第二順序）排序

schinese_stroke_m 按照筆劃（第一順序）、部首（第二順序）排序

schinese_pinyin_m 按照拼音排序

48. oracle8i中對象名可以用中文嗎？

可以

49. 如何改變win中sql*plus啟動選項？

sql*plus自身的選項設定我們可以在$oracle_home/sqlplus/admin/glogin.sql中設定。

50. 怎樣修改oracel資料庫的預設日期?

alter session set nls_date_format='yyyymmddhh24miss';

or

可以在init.ora中加上一行

nls_date_format='yyyymmddhh24miss'

51. 如何将小表放入keep池中?

alter table xxx storage(buffer_pool keep);

52. 如何檢查是否安裝了某個patch?

check that orainventory

53. 如何使select語句使查詢結果自動生成序号?

select rownum,col from table;

54. 如何知道資料褲中某個表所在的tablespace?

select tablespace_name from user_tables where table_name='test';

select * from user_tables中有個字段tablespace_name，（oracle）;

select * from dba_segments where …;

55. 怎麼可以快速做一個和原表一樣的備份表?

create table new_table as (select * from old_table);

55. 怎麼在sqlplus下修改procedure?

select line,trim(text) t from user_source where name =’a’ order by line;

56. 怎樣解除procedure被意外鎖定?

alter system kill session ,把那個session給殺掉，不過你要先查出她的session id

or

把該過程重新改個名字就可以了。

57. sql reference是個什麼東西？

是一本sql的使用手冊，包括文法、函數等等，oracle官方網站的文檔中心有下載下傳.

58. 如何檢視資料庫的狀态?

unix下

ps -ef | grep ora

windows下

看服務是否起來

是否可以連上資料庫

59. 請問如何修改一張表的主鍵?

alter table aaa

drop constraint aaa_key ;

alter table aaa

add constraint aaa_key primary key(a1,b1) ;

60. 改變資料檔案的大小?

用 alter database .... datafile .... ;

手工改變資料檔案的大小，對于原來的 資料檔案有沒有損害。

61. 怎樣檢視oracle中有哪些程式在運作之中？

檢視v$sessions表

62. 怎麼可以看到資料庫有多少個tablespace?

select * from dba_tablespaces;

63. 如何修改oracle資料庫的使用者連接配接數？

修改initsid.ora，将process加大，重新開機資料庫.

64. 如何查出一條記錄的最後更新時間?

可以用logminer 察看

65. 如何在pl/sql中讀寫檔案？

utl_file包答應使用者通過pl/sql讀寫作業系統檔案。

66. 怎樣把“&”放入一條記錄中？

insert into a values (translate ('at{&}t','at{}','at'));

67. exp　如何加ｑｕｅｒｙ參數？

exp user/pass file=a.dmp tables(bsempms)

query='"where emp_no="'s09394"'"" ﹔

68. 關于oracle8i支援簡體和繁體的字元集問題？

zhs16gbk可以支

69. data guard是什麼軟體？

就是standby的換代産品

70. 如何建立spfile?

sql> connect / as sysdba

sql> select * from v$version;

sql> create pfile from spfile;

sql> create spfile from pfile='e:"ora9i"admin"eygle"pfile"init.ora';

檔案已建立。

sql> create spfile='e:"ora9i"database"spfileeygle.ora' from pfile='e:"ora9i"admin"eygle"pfile"init.ora';

檔案已建立。

71. 核心參數的應用?

shmmax

　　含義：這個設定并不決定究竟oracle資料庫或者作業系統使用多少實體記憶體，隻決定了最多可以使用的記憶體數目。這個設定也不影響作業系統的核心資源。

　　設定方法：0.5*實體記憶體

　　例子：set shmsys:shminfo_shmmax=10485760

　　shmmin

　　含義：共享記憶體的最小大小。

　　設定方法：一般都設定成為1。

　　例子：set shmsys:shminfo_shmmin=1：

　　shmmni

　　含義：系統中共享記憶體段的最大個數。

　　例子：set shmsys:shminfo_shmmni=100

　　shmseg

　　含義：每個使用者程序可以使用的最多的共享記憶體段的數目。

　　例子：set shmsys:shminfo_shmseg=20：

　　semmni

　　含義：系統中semaphore identifierer的最大個數。

　　設定方法：把這個變量的值設定為這個系統上的所有oracle的執行個體的init.ora中的最大的那個processes的那個值加10。

　　例子：set semsys:seminfo_semmni=100

　　semmns

　　含義：系統中emaphores的最大個數。

　　設定方法：這個值可以通過以下方式計算得到：各個oracle執行個體的initsid.ora裡邊的processes的值的總和（除去最大的processes參數）＋最大的那個processes×2＋10×oracle執行個體的個數。

　　例子：set semsys:seminfo_semmns=200

　　semmsl:

　　含義：一個set中semaphore的最大個數。

　　設定方法：設定成為10＋所有oracle執行個體的initsid.ora中最大的processes的值。

　　例子：set semsys:seminfo_semmsl=-200

72. 怎樣檢視哪些使用者擁有sysdba、sysoper權限？

sql>conn sys/change_on_install

sql>select * from v_$pwfile_users;

73. 如何單獨備份一個或多個表？

exp 使用者/密碼 tables=(表1,…,表2)

74. 如何單獨備份一個或多個使用者？

exp system/manager owner=(使用者1,使用者2,…,使用者n) file=導出檔案

75. 如何對clob字段進行全文檢索？

select * from a where dbms_lob.instr(a.a,'k',1,1)>0;

76. 如何顯示目前連接配接使用者?

show user

77. 如何檢視資料檔案放置的路徑 ?

col file_name format a50

sql> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

78. 如何檢視現有復原段及其狀态 ?

sql> col segment format a30

sql> select segment_name,ccbzzp,tablespace_name,segment_id,file_id,status from dba_rollback_segs

79. 如何改變一個字段初始定義的check範圍？

sql> alter table xxx drop constraint constraint_name;

之後再建立新限制:

sql> alter table xxx add constraint constraint_name check();

80. oracle常用系統檔案有哪些？

通過以下視圖顯示這些檔案資訊：v$database,v$datafile,v$logfile v$controlfile v$parameter;

81. 内連接配接inner join?

select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no;

82. 如何外連接配接?

select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);

select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;

83. 如何執行腳本sql檔案?

sql>@$path/filename.sql;

84. 如何快速清空一個大表?

sql>truncate table table_name;

85. 如何查有多少個資料庫執行個體?

sql>select * from v$instance;

86. 如何查詢資料庫有多少表?

sql>select * from all_tables;

87. 如何測試sql語句執行所用的時間?

sql>set timing on ;

sql>select * from tablename;

88. chr()的反函數是?

ascii()

select char(65) from dual;

select ascii('a') from dual;

89. 字元串的連接配接

select concat(col1,col2) from table ;

select col1||col2 from table ;

90. 怎麼把select出來的結果導到一個文本檔案中？

sql>spool c:"abcd.txt;

sql>select * from table;

sql >spool off;

91. 怎樣估算sql執行的i/o數 ?

sql>set autotrace on ;

sql>select * from table;

or

sql>select * from v$filestat ;

可以檢視io數

92. 如何在sqlplus下改變字段大小?

alter table table_name modify (field_name varchar2(100));

改大行，改小不行（除非都是空的）

93. 如何查詢某天的資料?

select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');

94. sql 語句如何插入全年日期？

create table bsyear (d date);

insert into bsyear

select to_date('20030101','yyyymmdd')+rownum-1

from all_objects

where rownum

95. 假如修改表名?

alter table old_table_name rename to new_table_name;

96. 如何取得指令的傳回狀态值？

sqlcode=0

97. 如何知道使用者擁有的權限?

select * from dba_sys_privs ;

98. 從網上下載下傳的oracle9i與市場上賣的标準版有什麼差別？

從功能上說沒有差別，隻不過oracle公司有明文規定；從網站上下載下傳的oracle産品不得用于 商業用途，否則侵權。

99. 怎樣判定資料庫是運作在歸檔模式下還是運作在非歸檔模式下？

進入dbastudio，曆程--〉資料庫---〉歸檔檢視。

100. sql>startup pfile和ifile,spfiled有什麼差別？

pfile就是oracle傳統的初始化參數檔案，文本格式的。

ifile類似于c語言裡的include，用于把另一個檔案引入

spfile是9i裡新增的并且是預設的參數檔案，二進制格式

startup後應該隻可接pfile

101. 如何搜尋出前n條記錄？

select * from employee where rownum

102. 如何知道機器上的oracle支援多少并發使用者數?

sql>conn internal ;

sql>show parameter processes ;

103. db_block_size可以修改嗎?

一般不可以﹐不建議這樣做的。

104. 如何統計兩個表的記錄總數?

select (select count(id) from aa)+(select count(id) from bb) 總數 from dual;

105. 怎樣用sql語句實作查找一列中第n大值？

select * from

(select t.*,dense_rank() over (order by sal) rank from employee)

where rank = n;

106. 如何在給現有的日期加上2年？(

select add_months(sysdate,24) from dual;

107. used_ublk為負值表示什麼意思?

it is "harmless".

108. connect string是指什麼?

應該是tnsnames.ora中的服務名後面的内容

109. 怎樣擴大redo log的大小？

建立一個臨時的redolog組，然後切換日志，删除以前的日志，建立新的日志。

110. tablespace 是否不能大于4g?

沒有限制.

111. 傳回大于等于n的最小整數值?

select ceil(n) from dual;

112. 傳回小于等于n的最小整數值?

select floor(n) from dual;

113. 傳回目前月的最後一天?

select last_day(sys2003-10-17) from dual;

114. 如何不同使用者間資料導入?

imp system/manager file=aa.dmp fromuser=user_old touser=user_new rows=y indexes=y ;

115. 如何找資料庫表的主鍵字段的名稱?

sql>select * from user_constraints where constraint_type='p' and table_name='table_name';

116. 兩個結果集互加的函數?

sql>select * from bsempms_old intersect select * from bsempms_new;

sql>select * from bsempms_old union select * from bsempms_new;

sql>select * from bsempms_old union all select * from bsempms_new;

117. 兩個結果集互減的函數?

sql>select * from bsempms_old minus select * from bsempms_new;

118. 如何配置sequence?

建sequence seq_custid

create sequence seq_custid start 1 incrememt by 1;

建表時:

create table cust

{ cust_id smallint not null,

...}

insert 時:

insert into table cust

values( seq_cust.nextval, ...)

日期的各部分的常用的的寫法

119>.取時間點的年份的寫法:

select to_char(sys2003-10-17,'yyyy') from dual;

120>.取時間點的月份的寫法:

select to_char(sys2003-10-17,'mm') from dual;

121>.取時間點的日的寫法:

select to_char(sys2003-10-17,'dd') from dual;

122>.取時間點的時的寫法:

select to_char(sys2003-10-17,'hh24') from dual;

123>.取時間點的分的寫法:

select to_char(sys2003-10-17,'mi') from dual;

124>.取時間點的秒的寫法:

select to_char(sys2003-10-17,'ss') from dual;

125>.取時間點的日期的寫法:

select trunc(sys2003-10-17) from dual;

126>.取時間點的時間的寫法:

select to_char(sys2003-10-17,'hh24:mi:ss') from dual;

127>.日期,時間形态變為字元形态

select to_char(sys2003-10-17) from dual;

128>.将字元串轉換成日期或時間形态:

select to_2003-10-17('2003/08/01') from dual;

129>.傳回參數的星期幾的寫法:

select to_char(sys2003-10-17,'d') from dual;

130>.傳回參數一年中的第幾天的寫法:

select to_char(sys2003-10-17,'ddd') from dual;