#监听是否启动？

lsnrctl start

#登录服务器，切换oracle用户

sudo su - oracle

#确定当前的数据库或使用export指定

vi .bash_profile

#export ORACLE_SID=orcl

#export ORACLE_SID=uorcl

#export ORACLE_SID=ora11g

#删除用户及表数据

#drop eezg20190826 test cascade;

-----------------

#从log获取备份库信息 Qdchangshengtaiyangneng.dmp.log

#Starting "QDCS_65"."SYS_IMPORT_FULL_01":  qdcs_65/******** dumpfile=Qdchangshengtaiyangneng.dmp logfile=Qdchangshengtaiyangneng.dmp.log directory=dt12 parallel=8 remap_schema=QDCHANGSHENGTAIYANGNENG:qdcs_65 transform=oid:N 

#由于20.10.130.130数据库直接shell登录sqlplus中文乱码，所以直接在pl/sql里用 sys/sys然后勾选as dba 连接。启用命令行模式创建用户(已解)

#登录数据库

sqlplus / as sysdba;

#乱码就退出修改环境变量

export NLS_LANG='SIMPLIFIED CHINESE_CHINA'.ZHS16GBK

#或写入bash_profile

#i ~/.bash_profile

#NLS_LANG='SIMPLIFIED CHINESE_CHINA'.ZHS16GBK

#source .bash_profile

#oracle错误乱码

alter session set nls_language='SIMPLIFIED CHINESE';//修改会话字符集为中文

alter session set nls_language='AMERICAN';//修改会话字符集为英语

#ALTER DATABASE OPEN;

#ALTER PLUGGABLE DATABASE orclpdb12c OPEN READ WRITE;

#创建用户

create user TJRUIPUSWJT1909 identified by i default tablespace nnc_data01;    

create user oranew0929 identified by i default tablespace nnc_data01;    

create user NCCLOUD1903DEMO_1909 identified by i default tablespace nnc_data01;   

create user NCC_DEMO_PFF0519 identified by i default tablespace nnc_data01; 

create user QDCHANGSHENGTAIYANGNENG identified by i default tablespace nnc_data01; 

create user NCC20191111 identified by i default tablespace nnc_data01; 

create user "dm6118" identified by "nccloud001" default tablespace "nnc_data01" default index tablespace "nnc_index01";

#切回cdb

#alter session set container=CDB$ROOT;

#由于字符集问题，删除已建用户和数据重建

#drop tablespace NNC_DATA01 including contents and datafiles cascade constraint;

#drop tablespace NNC_INDEX01 including contents and datafiles cascade constraint;

#授权

#grant dba,connect to dm6118;

#grant dba,connect to QDCHANGSHENGTAIYANGNENG;

#创建目录，赋值direc，存放备份dmp

create directory direc as '/'; 

#授权用户对目录的读写权限

grant read,write on directory direc to nc65_18c_0506;

grant read,write on directory direc to nc1903_18c_0506;

impdp system/nccloud@10.10.25.99:1521/pdb1 schema=QDCHANGSHENGTAIYANGNENG directory=direc dumpfile=Qdchangshengtaiyangneng.dmp logfile=Qdchangshengtaiyangneng

#退出，导入

impdp nc65_18c_0506/nccloud001@127.0.0.1/orcl dumpfile=nc65gold.dmp  logfile=nc65gold_imp_20200506.log directory=direc  parallel=8  remap_schema=nc65:nc65_18c_0506

impdp QDCHANGSHENGTAIYANGNENG/i@127.0.0.1/pdb1 dumpfile=Qdchangshengtaiyangneng.dmp logfile=Qdchangshengtaiyangneng.log directory=direc  

#impdp SZNCC/1@20.10.130.205/ora11g dumpfile=/db_backup/daqianfangzhi20191011/20191010.DMP logfile=daqianfangzhi20191011.log parallel=4

#不带PDP导入

