用户相关
查看所有用户
select * from dba_users;
修改用户名/密码
select user#, name from user$ where name = 'HRDB';--查看用户编码
update user$ set name = 'PDS' where user# = 91; --根据编码修改用户名
alter user PDS identified by 1;--修改用户密码
删除用户
drop user BAODING cascade;--删除用户
数据泵相关
create directory dump_dir as 'G:\dump\dir_name';
grant read,write on directory dump_dir to userA;
impdp pds/1@ORCL directory=dir_name dumpfile=2017-08-14.dmp full=y
表空间
新建表空间
create tablespace NNC_DATA01
logging
datafile 'G:\OracleDB\app\oradata\orcl_qhd\NNC_DATA01.dbf'
size 5000m
autoextend on
next 1024m maxsize 20480m
extent management local;
空间
select segment_type, owner, sum(bytes) / 1024 / 1024
from dba_segments
where tablespace_name = 'NNC_DATA01'
group by segment_type, owner
SELECT UPPER (F.TABLESPACE_NAME) "表空间名",round(D.TOT_GROOTTE_MB/1024,2) "总空间大小(GB)",
round("剩余大小(mb)"/1024,2) "剩余大小(GB)",
TO_CHAR (
ROUND (
(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99')
"使用比"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND (MAX (BYTES) / (1024 * 1024), 2) MAX_BYTES,
SUM (bytes) / (1024 * 1024) AS "剩余大小(mb)"
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
( SELECT DD.TABLESPACE_NAME,
ROUND (SUM (DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
AND UPPER (F.TABLESPACE_NAME) NOT LIKE '%UNDOTBS%'
ORDER BY "使用比" DESC;
表相关
查看所有表
select * from all_tables--查看所有表
