数据字典总结(Orcle_10g).docVIP

数据字典总结(Oracle 10g) Oracle data dictionary 1 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 newtablename 3 modify a field,changing its data type.Datum empty needed first. --修改字段的类型 SQLalter table testtable modify 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 dual wh