1 --------oracle备份方式了解-----------
Oracle的常规备份无非是exp/imp,expdp/impdp,rman三种方式。
exp/imp简单方便,适用于跨db版本、跨os平台、异地备份等情况,是大家最常用的一种备份方式。
expdp/impdp是10g以后推出的备份方式,其特点就是效率的大幅改善,据eygle报告,impdp相比与传统imp有20倍速度提升,其最大的缺点是不能跨数据库版本,连小版本号也不行(例如11.1到11.2)。
rman相比前两种备份方式,相对配置复杂,一般是DBA作为前两种方案的备份。
2 exp导出密码含有特殊符号的密码的用户,导出表(window系统上面使用)
exp user/"""admin@%#"""@10.4.152.54/XZWFWW file=D:\aaaa\beifen\JFAQ_ANSWER.dmp tables=JFAQ_ANSWER
3 修改密码,同时也延长账号密码过期时间
alter user TEST IDENTIFIED BY “password”
4 查看密码过期时间
select username,profile,EXPIRY_DATE from dba_users where username='TEST';
5 查看密码策略
select * from dba_profiles;
6 修改密码策略,oracle往往有个策略限制用户密码的复杂度,可以将其禁用
alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION NULL; ##之前为ORA12C_VERIFY_FUNCTION
7 给账号解锁
alter user test account unlock;
8 关于oracle表名大小写的问题
问题:我们有时候查询表的时候发现 不加双引号 显示表不存在,加上就能正常查询
select count(*) from qxbmyfk;
结果:
> ORA-00942: 表或视图不存在
select count(*) from "qxbmyfk";
结果:
count(*)
1887
说明:oracle表和字段是有大小写的区别。oracle默认是大写,如果我们用双引号括起来的就区分大小写,如果没有,系统会自动转成大写
-----oracle修改表名---------------------
RENAME table_name TO new_table_name;
9 oracle表空间扩容
----查看表空间的情况-----------------
SELECT distinct R1.*,R2.MAX_SIZE_GB,R3.AUTOEXTENSIBLE FROM
(SELECT /*+ ORDERED */
A.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(A.BYTES / 1024 / 1024 / 1024, 2) CURRENT_SIZE_GB,
ROUND((A.BYTES - B.BYTES) / 1024 / 1024 / 1024, 2) USED_SIZE_GB,
ROUND(B.BYTES / 1024 / 1024 / 1024, 2) FREE_SIZE_GB,
ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) PERCENT_USED_RATE
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY A.TABLESPACE_NAME) R1,
(SELECT /*+ ORDERED */
D.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(SUM(D.BYTES) / 1024 / 1024 / 1024, 2) CURRENT_SIZE_GB,
ROUND(SUM(D.MAXBYTES) / 1024 / 1024 / 1024, 2) MAX_SIZE_GB
FROM SYS.DBA_DATA_FILES D,
V$DATAFILE V,
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') E
WHERE (D.FILE_NAME = V.NAME)
GROUP BY D.TABLESPACE_NAME) R2,
DBA_DATA_FILES R3
WHERE R1.TABLESPACE_NAME = R2.TABLESPACE_NAME AND R2.TABLESPACE_NAME=R3.TABLESPACE_NAME
union all
select distinct c.TABLESPACE_NAME,
ROUND(c.BYTES / 1024 / 1024 / 1024, 2) CURRENT_SIZE_GB,
ROUND(d.BYTES_USED / 1024 / 1024 / 1024, 2) USED_SIZE_GB,
ROUND((c.BYTES-d.BYTES_USED) / 1024 / 1024 / 1024, 2) FREE_SIZE_GB,
ROUND(d.BYTES_USED*100/c.BYTES,2) PERCENT_USED_RATE,
ROUND(c.MAXBYTES / 1024 / 1024 / 1024, 2) MAX_SIZE_GB,
c.AUTOEXTENSIBLE
from
(select tablespace_name,sum(bytes) bytes,sum(maxbytes) maxbytes,AUTOEXTENSIBLE
from dba_temp_files group by tablespace_name,AUTOEXTENSIBLE) c,
(select tablespace_name,sum(bytes_used) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name;
---------------查看表空间--------------------
select * from dba_data_files where tablespace_name='test';
#增加一个文件到表空间里面,每次自动增长64M,新增的文件为10G
alter tablespace test add datafile '+DATADG/HLWDBC/5D1177A3254663C6E0532109040A3C4E/DATAFILE/test03.dbf' size 10G autoextend on next 64M maxsize unlimited;
------------查看表空间文件---------------
select file_name, tablespace_name, bytes, status, autoextensible from dba_data_files where tablespace_name='test';