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

数据字典总结(Oracle 10g)Oracle data dictionary1 To see some kind of fields belong to some table-查看字段中含有”A”并且所有者是”Test”的表SQLselect table_name from dba_tab_cols where column_name=A and owner=TEST;2 rename a table to a new one -重命名表名SQLrename oldtablename to newtablename3 modify a field,changing its data type.Datum empty needed first.-修改字段的类型SQLalter table testtable modify column id varchar2(12)4 rename a column of a table to a new one-重命名字段名SQLalter table testtable rename column id to iden;5 delete a column form a table-删除字段SQLalter table testtable drop column id;6 add a new field to a table-添加字段SQLalter table testtable add newfield varchar2(2);7 To view privileges or roles one user own -查看当前用户下的表权限SQLselect * from user_tab_privs;-查看当前用户下的角色权限SQLselect * from user_role_privs;8 To see current database name -查看当前数据库名SQLselect name from v$database;9 To see tablespace user own-查看当前用户下的表空间SQLselect * from dba_tablespaces;-for more details-查看当前用户下的文件名,表空间,字节数,用户块的信息SQLselect file_name,tablespace_name,bytes,user_blocks from dba_data_files;10 To create your own tablespace-创建一个表空间SQLCreate tablespace MyFirstSpace datafile c:/oracle/product/9.2.0/dbs/MyFirstSpace.ora size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED default storage (initial 128K next 2M pctincrease 0);11 drop a tablespace-删除一个半空间SQLdrop tablespace MyFirstSpace including contents;12 alter its owner for tablespace-改变表空间的拥有者SQLalter tablespace MyFirstSpace owner to shen;13 resize tablespace-重新设置表空间的大小SQLalter database datafile c:/oradata/k12db/MyFirstSpace.dbf resize 500M;14 to see all tables one user own-For current user-查看当前用户下的所有的表SQLselect table_name from user_tables;-for all users-查看所有用户下的所有的表SQLselect table_name from all_tables;15 see your system table props$-查看系统设置的命令SQLselect * from props$;16 update character set to needed-修改数据库字符集的例子SQLalter database character set ZHS16GBK;17 View current time-查看当前的系统时间SQLselect sysdate from dualwhile in sql server getdate is used as a function-sql server的语法SQLselect getdate()18 select current date-查看当前的日期SQLselect current_date from dual19 Change current date display type-设置日期的显示格式的方法SQLalter session set NLS_date_formate=dd-mon-yyyy hh:mi:ss;20 query current database instance-查看当前的数据库实例名SQLselect instance_name from v$instance;21 domain integrity operationSQLalter table tbname add constraint pk_tbname primary key(n1)22 referential integrity operation-添加外键的语法SQLalter table emp add constraint constraint fk_tbname foreign key(n1)references tbname(n1)- initially deferred deferrable23 Check operation-添加check的语法SQLalter table emp add constraint ck_emp_agender check(agender=男or agender=女)24 Create a table through a subquery-通过子查询创建表的语法SQLcreate table emp_41 as select id, last_name, userid, start_date from s_emp where dept_id = 41;25 A complex example 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_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, start_date DATE DEFAULT SYSDATE, comments VARCHAR2(25), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2), commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20);26 search constraint defination from a table-查看某个表下的约束名,约束类型,查询条件,重命名的名字SQLselect constraint_name, constraint_type, search_condition, r_constraint_name from user_constraints where table_name = &table_name;27 query result set between 80 and 100-查看记录在80至100之间的数据SQLselect * from ( ( select rownum as numrow, c.* from ( select field_name,. from table_name where condition1 order by condition2) c) where numrow 80 and numrow insert into testc(id,addr) select (empid,empaddr) from testb29 drop a constraint-删除约束SQLalter table fortest drop constraint fortest_cons30 create a user-创建一个用户create user gdcc identified by oracle default tablespace tsgusers Temporary tablespace Temp;31 full description on create a user-更详细的创建用户的语法SQLCREATE USER sidney IDENTIFIED BY out_standing1 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE app_user PASSWORD EXPIRE;32 create a procedure-创建存储过程,执行的方法SQLcreate or replace procedure get_newsasaa number;begin select 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 testview3 as select * from test3;34 a view by using aggregate function-创建个带有聚集函数的视图SQL create view testview4 as