1、數據字典總結 (Oracle 10g)Oracle data dictionary1 To see some kind of fields belong to some table-查看字段中含有 A并且所有者是 Test 的表SQL>select table_name from dba_tab_colswhere column_name ='A' andowner ='TEST' ;2 rename a table to a new one- 重命名表名SQL>rename oldtablenameto newtablename3 modify a

2、 field,changing its data type.Datum empty needed first.- 修改字段的類型SQL>alter table testtable modify column id varchar2 ( 12 )4 rename a column of a table to a new one- 重命名字段名SQL>alter table testtable renamecolumn id to iden ;5 delete a column form a table- 刪除字段SQL>alter table testtable drop co

3、lumn id ;6 add a new field to a table- 添加字段SQL>alter table testtable add newfield varchar2( 2);7 To view privileges or roles one user own- 查看當前用戶下的表權限SQL>select * from user_tab_privs ;- 查看當前用戶下的角色權限SQL>select * from user_role_privs;8 To see current database name- 查看當前數據庫名SQL>select name

4、from v$database ;9 To see tablespace user own- 查看當前用戶下的表空間SQL>select * from dba_tablespaces ;-for more details- 查看當前用戶下的文件名 ,表空間,字節數, 用戶塊的信息SQL>selectfile_name ,tablespace_name,bytes,user_blocksfromdba_data_files;10 To create your own tablespace- 創立一個表空間SQL>CreatetablespaceMyFirstSpacedataf

5、ile'c:/oracle/product/dbs/MyFirstSpace.ora'size 512M AUTOEXTEND ON NEXT5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease0);11 drop a tablespacetablespace MyFirstSpace including contents- 刪除一個半空間 SQL> drop12 alter its owner for tablespace- 改變表空間的擁有者 SQL> alter table

6、space MyFirstSpace13 resize tablespace - 重新設置表空間的大小 SQL> alter database datafile 500M ;14 to see all tables one user own -For current user- 查看當前用戶下的所有的表SQL> select table_name from -for all users- 查看所有用戶下的所有的表 SQL> select table_name from15 see your system table props$- 查看系統設置的命令SQL> selec

7、t * from props$; 16 update character set to needed - 修改數據庫字符集的例子SQL> alter database character17 View current time - 查看當前的系統時間 SQL> select sysdate from dualowner to shen'c:/oradata/k12db/MyFirstSpace.dbf'user_tablesall_tablesset ZHS16GBKresizewhile in sql server getdate is used as a fun

8、ction -sql server的語法SQL> selectgetdate ()18 select current date - 查看當前的日期SQL> select current_date from dual19 Change current date display type- 設置日期的顯示格式的方法SQL> alter session set NLS_date_formate= 'dd-mon-yyyy hh:mi:ss'20 query current database instance- 查看當前的數據庫實例名SQL> select in

9、stance_name from v$instance ;21 domain integrity operationSQL> altertabletbnameadd constraintpk_tbnameprimarykey ( n1)22 referential integrityoperation- 添加外鍵的語法SQL> alter tableempadd constraintconstraintfk_tbname foreignkey ( n1) referencestbname( n1)- initially deferred deferrableor23 Check o

10、peration - 添加 check 的語法 SQL> alter table emp add constraint ck_emp_agender check ( agender =' 男 agender =' 女' )24 Create a table through a subquery- 通過子查詢創立表的語法SQL> createtable emp_41 as select id , last_name , userid , start_datefrom s_emp where dept_id = 41 ;25 A complex example

11、on table structure- 復雜的表結構SQL> CREATE TABLE s_emp( id NUMBER ( 7)CONSTRAINT s_emp_id_pk PRIMARY KEY, last_name VARCHAR2( 25)CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2 ( 25 ), userid VARCHAR2 ( 8)CONSTRAINT s_emp_userid_nnNOT NULLCONSTRAINT s_emp_userid_ukUNIQUE ,start_date DATE D

12、EFAULT SYSDATE , comments VARCHAR2 ( 25), manager_id NUMBER ( 7), title VARCHAR2 ( 25), dept_id NUMBER ( 7)CONSTRAINT s_emp_dept_id_fkREFERENCES s_dept ( id ),salary NUMBER ( 11, 2),commission_pct NUMBER( 4, 2)CONSTRAINT s_emp_commission_pct_ck CHECK ( commission_pct IN( 10, 15 , 20 );26 search cons

13、traint defination from a table- 查看某個表下的約束名 , 約束類型 , 查詢條件 , 重命名的名字 SQL> select constraint_name , constraint_type , search_condition , r_constraint_namefrom user_constraints where table_name = '&table_name' ;27 query result set between 80 and 100 - 查看記錄在 80 至 100 之間的數據 SQL> select *

14、from ( select rownum as numrow , c .* from (byselect field_name,. from table_namewhere condition1 ordercondition2 ) c ) where numrow > 80 and numrow <= 100 )order by condition328 init a table by using Insert into select syntax-insert的語法初始化表SQL> insertinto testc ( id , addr ) select29 drop a

15、 constraint- 刪除約束SQL> altertable fortest drop constraint30 create a user- 創立一個用戶create user gdcc identified by oracle users Temporary tablespace Temp ;( empid , empaddr ) fromfortest_consdefault tablespace tsgtestb31 full description on create a user- 更詳細的創立用戶的語法SQL>CREATE USER sidneyIDENTIFIE

16、D BY out_standing1DEFAULT TABLESPACE exampleQUOTA 10M ON exampleTEMPORARY TABLESPACE tempQUOTA 5M ON systemPROFILE app_userPASSWORDEXPIRE ;32 create a procedure- 創立存儲過程 , 執行的方法SQL> create or replace procedure get_news asaa number ;beginselect count (*) into aa from cf_news ; dbms_output . put_line ( 'aa=' | aa); end ;-excute the procedureSQL> execute get_news ;aa =333 A simple sample on create index- 創立視圖的語法SQL> create or replace view testview3asselect * from test3 ;34 a view by using aggregate function - 創立個帶有聚集函數的視圖 SQL>