常用sql命令:
查看用户所有用的所有权限
select privilege from dba_sys_privs where grantee='&USERNAME'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='&USERNAME' );
把用户a的数据读写权限赋给b
select 'grant select on '‖table_name‖ ' to 用户b;' sql_text from dba_tables where owner='用户a':
然后将生成的sql语句执行
查看表结构:
desc table_name;
查看表里有多少条数据:
select count(*) from table_name;
查询表A\B相同的列,自然连接表A和表B
select 列名 from Atable_name natural join Btable_name;
查询数据文件:
select * from DBA_DATA_FILES;
查询控制文件:
select pata_name,para_value from v$dm_ini where para_name like'%PATH%';
查询重做日志文件:
select file_id,path,rlog_size from v$rlogfile;
resize重做日志文件:
alter database resize logfile '/dm8/data/DAMENG/DAMENG01.log' to 300;
新增重做日志文件:
alter database add loglile '/dm8/data/DAMENG/DAMENG03.log' size 300;
查看数据库是否打开归档:
select name,arch_mode from v$database;
打开归档模式(在disql中):
alter database mount;
alter database add archivelog 'type=LOCAL,dest=/dm8/arch,file_size=100,space_limit=0';
alter database archivelog;
alter database open;
查看段:
select * from user/all/DBA_seqments;
查看表空间:
select tablespace_name from dba_tablespaces;
查看表空间中的数据文件:
select tablespace_name,file_name from dba_data_files;
创建一个初始值为50M的表空间test:
create tablespace test datafile '/path/TEST01.DBF' size 50;
创建一个初始值为50M,每次扩展2M,最大1024M的表空间test:
create tablespace test datafile '/path/TEST01.DBF' size 50 autoextend on next 2 maxsize 1024 CAHE=NORMAL;
使表空间test脱机:
alter tablespace test offline;
使表空间test联机:
alter tablespace test online;
解锁用户:
ALTER USER USERNAME ACCOUNT UNLOCK;
或者
call SP_UNLOCK_USER('USER123');
查看当前登录的用户下的表:
select table_name from user_tables;
查看用户下的表:
select * from all_tables where owner='username';
查询用户的角色:
SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='USERNAME';
查询用户的权限:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='USERNAME';
查询系统中所有用户以及其详细信息:
SELECT * FROM DBA_USERS;
查询当前用户可见的所有用户,以及创建时间:
SELECT * FROM ALL_USERS;