#imp NCC_DEMO_PFF0519/i@127.0.0.1/orclpdb12c file=9090_20190530012302.dmp  log=9090_20190530012302.log fromuser=NCC_DEMO_PFF0519  touser=NCC_DEMO_PFF0519 buffer=20480000

#连接数据库清理敏感信息(不测试不用删)

#用户数据停用监听，最新版停，NC65不停

update pub_eventlistener set enabled='N' where owner in ('6001','6003','6004','6005','6007','6009','6011','6013','6015','6017','6019','6021','6029','6033','CH30','E202','E3','E324','E326','E342','EC12','EC15','EC30','EC50');

update pub_eventlistener set enabled='N' where IMPLCLASSNAME like '%.oa.%';

#删除敏感信息

update sm_user set EMAIL=null;

update bd_psndoc set EMAIL=null,ID=null,MOBILE=null;

#当前表空间占用

SELECT a.tablespace_name TABLESPACE_NAME, total / 1048576 TOTAL_M, free / 1048576 FREE_M, (total - free) / 1048576 USED_M, ROUND((total - free) / total, 4) * 100 "USED%", autoextensible autoextem FROM (SELECT tablespace_name, SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total, max(autoextensible) autoextensible FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name order by 6, 5 desc;

#当前表空间数据文件位置

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space  from dba_data_files  order by tablespace_name;

#查看表空间下哪个表大大表查询

select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
where tablespace_name='SYSTEM' group by segment_name)
where sx>100 order by sx desc;

#增加表空间数据文件

#create tablespace NNC_DATA01 datafile '/data/oracle12c/app/oracle12c/oradata/orcl12c/nnc_data01_01.dbf' size 30G autoextend off;

#create tablespace NNC_INDEX01 datafile '/data/oracle12c/app/oracle12c/oradata/orcl12c/nnc_index01_01.dbf' size 10G autoextend off;

#create tablespace NNC_DATA01 datafile '+DATA/RACNCC/DATAFILE/nnc_data01_01.dbf' size 30G autoextend off;

#create tablespace NNC_INDEX01 datafile '+DATA/RACNCC/DATAFILE/nnc_index01_01.dbf' size 10G autoextend off;

alter tablespace NNC_DATA01 add datafile '/oradata/oracle/data_files/ora11g/nnc_data01_43.dbf' size 30G autoextend off;

为表空间 添加数据库文件

alter tablespace NNC_DATA01 add datafile '/backup/oradata/nnc_data01_02.dbf' size 30G autoextend off;

#ora11g

alter tablespace NNC_DATA01 add datafile '/oradata/oracle/data_files/ora11g/nnc_data01_47.dbf' size 30G autoextend off;

查看表空间的名称及大小 

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 

FROM dba_tablespaces t, dba_data_files d 

WHERE t.tablespace_name = d.tablespace_name 

GROUP BY t.tablespace_name; 

查看数据库的版本　 

SELECT version 

FROM product_component_version 

WHERE substr(product, 1, 6) = 'Oracle'; 

查看表空间的使用情况 

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 

FROM dba_free_space 

GROUP BY tablespace_name; 

SELECT a.tablespace_name, 

a.bytes total, 

b.bytes used, 

c.bytes free, 

(b.bytes * 100) / a.bytes "% USED ", 

(c.bytes * 100) / a.bytes "% FREE " 

FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 

WHERE a.tablespace_name = b.tablespace_name 

AND a.tablespace_name = c.tablespace_name; 

删除用户和删除表空间

drop user usernamecascade;

drop tablespace NNC_DATA01 including contents and datafiles cascade constraint;

drop tablespace NNC_INDEX01 including contents and datafiles cascade constraint;

#查看表空间的名称及大小 

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 

FROM dba_tablespaces t, dba_data_files d 

WHERE t.tablespace_name = d.tablespace_name 

GROUP BY t.tablespace_name; 

#查看当前连接的container

show con_name：

