本文包括查看用户下的表、查看当前用户的角色、查看当前用户的系统权限和表级权限、用户创建详细实例、用户时指定临时的表空间为临时表空间群组、查看当前用户的所有权限、检索加密密码并使用后还原、查看用户下的表、查看用户默认表空间、查看用户下的所有表、用户下找所有的索引
查看用户下的表
select * from tab;
desc user_tables;
select TABLE_NAME from user_tables;
查看当前用户的角色
select * from user_role_privs;
**************************************************************************
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
**************************************************************************
用户创建详细实例
给oracle添加一个用户时一般都分配个connect、resource 角色就行了如：
create user zhyhl_dc identified by zhyhl_dc default tablespace data; 创建一个用户zhyhl_dc，默认表空间为data
grant connect,resource to zhyhl_dc;赋予用户connect,resource权限
但这里connect 角色有一个 UNLIMITED TABLESPACE 权限，也就是zhyhl_dc这个用户可以在其他表空间里随意建表。
select * from dba_sys_privs where grantee='ZHYHL_DC';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ZHYHL_DC UNLIMITED TABLESPACE NO
一般ＤＢＡ要把这个 UNLIMITED TABLESPACE权限关掉
SQL> revoke unlimited tablespace from zhyhl_dc; #回收unlimited tablespace权限
SQL> alter user zhyhl_dc quota unlimited on data; #其中后面data是表空间名
User altered 经过这样修改，用imp就不会导入到其他表空间的情况了。
**************************************************************************
1. 标准用户的创建
create tablespace mcdata datafile '/u02/oradata/sxtprv/mcdata01.dbf' size 5000m;
create user mc identified by PASSWORD default tablespace mcdata temporary tablespace temp;
grant connect, resource to mc;
revoke unlimited tablespace from mc;
alter user mc quota unlimited on mcdata;
连接测试
conn mc/password
查询权限
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
*******************************************************************************
CREATE INDEXTYPE
9 rows selected.
查询磁盘的配额
set linesize 180
select tablespace_name, username, bytes, max_bytes FROM dba_ts_quotas;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
SYSAUX SYSMAN 72679424 -1
SYSAUX OLAPSYS 16318464 -1
MCDATA MC 2883584 -1
SYSAUX DMSYS 262144 209715200
2. 用户的帐号修改
SQL> select username,default_tablespace from dba_users;
SQL> revoke connect from dbhousecp;
SQL> alter user dbhousecp account lock;
用户已更改。
SQL> drop user dbhousecp cascade;
3. 失效的帐号
SQL> select Username, Account_Status
2 from DBA_USERS
3 /
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
testuser OPEN
DBSNMP OPEN
PERFSTAT OPEN
BECVX OPEN
OUTLN EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
CTXSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
ANONYMOUS EXPIRED & LOCKED
WKSYS EXPIRED & LOCKED
WKPROXY EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
已选择17行。
4. 手工强制用户口令终止
要求该用户在下一个会话期间改动这个口令。ALTER USER user PASSWORD EXPIRE
SQL> conn /as sysdba
已连接。
SQL> alter user testuser
2 password expire;
用户已更改。
SQL> conn testuser/testuser
ERROR:
ORA-28001: the password has expired
更改testuser的口令
新口令:
重新键入新口令:
口令已更改
已连接。
SQL>
5. 解除帐号封锁和修改口令
SQL> alter user <USERNAME> identified by <NEW_PASSWORD> account unlock;
用户时指定临时的表空间为临时表空间群组
SQL> create user testuserweb identified by sun2moon
2 default tablespace users
3 temporary tablespace tempgrp;
User created.
SQL>
SQL> select username, temporary_tablespace from dba_users
2 where username='testuserWEB';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
testuserWEB TEMPGRP
查看当前用户的所有权限
SQL> GRANT CONNECT,RESOURCE TO testuser;
授权成功。
SQL> CONN testuser/testuser
已连接。
SQL> SELECT * FROM SESSION_PRIVS; /* 查看当前用户的所有权限 */
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
已选择14行。
SQL>
注意：当前具备UNLIMITED TABLESPACE权限，因为授予了resource或dba角色后，会自动授予该权限，revoke时也会自动收回。
检索加密密码并使用后还原
SQL> select password from dba_users where username = 'SKING';
PASSWORD
------------------------------
83C7CBD27A941428
SQL> alter user sking identified by temp_pass;
User altered.
SQL> connect sking/temp_pass@dw;
Connected.
SQL> alter user sking identified by values '83C7CBD27A941428';
**************************************************************************
查看用户默认表空间
SQL> select username,default_tablespace from DBA_USERS;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
**************************************************************************
查看用户下的所有表
select * from tab;
select * from cat;
exec dbms_stats.gather_schema_stats(ownname=>USER, cascade=> true );
select TABLE_NAME, NUM_ROWS, TO_CHAR(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED_TIME from user_tables;
示例输出：
SQL> exec dbms_stats.gather_schema_stats(ownname=>USER, cascade=> true );
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME, NUM_ROWS, TO_CHAR(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED_TIME from user_tables;
TABLE_NAME NUM_ROWS LAST_ANALYZED_TIME
------------------------------ ---------- -------------------
REGIONS 4 2010-05-08 11:35:37
LOCATIONS 23 2010-05-08 11:35:37
DEPARTMENTS 27 2010-05-08 11:35:36
JOBS 19 2010-05-08 11:35:36
EMPLOYEES 107 2010-05-08 11:35:36
JOB_HISTORY 8 2010-05-08 11:35:36
CUSTOMERS 319 2010-05-08 11:35:36
WAREHOUSES 0 2010-05-08 11:35:37
ORDER_ITEMS 665 2010-05-08 11:35:37
ORDERS 105 2010-05-08 11:35:37
INVENTORIES 0 2010-05-08 11:35:36
TABLE_NAME NUM_ROWS LAST_ANALYZED_TIME
------------------------------ ---------- -------------------
CATEGORIES 22 2010-05-08 11:35:36
PRODUCT_INFORMATION 288 2010-05-08 11:35:37
PRODUCT_DESCRIPTIONS 0 2010-05-08 11:35:37
LOAN 3 2010-05-08 11:35:37
BORROWER 3 2010-05-08 11:35:36
DEPT_10 1 2010-05-08 11:35:36
DEPT_20 2 2010-05-08 11:35:36
T 108 2010-05-08 11:35:37
T1 0 2010-05-08 11:35:37
COUNTRIES 25 2010-05-08 11:35:36
21 rows selected.
cat 与 tab 的区别
SQL> desc cat
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
SQL>
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BORROWER TABLE
CATEGORIES TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
cat 与 tab 的区别是cat能显示SEQUENCE, tab能多出了CLUSTERID列
**************************************************************************
用户下找所有的索引
set linesize 180
col COLUMN_NAME format a50
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;
欢迎关注我的公众号 扫描二维码或公众号搜索 “我的工作”
