Notes

新库创建完要禁用域名登陆

RAC的话，单节点执行即可，可以先 shutdown 其中一个节点

-- 禁用域名登录验证
ALTER SYSTEM SET global_names = false SCOPE = SPFILE;

-- 修改域名为空
ALTER SYSTEM SET db_domain = '' SCOPE = SPFILE;

-- 查询现在的域名，取最前面的实例名字
select * from global_name;

-- 用查询的实例名字，替代现在的域名
UPDATE GLOBAL_NAME SET GLOBAL_NAME = '实例名字';

-- 确保现在只有实例名字，域名已经取掉
select * from global_name;

-- 修改完成后要重启库
shutdown immediate;
startup

关闭180天 密码过期

-- 查询密码过期时间
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
-- 关闭密码过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

数据库客户端版本降级

cd $ORACLE_HOME/network/admin/
vi sqlnet.ora  ## 添加如下内容，没有的话创建文件
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

修改用户密码版本

set lines 999 pages 999
col username for a20
select username,account_status,password_versions from dba_users where account_status='OPEN';

查询数据库字符集

select userenv('language') from dual;

set linesize 400;
set pagesize 400;
select * from nls_database_parameters;

修复失效对象

@$ORACLE_HOME/rdbms/admin/utlrp.sql

查询失效对象

select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';

检查备份路径：

select * from dba_directories

创建备份路径：

create directory dpdata01 as ’/backup’

给用户授予备份路径的读写权限

grant read,write on directory dpdata01 to system

查询用户对象

col  OBJECT_NAME format a30
set linesize 400;
set pagesize 400;
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS,NAMESPACE from dba_objects where owner in (select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')
);

查询用户对象数量

select owner,count(*) from dba_objects where owner in (select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')
) group by owner order by owner;

查询用户每种对象数量

select owner,OBJECT_TYPE,count(*) from dba_objects where owner in (select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')
) group by owner,OBJECT_TYPE order by owner,OBJECT_TYPE;

查询每个用户表的数量

select owner,count(*) from SYS.dba_tables where owner in ( select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP') 
) group by owner;
exit;
!

查询每张表多少行

select owner,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS from SYS.dba_tables where owner in (select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')
) order by owner,TABLE_NAME;
exit;
!

查询用户和默认表空间

select username,default_tablespace from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP');

查询每个schema 大小

select owner,round(sum(bytes/1024/1024),2)||' MB' as schema_size from dba_segments where owner in (select username  from SYS.dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')
)  group by owner order by SCHEMA_SIZE desc;

查询所有表空间大小

select Tablespace_Name,round(sum(bytes/1024/1024),2)||' MB' From Dba_Segments Group By Tablespace_Name;

生成创建表空间命令

SELECT 
       'create tablespace ',
       DF.TABLESPACE_NAME,
    ' datafile size ',
       ROUND(SUM(FREE.BYTES) / 1048576 ) FREE_GB,
    'm autoextend on next 100m;'
  FROM DBA_DATA_FILES DF,
       (SELECT TABLESPACE_NAME,
               FILE_ID,
               SUM(BYTES) BYTES,
               MAX(BYTES) MAXBYTES
          FROM DBA_FREE_SPACE
         WHERE BYTES > 1024 * 1024
         GROUP BY TABLESPACE_NAME, FILE_ID) FREE
 WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+)
  AND DF.FILE_ID = FREE.FILE_ID(+)
 GROUP BY DF.TABLESPACE_NAME;

数据泵全库导出命令

expdp system/system directory=dpdata01 dumpfile=expdp_6_XX01.dmp full=y;

数据泵全库导入数据

impdp system/system directory=dpdata01 dumpfile=expdp_6_XX01.dmp full=y;

expdp/impdp备份

创建expdp 备份路径

mkdir -p /backup
chown -R oracle:oinstall /u01/backup
chmod 755 /backup

创建备份位置

create user expdp_user identified by expdp_passwd;

创建 PDB

create pluggable database portal admin user pdbadmin identified by pdbpasswd;
grant dba to pdbadmin;

授权DBA权限给备份用户

