Oracle DBA操作

SYSTEM用户查某个用户下的表

select * from dba_tables t where t.OWNER='G_M'

查所有sequence

select * from dba_sequences a where a.sequence_owner like 'GAME%'

某个用户的表名注释

select a.TABLE_NAME, b.COMMENTS,a.NUM_ROWS,a.OWNER,a.TABLESPACE_NAME
  from dba_tables a, all_tab_comments b
 where  a.TABLE_NAME = b.table_name(+) and a.OWNER like 'G_%'
    order by a.OWNER,a.TABLE_NAME

某个表的字段注释

select * from all_col_comments a where a.table_name='T_UR' and a.OWNER='GE_R'

1. Oracle 查看当前用户所拥有的表

select * from user_tables t order by t.TABLE_NAME

2. 查看用户和默认表空间的关系

select * from dba_users;

3. 查看表空间

select * from dba_tablespaces;

4. 删除当前用户

drop user newcourse cascade; 

5. 删除表空间

drop tablespace cps including contents and datafiles;

6. 查询所有函数和储存过程

select * from user_source

7. 查看表空间使用情况

select a.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"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;

--创建用户  
CREATE USER "APITEST"  PROFILE "DEFAULT"   
    IDENTIFIED BY "apitest" DEFAULT TABLESPACE "LOUSHANG"   
    TEMPORARY TABLESPACE "TEMP"   
    ACCOUNT UNLOCK;  
  
--为用户指定表空间  
GRANT UNLIMITED TABLESPACE TO "APITEST";  
  
--为用户授权  
GRANT "CONNECT" TO "APITEST";  
GRANT "DBA" TO "APITEST";  
GRANT "RESOURCE" TO "APITEST";  
  
--将锁定用户解锁  
alter user <用户名> account unlock;  
  
--修改用户密码  
alter user <用户名> identified by <新密码>;  
  
--删除用户  
drop user  apitest； ----仅仅是删除用户，  
drop user apitest cascade ；----会删除此用户名下的所有表和视图。  
  
---查看当前用户信息  
select * from user_users;  
  
---查询当前数据库实例中有哪些用户  
select * from dba_users order by username;  
  
---查看当前用户拥有的角色  
select * from user_role_privs;  
  
---查看当前用户所拥有的表  
select * from user_tables;  
  
---查看当前用户所拥有表的列  
select * from USER_TAB_COLUMNS ;  
  
---显示特权用户（一般包括sys、system）  
select * from v$pwfile_users;  
  
---查询当前用户所拥有的所有对象（表、视图、索引、存储函数和过程等）  
select * from user_objects   
  
----查看序列号  
select * from user_sequences;  
  
---查看当前用户所有的视图  
select * from  user_views;  
  
--查看当前连接信息  
select SID,SERIAL#,USERNAME,MACHINE,LOGON_TIME from v$session where username='APITEST';  
  
--断开指定连接  
alter system kill session '530,49177';