第三天3

第三天3

select * from v$transaction  
--uba undo block address
--XIDUSN  XID 排他锁 USN undo segment number
-- 
select * from dba_tablespaces where contents='UNDO'
select * from dba_segments where tablespace_name='UNDOTBS1'
select * from dba_segments where tablespace_name='UNDOTBS2'
--每个transaction对应一个undo segment
select * from dba_rollback_segs --segment_id 对应就是 select xidusn from v$transaction
select * from v$rollstat --监控online undo segments
--其中XACTS字段表示现有的transaction数量
------3-1
select * from database_properties--查询默认表空间
select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE'
select * from database_properties where property_name like '%TABLESPACE%'
drop tablespace users
*
第 1 行出现错误:
ORA-12919: 不能删除默认永久表空间
select * from dba_tables where tablespace_name ='USERS'
select * from dba_tablespaces
alter database default tablespace test 
select * from dba_users --与用户相关的视图基本就这一个
grant create session to user1 --授权create session权限
select * from dba_sys_privs where grantee='TEST'--看一个用户的系统权限
alter user test password expire --口令过期
alter ueser test account lock --账号锁定
alter ueser test account unlock --账号锁定
select status from v$instance
select * from system_privilege_map --system_privilege_map 是数据库表
select * from dba_tables where table_name='SYSTEM_PRIVILEGE_MAP'
select * from dba_objects where object_name='SYSTEM_PRIVILEGE_MAP'
grant create table to user test
alter user test quota 10m on tablesapce0 quota 20m on users --配额限制用户使用表空间的量
alter user test quota unlimited on system
select * from dba_ts_quotas
--fat32 没有配额
--ntfs 可以有配额限制
select * from dba_roles -- dba connect resource exp_full_database imp_full_database
select * from dba_sys_privs where grantee='RESOURCE'
--UNLIMITED TABLESPACE 不能授予角色，只能授给用户
--当resource授权给用户后，unlimited tablespace 也自动授权给用户
select * from dba_role_privs where grantee='TEST' --查询用户具有哪些角色
--授予角色生效要重新登录，授予系统权限不需要重新登录
select * from session_privs --查询当前session的用户权限
select * from session_roles --session包含的角色
create user test4 identified by test4 default tablespace users temporary tablespace temp password expire  account unlock quota unlimited on users