数据字典总结(Oracle_10g).doc
文本预览下载声明
数据字典总结(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
显示全部