《数据字典总结(Oracle-10g).pdf》由会员分享，可在线阅读，更多相关《数据字典总结(Oracle-10g).pdf（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据字典总结数据字典总结(Oracle 10g)(Oracle 10g)Oracle data dictionaryOracle data dictionary1 To see some kind of fields belong to some table-查看字段中含有”A”并且所有者是”Test”的表SQLselecttable_namefromdba_tab_colswherecolumn_name=Aandowner=TEST;2 rename a table to a new one-重命名表名SQLrename oldtablename to newtablename3 modi
2、fy 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
3、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
4、own-查看当前用户下的表空间SQLselect*from dba_tablespaces;-for more details-查看当前用户下的文件名,表空间,字节数,用户块的信息SQLselectfile_name,tablespace_name,bytes,user_blocksdba_data_files;10 To create your own tablespace-创建一个表空间SQLCreatetablespaceMyFirstSpacedatafilefromc:/oracle/product/dbs/MyFirstSpace.ora size 512M AUTOEXTEND
5、ON NEXT5M MAXSIZE UNLIMITED default storage(initial 128K next 2M pctincrease0);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 dat
6、afile c:/oradata/k12db/MyFirstSpace.dbf resize500M;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 s
7、et 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 ty
8、pe-设置日期的显示格式的方法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-添加外键的语法SQLalte
9、rtableempaddconstraintconstraintfk_tbnameforeignkey(n1)references tbname(n1)-initially deferred deferrable23 Check operation-添加 check 的语法SQLalter table emp add constraint ck_emp_agender check(agender=男oragender=女)24 Create a table through a subquery-通过子查询创建表的语法SQLcreate table emp_41 as select id,las
10、t_name,userid,start_datefrom 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 NO
11、T NULLCONSTRAINT 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_p
12、ct IN(10,15,20);26 search constraint defination from a table-查看某个表下的约束名,约束类型,查询条件,重命名的名字SQLselect constraint_name,constraint_type,search_condition,r_constraint_namefrom user_constraintswhere table_name=&table_name;27 query result set between 80 and 100-查看记录在 80 至 100 之间的数据SQLselect*from(select rownu
13、m as numrow,c.*from(selectfield_name,.fromtable_namewherecondition1condition2)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
14、 default tablespace tsgusers Temporary tablespace Temp;31 full description on create a user-更详细的创建用户的语法SQLCREATE USER sidney IDENTIFIED BY out_standing1DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE temp QUOTA 5M ON systemPROFILE app_userPASSWORD EXPIRE;32 create a procedure-创建
15、存储过程,执行的方法SQLcreate or replace procedure get_newsasaa 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 create view testview4asselect id,sum(id)test3_id from test3group by id;35 Exp 和 Imp 的帮助的查看方法:Dos命令下:Exp help=yImp help=y36 删除用户下对象的方法最方便的方法就是 drop user aaa cascade;然后重建用户。如果你不想删除后重建用户，那就：select drop|object_type|object_name|;from user_objects;然后执行得出的结果即可。