oracle 查看 用户用户权限用户表空间用户默认表空间.docx
《oracle 查看 用户用户权限用户表空间用户默认表空间.docx》由会员分享,可在线阅读,更多相关《oracle 查看 用户用户权限用户表空间用户默认表空间.docx(7页珍藏版)》请在冰豆网上搜索。
![oracle 查看 用户用户权限用户表空间用户默认表空间.docx](https://file1.bdocx.com/fileroot1/2022-11/29/6c259052-1cc9-414a-8c71-e1729b8f9c50/6c259052-1cc9-414a-8c71-e1729b8f9c501.gif)
oracle查看用户用户权限用户表空间用户默认表空间
oracle查看用户,用户权限,用户表空间,用户默认表空间
1.查看用户和默认表空间的关系。
select username,default_tablespace from dba_users;
2.查看当前用户的表:
select table_namefrom user_tables;
3.查看所有用户的表名:
selecttable_name fromall_tables;
4.查看所有表名(其中包括系统表)
selecttable_name fromall_tables;
5.查看所有的表:
select * from tab/dba_tables/dba_objects/cat;
下面介绍Oracle查询用户表空间
◆Oracle查询用户表空间:
select*fromuser_all_tables
◆Oracle查询所有函数和储存过程:
select*fromuser_source
◆Oracle查询所有用户:
select*fromall_users.select*fromdba_users
◆Oracle查看当前用户连接:
select*fromv$Session
◆Oracle查看当前用户权限:
select*fromsession_privs
◆Oracle查看用户表空间使用情况:
1.selecta.file_id"FileNo",a.tablespace_name "Tablespace_name", a.bytes"Bytes",a.bytes-
sum(nvl(b.bytes,0))"Used", sum(nvl(b.bytes,0))"Free", sum(nvl(b.bytes,0))/a.bytes*100"%free"
fromdba_data_filesa,dba_free_spaceb wherea.file_id=b.file_id(+) groupbya.tablespace_name,
a.file_id,a.bytesorderbya.tablespace_name;
1.查看所有用户:
select*fromdba_user;
select*fromall_users;
select*fromuser_users;
2.查看用户系统权限:
select*fromdba_sys_privs;
select*fromall_sys_privs;
select*fromuser_sys_privs;
3.查看用户对象权限:
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
4.查看所有角色:
select*fromdba_roles;
5.查看用户所拥有的角色:
select*fromdba_role_privs;
select*fromuser_role_privs;
6.查看角色所拥有的权限:
select*fromrole_sys_privs;
select*fromrole_tab_privs;
7.查看所有系统权限
select*fromsystem_privilege_map;
8.查看所有对象权限
select*fromtable_privilege_map;
以上是在Oracle中查看用户权限,
在DB2中为:
select*fromsyscat.dbauth
或者
getauthorizations
查看sid
select*fromv$instance
--[ifgtemso9]>WordDocument>View>Normal
View>Zoom>0
Zoom>PunctuationKerning/>DrawingGridVerticalSpacing>7.8磅
DrawingGridVerticalSpacing>DisplayHorizontalDrawingGridEvery>0
DisplayHorizontalDrawingGridEvery>DisplayVerticalDrawingGridEvery>2
DisplayVerticalDrawingGridEvery>ValidateAgainstSchemas/>SaveIfXMLInvalid>false
SaveIfXMLInvalid>IgnoreMixedContent>false
IgnoreMixedContent>AlwaysShowPlaceholderText>false
AlwaysShowPlaceholderText>Compatibility>SpaceForUL/>BalanceSingleByteDoubleByteWidth/>DoNotLeaveBackslashAlone/>ULTrailSpace/>DoNotExpandShiftReturn/>AdjustLineHeightInTable/>BreakWrappedTables/>SnapToGridInCell/>WrapTextWithPunct/>UseAsianBreakRules/>DontGrowAutofit/>UseFELayout/>
Compatibility>BrowserLevel>MicrosoftInternetExplorer4
BrowserLevel>
WordDocument>
[endif]-->
--[ifgtemso9]>LatentStylesDefLockedState="false"LatentStyleCount="156">
LatentStyles>
[endif]-->
--/*FontDefinitions*/@font-face{font-family:
宋体;panose-1:
2160311111;mso-font-alt:
SimSun;mso-font-charset:
134;mso-generic-font-family:
auto;mso-font-pitch:
variable;mso-font-signature:
31351352321602621450;}@font-face{font-family:
"\@宋体";panose-1:
2160311111;mso-font-charset:
134;mso-generic-font-family:
auto;mso-font-pitch:
variable;mso-font-signature:
31351352321602621450;}/*StyleDefinitions*/p.MsoNormal,li.MsoNormal,div.MsoNormal{mso-style-parent:
"";margin:
0cm;margin-bottom:
.0001pt;text-align:
justify;text-justify:
inter-ideograph;mso-pagination:
none;font-size:
10.5pt;mso-bidi-font-size:
10.0pt;font-family:
"TimesNewRoman";mso-fareast-font-family:
宋体;mso-font-kerning:
1.0pt;}/*PageDefinitions*/@page{mso-page-border-surround-header:
no;mso-page-border-surround-footer:
no;}@pageSection1{size:
595.3pt841.9pt;margin:
72.0pt90.0pt72.0pt90.0pt;mso-header-margin:
42.55pt;mso-footer-margin:
49.6pt;mso-paper-source:
0;layout-grid:
15.6pt;}div.Section1{page:
Section1;}-->
--[ifgtemso10]>
[endif]-->
获取表:
selecttable_namefromuser_tables;//当前用户的表
selecttable_namefromall_tables;//所有用户的表
selecttable_namefromdba_tables;//包括系统表
selecttable_namefromdba_tableswhereowner='用户名'
user_tables:
table_name,tablespace_name,last_analyzed等
dba_tables:
ower,table_name,tablespace_name,last_analyzed等
all_tables:
ower,table_name,tablespace_name,last_analyzed等
all_objects:
ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等
获取表字段:
select*fromuser_tab_columnswhereTable_Name='用户表';
select*fromall_tab_columnswhereTable_Name='用户表';
select*fromdba_tab_columnswhereTable_Name='用户表';
user_tab_columns:
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
all_tab_columns:
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
dba_tab_columns:
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
获取表注释:
select*fromuser_tab_comments
user_tab_comments:
table_name,table_type,comments
--相应的还有dba_tab_comments,all_tab_comments,这两个比user_tab_comments多了ower列。
获取字段注释:
select*fromuser_col_comments
user_col_comments:
table_name,column_name,comments