select sys_context('userenv', 'con_name') "Container DB" from dual;

#切换到PDB

alter session set container=上一步的name;

#切换到CDB

alter session set container=CDB$ROOT;

将查询结果拼接成SQL

参考

SELECT 'drop user '||t.username||' cascade;' FROM dba_users t WHERE user_id > 6

输出结果为 

drop user  +  username + cascade;

查看当前数据库字符集

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

当前用户有多少表

select count(*) from user_tables;

select count(*) from sys_tables where tableowner = 'ncc_auto';

windows 通过bat脚本实现数据库快速备份。脚本如下：

说明：请将如下内容保存为.bat文件。根据实际情况修改注释部分说明的变量：

@echo off
echo ================================================
echo Windows环境下Oracle数据库的自动备份脚本
echo 1. 使用当前日期命名备份文件。
echo 2. 自动删除之前的备份。

echo 3. 

backuppath 为备份目录，请如实修改 。 

echo 4. username、userpasswd备份的用户/模式

echo 5. 

exp命令中默认走本地127.0.0.1数据库，模式为用户名，如有不符，请结合实际情况修改 
echo ================================================
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
set curdate=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~5,2%
set backuppath=F:\dbbak
md %backuppath%
::备份前先删除之前备份文件
::del %backuppath%\*.* /F /S /Q
::设置用户名密码
set username=ncc2005pk
set userpasswd=nccloud001

if not exist "F:\dbbak" mkdir F:\dbbak
exp %username%/%userpasswd%@127.0.0.1/orcl file=%backuppath%\%username%_%curdate%.dmp owner(%username%) log=%backuppath%\'%username%'_'%curdate%'.log

exp ncc2005_feeling/i@10.10.3.201:1522/ora11g file=/db_logfile/niuxzhc/NCC2005_FEELING.std20210107.1810.dmp log=/db_logfile/niuxzhc/NCC2005_FEELING.std20210107.1810.dmp.exp.log buffer=20480000

imp ncc2005_feeling_1/i@10.10.3.201:1522/ora11g file=/db_logfile/niuxzhc/NCC2005_FEELING.std20210107.1810.dmp log=/db_logfile/niuxzhc/NCC2005_FEELING.std20210107.1810.dmp.imp.log buffer=40480000  statistics=none fromuser=ncc2005_feeling touser=ncc2005_feeling_1;

oracle中用户删除不了，提示“无法删除当前已连接的用户”

alter user XXX account lock;SELECT * FROM V$SESSION WHERE USERNAME='XXXXX'；

alter system kill session 'xx,xx'drop user xx cascade  ；

# 导入指定表，并设置表存在的策略  

impdp sswu/1 directory=sswu dumpfile=NCC.DMP table_exists_action=truncate tables=scott.dept,scott.emp remap_schema=NCC:SSWU PARALLEL=4 

select value from v$parameter where name = 'processes' --数据库允许的最大连接数

select count(*) from v$process --当前的连接数

*修改最大连接数:

alter system set processes = 300 scope = spfile

命令行--with---shell

一.最简单的调用sqlplus
sqlplus -S "sys/unimas as sysdba" << !
select to_char(sysdate,'yyyy-mm-dd') today from dual;
exit;
!

[oracle@hb shell_test]$ ./echo_time 

TODAY
----------
2011-03-21

-S 是silent mode，不输出类似“SQL>”，连接数据库，关闭数据库之类的信息。


eof可以是任何字符串 比如"laldf"那么当你输入单独一行laldf时"shell认为输入结束，但是必须表示块开始必须使用<<；
开始和结束要匹配这个符号“<<”后面的内容
举例子:

[oracle@hb shell_test]$ sqlplus -s "sys/unimas as sysdba" << abc
> select to_char(sysdate,'yyyy-mm-dd') today from dual;
> exit;
> abc

TODAY
----------
2011-03-21

二.sqlplus的结果传递给shell的方法一

