主要事理于2011、2013年,Oracle11g
1、设置变量
set timing on
alter session enable parallel dml;
alter session enable parallel query;
alter session enable parallel ddl;
alter session set workarea_size_policy=manual ;
alter session set sort_area_size=209715200 ;
alter session set sort_area_retained_size=209715200 ;
alter session set hash_area_size=209715200;
alter session set db_file_multiblock_read_count=64;
alter session set optimizer_mode=all_rows;
--alter session set events '6550 trace name ERRORSTACK level 3';
--alter session set events '10035 trace name context forever, level 3';
set arraysize 1000 head off termout off
exec admin_set_module('DAY_HANGAME','CRON');
exec SP_DW_DAY_HANGAME(to_char(sysdate-1,'mmdd'));
spool off
exit
2、Data Warehouse Best Practices for Oracle Database 11g :
Tips for a balanced system
Total throughput = # cores X 200MB
Use 1 HBA port per CPU
Use 1 disk controller per HBA Port
Max of 10 physical disks per controller
Use more smaller drives (146GB or 300 GB
Use minimum of 4GB of Memory per core
Use RAID 1 with ASM
Interconnect bandwidth = IO subsystem bandwidth
Tips for the Staging Layer
Use external tables
Load using parallel DML stmts CTAS or IAS
Use data compression
Considering range partitioning fact table to enable partition exchange loads
Tips for the Foundation Layer
Partition fact tables to get partition pruning
Sub-partitions by hash to achieve partition wise joins
Use parallel execution to allow multiple process to work on large queries
Number of sub-partitions needs to be a power of 2 and should be greater than or equal to the DOP
Tips for the Access Layer
Partition fact tables to get partition pruning
Create bitmap indexes on all FK columns
Set STAR_TRANSFORMATION_ENABLED to true
Tips for System Management
Use Parallel Execution where appropriate
Take hourly AWR or statspack report
Use EM to do real-time system monitoring
Use Resource Manager to ensure necessary users get high priority on the system
Always have accurate Optimizer statistics
Use INCREMENTAL statistic maintenance or copy_stats to keep large partitioned fact table up to date in a timely manner
Set only the initialization parameters that you need to
3、oracle startup
1. Start an instance.
When Oracle starts an instance, it reads the server parameter file (SPFILE) or
initialization parameter file to determine the values of initialization parameters. Then,
it allocates an SGA, which is a shared area of memory used for database information,
and creates background processes.
2. Mount the database.
To mount the database, the instance finds the database control files and opens them. Control files
are specified in the CONTROL_FILES initialization parameter in the parameter file
used to start the instance. Oracle then reads the control files to get the names of the
database’s datafiles and redo log files
3. Open the database.
When you open the database, Oracle opens the online datafiles and redo log files. If a
tablespace was offline when the database was previously shut down, the tablespace
and its corresponding datafiles will still be offline when you reopen the database.
4、查oracle锁:
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
ORDER BY 1, 2;
alter system kill session 'sid, serial#'; --杀掉
5、更改时间格式
alter session set nls_date_format= 'yyyy-mm-dd hh24:mi:ss';
6、去重
select mp_type, orig_mp_type, class_id, plat_type
from(select mp_type, orig_mp_type, class_id, plat_type, row_number() over (partition by orig_mp_type,class_id,plat_type order by mp_type desc) rn from p_media_prod_map
) a where rn = 1;
delete from ts_action_software_def
where rowid not in (select rd
from (select rowid rd,
row_number() over(partition by software_name order by id desc) rn
from ts_action_software_def)
where rn = 1);
7、导入导出
exp rdd/rdd file=/tmp/rdd.dmp owner=rdd
imp rdd/rdd fromuser=rdd touser=rdd file=/tmp/rdd.dmp
8、输出字符集
export NLS_LANG=AMERICAN_AMERICA.UTF8 #linux
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK #windows
9、查表
select tab.table_name, comm.COMMENTS, tab.OWNER from all_tab_comments comm, all_tables tab where comm.table_name = tab.TABLE_NAME and comm.OWNER = tab.OWNER and tab.OWNER in {0} and tab.table_name in('FIN_ACCOUNTINFO') order by tab.OWNER, tab.TABLE_NAME
10、查列
select md.COLUMN_NAME,
md.COMMENTS,
md.data_type,
case
when cons.COLUMN_NAME is not null then
'Y'
end as column_key,
md.nullable
from (SELECT T.COLUMN_NAME,
T1.COMMENTS,
case
when t.DATA_PRECISION is not null then
T.DATA_TYPE || '(' || T.DATA_LENGTH || ',' ||
t.DATA_PRECISION || ')'
when t.DATA_LENGTH is not null then
T.DATA_TYPE || '(' || T.DATA_LENGTH || ')'
else
T.DATA_TYPE
end as data_type,
t.nullable,
t.COLUMN_ID,
t.TABLE_NAME,
t.OWNER
FROM ALL_TAB_COLUMNS T, ALL_COL_COMMENTS T1
WHERE T.TABLE_NAME = T1.TABLE_NAME
AND T.COLUMN_NAME = T1.COLUMN_NAME
AND T.OWNER = T1.OWNER
AND T.OWNER = '{0}'
AND T.TABLE_NAME = '{1}') md
left join all_cons_columns cons
on md.owner = cons.OWNER
and md.table_name = cons.TABLE_NAME
and md.column_name = cons.COLUMN_NAME
and cons.constraint_name like 'PK%'
order by md.COLUMN_ID
11、最新查询语句
select * from v$sqlarea t where t.SQL_TEXT like '%KPI_LEV2%'
order by t.LAST_ACTIVE_TIME desc
12、表空间
–查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
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
ORDER BY 1
–查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name
–查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
–查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;
13、列字符串连接
select province_id, WMSYS.WM_CONCAT(area_id) from area_info group by province_id