grant dba to expdp_user;
create directory DPDATA01 as '/backup';
grant read,write on directory DPDATA01 to expdp_user;
select * from dba_directories;

创建表空间

create tablespace test datafile size 1M autoextend on next 1M;

-- 增加表空间文件
alter tablespace test add datafile size 1M autoextend on next 1M;

备份数据库

bak_user='test'
pdb_sid='u01prmy'
bak_ops='PARALLEL=3 CONTENT=ALL COMPRESSION=ALL' 
bak_date="`date +%Y-%m-%d`"
expdp expdp_user/expdp_passwd@$pdb_sid directory=DPDATA01 dumpfile=$bak_user'_'$bak_date'_'%U.DUMP logfile=expdp_$bak_user'_'$bak_date.log schemas="'"$bak_user"'" $bak_ops

导入数据

exclude=STATISTICS -----排除统计信息
不导入统计信息，导入完成以后，使用dbms_stats包重新采集统计信息。
可以用dbms_stats.gather_schema_stats('SCHEMANAME') 对导入的那几个用户进行统计。

dbms_stats.gather_schema_stats('SCHEMANAME') 

TABLE_EXISTS_ACTION=REPLACE  -----如果已经有表，第二次倒入需要处理

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

1. SKIP: 直接跳过，进行下一个对象操作。（默认）

2. APPEND: 在原有数据基础上继续追加

3. TRUNCATE: 先TRUNCATE，再完成数据插入

4. REPLACE: 先DROP表，然后创建表，最后完成数据插入

如果表存在使用 replace 选项，导入数据
impdp system/system dumpfile=full.dmp directory=dpdata01 TABLE_EXISTS_ACTION=replace full=y;

如果表存在使用 truncate 选项导入数据
impdp system/'passwd' dumpfile=full.dmp directory=dpdata01 TABLE_EXISTS_ACTION=TRUNCATE  full=y;

如果表存在如何处理
TABLE_EXISTS_ACTION=TRUNCATE

bak_user='test'
pdb_sid='u01prmy'
bak_ops='CONTENT=ALL PARALLEL=4 TABLE_EXISTS_ACTION=REPLACE exclude=STATISTICS' 
bak_date="`date +%Y-%m-%d`" 
impdp expdp_user/expdp_passwd@$pdb_sid directory=DPDATA01 dumpfile=$bak_user'_'$bak_date'_'%U.DUMP logfile=impdp_$bak_user'_'$bak_date.log schemas="'"$bak_user"'" $bak_ops

切换PDB

alter session set container=HRPDB;

EXP/IMP 备份

oracle exp 和 imp 数据和表结构互相独立导出导入

1）只导入数据、不到导入表结构 不可能只导出数据而不导出表结构 这里做了一个变向处理 可以在imp时使用ignore=Y选项就可以了，而不报错。 这样就可以实现把数据导入到原来的表里；

imp username/pwd@sid fromuser=xx touser=xx ignore=Y;

2）导出表结构，不导出表数据 只需在命令行里加一个参数rows=n即可。表示不导出表数据。

exp username/pwd@sid file=d:/data/bak.dmp owner=(user) rows=n;

-- expdp 用参数：

CONTENT=METADATA_ONLY 可以搞定。

-- 示例：

expdp user1/user1 DIRECTORY=dmpdir DUMPFILE=testimpdp4.dat SCHEMAS=user1 logfile=testimpdp4.log CONTENT=METADATA_ONLY exclude=STATISTICS

导出脚本

例子：

exp scott / tiger FILE = scott.dmp OWNER = scott GRANTS = y ROWS = y COMPRESS = y consistent=y; exp TERMINAL/TERMINAL FILE=/orabak/ceshi/TERMINAL.dmp OWNER=TERMINAL LOG=/orabak/ceshi/TERMINAL.log;

导入脚本

例子：

imp system / manager FULL = y IGNORE = y FILE = expdat.dmp imp YDFWPT_SF/YDFWPT_SF FILE =/backup/ceshi/YDFWPT_SF.dmp FROMUSER = YDFWPT_SF TOUSER = YDFWPT_SF ignore=y;