[oracle@hb shell_test]$ cat test2.sh 
#!/bin/bash
VALUE=`sqlplus -S "test/unimas" << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select to_char(sysdate,'yyyy-mm-dd') today from dual;
exit
!`
echo $VALUE
if [ -n "$VALUE" ]; then
echo "The rows is $VALUE"
exit 0
else
echo "There is no row"
fi

三.sqlplus的结果传递给shell的方法二

[oracle@hb shell_test]$ cat test1.sh 
#!/bin/bash
sqlplus -S "test/unimas" << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
col coun new_value v_coun
select count(*) coun from lesson;
exit v_coun
!
VALUE="$?"
echo "show row:$VALUE"

col coun new_value v_coun v_coun为number类型。因为exit 只能返回数值类型。

四.把shell参数传递给sqlplus

#!/bin/bash
t_id="$1"
sqlplus -S "test/unimas" << !
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select teachername from teacher where id=$t_id;
exit
!

五.sqlplus的结果存储在文件中

#!/bin/sh
sqlplus -S "test/unimas"<<EOF
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
spool spool_file
SELECT * from teacher;
spool off
exit;
EOF

http://blog.chinaunix.net/space.php?uid=9124312&do=blog&id=181372

####################################################################################################################################

查看调度系统状态脚本：

#!/bin/sh

if [[ -z "$1" ]] || [[ "$1" -ne 0 && "$1" -ne 2 ]]       #使用[[ ]] 进行逻辑短路操作
then
    echo "Please input your parameter: query status[0,2]!"
    exit
fi

#for buname in cnlog enlog ItLog JrLog AuLog InnerLog
for buname in cnlog enlog 
do
    sqlplus -S 'etl/etl@dw_testdb' << abc        #使用 << EOF方式输入信息
    set line 155
    set pages 9999
    SELECT /*+ PARALLEL(a,4) */ * FROM $buname.hla_job_rec a where status = $1;
    exit
abc
done

STATISTICS=NONE

查询当前实例下的schema

--select username from sys.dba_users可以查出所有的schema
--select table_name from dba_tables where owner='schema名称';可以查出来schema对应的表名。  

select ＊ from all＿tab＿comments －－ 查询所有用户的表，视图等。

select ＊ from user＿tab＿comments －－ 查询本用户的表，视图等。

select ＊ from all＿col＿comments  －－查询所有用户的表的列版名和权注释。

select ＊ from user＿col＿comments －－ 查询本用户的表的列名和注释。

select ＊ from all＿tab＿columns －－查询所有用户的表的列名等信息。

select ＊ from user＿tab＿columns －－查询本用户的表的列名等信息。

统计信息收集

收集某个用户的：exec dbms_stats.gather_schema_stats(ownname=>'用户名',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL'); 

查看某个表的统计信息
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2');
查看某个表上索引的统计信息
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from user_indexes t where table_name in ('T1','T2');
oracle会在一个固定的时间将数据库里的表和索引的相关统计信息进行收集，默认选择周一到周五晚上10点，持续收集4小时，和周六周日早上6点，持续收集20小时。
oracle可以专门对表的记录变化量进行管理，当某表一天记录变化量没有超过指定的阀值时，oracle就不会对该表进行统计信息收集。

修改统计信息自动收集时间
set linesize 200
col REPEAT_INTERVAL for a60
col DURATION for a30
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME REPEAT_INTERVAL DURATION
------------------ ------------------------------------------------------------ ---------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00

关闭自动统计信息收集
BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SATURDAY_WINDOW"',
force => TRUE);
END;
/


修改自动统计信息持续时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value => numtodsinterval(240,'minute'));
END;
/

修改自动统计信息开始时间
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0 ');
END;
/

开启自动统计信息收集
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SATURDAY_WINDOW"');
END;
/

