--LINUX
du -sh * 查看各文件占用空间
du -h --max-depth=1 查看各文件夹占用空间
df -h 查看整体空间使用情况
--------------
DBCA
--
dbca -silent -createDatabase -templateName Data_Warehouse.dbc -gdbname MAZESIT -sid MAZESIT -systemPassword oracle -sysPassword oracle -characterSet ZHS16GBK
--表空间和用户(临时表空间类似)------------------------
CREATE [ TEMPORARY ] TABLESPACE TBS_BMK DATAFILE/TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\SIT\TBS_BMK01.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M; --创建表空间
ALTER TABLESPACE TBS_BMK add DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\SIT\TBS_BMK02.DBF' SIZE 100M AUTOEXTEND ON;--增加表空间数据文件
ALTER TABLESPACE TBS_BMK DROP DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\SIT\TBS_BMK02.DBF' --删除表空间文件
DROP TABLESPACE TBS_BMK INCLUDING CONTENTS AND DATAFILES; --????????????????????
CREATE USER xiaoming IDENTIFIED BY xm123 -- //创建用户xiaoming,设置密码为xm123
DEFAULT TABLESPACE TEST_DATA -- //指定默认表空间为test_data
TEMPORARY TABLESPACE TEST_TEMP; -- //指定临时表空间为test_temp
select * from dba_data_files; --查表空间数据文件明细
select * from dba_tablespaces; --查表空间明细
select * from dba_users ; 查用户和表空间关系
--数据库间批量数据导出/导入
exp username/pwd@tnsname FILE=数据文件名.dmp BUFFER=2000000 GRANTS=N TABLES=(表名列表逗号分隔)
imp username/pwd@tnsname FILE=数据文件名.dmp FULL=Y IGNORE=Y BUFFER=2000000
--spool,自动导出DOS程序详见“oracle自动导出DOS程序.bat”
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --修改日期格式
SET ECHO OFF NEWPAGE 0 SPACE 0 heading off linesize 10000 pagesize 0 trimspool ON termout off feedback off --设置参数
SPOOL D:\WORK_DOC\bank\CHARTSQL\20170912.TXT --开启导出
SELECT a.* from sym_tap_autochart_chart a order by 2,3 ; --导出数据1
...
SPOOL OFF --导出结束
--oracle造数方法
1、如果只是少量数据,powerdesign 生成(全部关联关系数据)或者PLSQL的生成测试数据(单表20W/小时)均可
2、如果数据量大,则可按照表数据逻辑编写SQL,类似结构如下,详细例子参考TBMK_MD_PRD_RELATION.sql和衍生数据脚本
insert into 目标表
with 依赖数据字段来源1 as (
....
),
....
tmp_rs AS (
select rownum as id ,ceil(dbms_random.value(0,t1.cnt)) tmp_mode
from dual
join (select COUNT(*) cnt from 依赖数据字段来源1 ) t1 on 1=1
CONNECT BY LEVEL <=需要产生数据量
)
select DATE '2015-12-01' data_date ,
...
from tmp_rs a1
JOIN 依赖数据字段来源1 A2 ON A1.ID = A2.ID
;
commit
;
-----插入附加信息表日数据start_date 到 end_date,使用DATE'2017-06-01'日期数据
declare
start_date date;
end_date date;
V_N_CNT INTEGER; --执行影响数据量
V_N_RETCODE varchar2(200);
V_V_RETDESC varchar2(200);
begin
start_date :=date'2017-01-01';
end_date :=date'2017-01-31';
FOR X IN (SELECT start_date + (LEVEL - 1) D_RUN_FIRST
FROM DUAL
CONNECT BY LEVEL <= ( end_date - start_date) + 1 )
LOOP
insert /*+ append */ into SYM_TBDW_CUST_INFO_APPEND
SELECT X.D_RUN_FIRST AS data_date,
cust_id,
cust_mgr_id,
cust_mgr_name,
orgno_mng,
dict_cust_level,
dict_flag_vip,
dict_flag_sensitive,
dict_flag_lawsuit,
credit_tot,
dict_invest_risk,
SYSDATE d_insert
FROM SYM_TBDW_CUST_INFO_APPEND
WHERE data_date=DATE'2017-06-01'
;
V_N_CNT := SQL%ROWCOUNT; --插入数据量
SYM_PKG_TOOLS.P_TASK_RUN_LOG(PI_V_TASK_ID => 'TBDW_CUST_INFO_APPEND',
PI_V_LEVEL => '0',
PI_V_LOG_LEVEL => '2',
PI_V_START_DT => TO_CHAR(X.D_RUN_FIRST, 'YYYYMMDD'),
PI_V_END_DT => TO_CHAR(X.D_RUN_FIRST, 'YYYYMMDD'),
PI_V_LOGCONTENT => ' 插入完成 ' ,
PI_N_RECORDS => V_N_CNT,
PO_N_RETCODE => V_N_RETCODE,
PO_V_RETDESC => V_V_RETDESC);
commit;
end loop;
commit;
end
;
/
--查看被锁的表?
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程?
SELECT sid, serial#, username, osuser FROM v$session;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address
;