数据字典总结(Oracle-10g)

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>