set linesize 200
col REPEAT_INTERVAL for a60
col DURATION for a30
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME REPEAT_INTERVAL DURATION
----------------- ------------------------------------------------------------ --------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00

7 rows selected.

手动收集统计信息
使用dbms_stats程序包手动收集统计信息
收集表统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns');
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TAB_NAME',CASCADE=>TURE);
收集分区表的某个分区统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'RANGE_PART_TAB',partname => 'p_201312',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'USER',indname => 'IDX_OBJECT_ID',estimate_percent => '10',degree => '4');
收集表和索引统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE);
收集某个用户的统计信息
exec dbms_stats.gather_schema_stats(ownname=>'CS',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
收集整个数据库的统计信息
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');
关于dbms_stats中参数的解释，具体参考官方文档：
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582
ownname： USER_NAME
tabname： TABLE_NAME
partname: 分区表的某个分区名
estimate_percent: 采样百分比，有效范围为[0.000001,100]
block_sample：使用随机块采样代替随机行采样
method_opt：
cascade:是否收集此表索引的统计信息
degree:并行处理的cpu数量
granularity： 统计数据的收集，'ALL' - 收集所有（子分区，分区和全局）统计信息

动态采集统计信息
对于新创建的表，当访问此表时，oracle会动态的收集这个表的相关信息，等到晚上10点，再将其收集到数据字典中。

SQL> set autotrace off
SQL> create table t_sample as select * from dba_objects;
Table created.
SQL> create index idx_t_sample_objid on t_sample(object_id);
Index created.
新建的表，查不到统计信息
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------

SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select * from t_sample where object_id=20;
Execution Plan
----------------------------------------------------------
Plan hash value: 1453182238

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=20)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
93 consistent gets
1 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

- dynamic sampling used for this statement (level=2) 表示动态采样，但是不记录数据字典，除非手动收集表的统计信息。
[html] view plain copy
SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = 'T_SAMPLE';

NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------


SQL>

----------------------------------------------------------------------------------------------------------------------------------------全库收集
exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

schema收集
exec dbms_stats.gather_schema_stats(ownname=>'SE',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

Table收集
exec dbms_stats.gather_table_stats(ownname=>'DW_HW_CHCH',tabname=>'表名',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

分区收集
exec dbms_stats.gather_table_stats(ownname=>'DW_HW_CHCH',tabname=>'表名',partname='分区名',estimate_percent=5,degree=>8,cascade=>true,granularity=>'PARTITION',method_opt>'FOR ALL INDEXED COLUMNS');

数据字典收集
exec dbms_stats.gather_dictionary_stats (estimate_percent=>100,degree=>8,cascade=>true,granularity=>'ALL');

动态性能表统计信息
exec dbms_stats.gather_fixed_objects_stats;

硬件统计信息收集
--典型业务开始前
exec dbms_stats.gather_system_stats('START');
--典型业务结束后
exec dbms_stats.gather_system_stats('STOP');


将一个表的统计信息锁住，以防止错误的统计信息将此正确的信息覆盖掉时需要用到LOCK_TABLE_STATS包
--对于有些表或者用户的数据基本不怎么发生变化，如果每次收集时也将其收集一遍浪费资源，因此可以将这些表或者用户进行统计信息采集的锁定
exec dbms_stats.lock_table_stats('owner name','table name');

exec dbms_stats.lock_schema_stats ('schema name');
--通过unlock_table_stats unlock_schema_stats 可以解锁


select
column_name,
num_distinct,
histogram, num_buckets,
to_char(LAST_ANALYZED, 'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from dba_tab_col_statistics
where table_name='EMP';
--查看表的各个列的统计信息收集情况。

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name in ('T1','T2');
select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from user_tables t where table_name='dba_objects';

BEGIN
DBMS_STATS.SET_TABLE_PREFS ( ownname =>'XXX', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');
END;
/


BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

DBMS_STATS.LOCK_TABLE_STATS(
ownname VARCHAR2,
tabname VARCHAR2
);