oracle数据库常用操作文档.docx

查看当前用户的表空间：SQLselect username,default_tablespace from user_users;查看表空间具体在硬盘的位置：SQL select name from v$datafile where name like %NFRCDL_TEST%;查看表空间中数据文件具体位置和文件名，大小，表空间名等信息SQL Select * FROM DBA_DATA_FILES where file_name like %NFRCDL_TEST%;Oracle创建表空间注意点：1.如果在PL/SQL 等工具里打开的话，直接修改下面的代码中[斜体加粗部分]执行2.确保路径存在，比如【D:\oracle\oradata\Oracle9i\】也就是你要保存文件的路径存在/*分为四步 *//*第1步：创建临时表空间 *//*这一步可以省略，直接用系统默认临时表空间*/create temporarytablespaceuser_temptempfile D:\oracle\oradata\Oracle9i\user_temp.dbf size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步：创建数据表空间 */createtablespaceuser_dataloggingdatafile D:\oracle\oradata\Oracle9i\user_data.dbf size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步：创建用户并指定表空间 */create user username identified by passworddefaulttablespaceuser_datatemporarytablespaceuser_temp; /*第4步：给用户授予权限 */grant connect,resource,dba to username;用户查看当前用户的缺省表空间SQLselect username,default_tablespace from user_users;查看当前用户的角色SQLselect * from user_role_privs;查看当前用户的系统权限和表级权限SQLselect * from user_sys_privs;SQLselect * from user_tab_privs;显示当前会话所具有的权限SQLselect * from session_privs;显示指定用户所具有的系统权限SQLselect * from dba_sys_privs where grantee=GAME;表查看用户下所有的表SQLselect * from user_tables;查看名称包含log字符的表SQLselect object_name,object_id from user_objectswhere instr(object_name,LOG)0;查看某表的创建时间SQLselect object_name,created from user_objects where object_name=upper(table_name);查看某表的大小SQLselect sum(bytes)/(1024*1024) as size(M) from user_segmentswheresegment_name=upper(table_name);查看放在ORACLE的内存区里的表SQLselect table_name,cache from user_tables where instr(cache,Y)0;索引查看索引个数和类别SQLselect index_name,index_type,table_name from user_indexes order by table_name;查看索引被索引的字段SQLselect * from user_ind_columns where index_name=upper(index_name);查看索引的大小SQLselect sum(bytes)/(1024*1024) as size(M) from user_segmentswhere segment_name=upper(index_name);序列号查看序列号，last_number是当前值SQLselect * from user_sequences;视图查看视图的名称SQLselect view_name from user_views;查看创