数据库迁移命令备忘(二)
in:/home/oracle/app/oracle11g/bin
删除用户，及删掉使用的进程
1.drop user mdbadmin cascade;
2.select username ,sid,serial# from v$session
3.alter system kill session '31,17'
Dblink创建
create database link ITILDB_LINK
connect to MDBADMIN
IDENTIFIED BY "ca1234"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.64.96.76)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = mdb)
)
)';
查看 oracle内存使用情况
select a.sid,b.name,a.value from v$sesstat a,v$statname b
where (b.name like '%uga%' or b.name like '%pga%') and a.statisti
c# = b.statistic#
order by sid
函数的创建
CREATE OR REPLACE FUNCTION "DATE_TO_SEC" (strDate IN CHAR)
--将日期型转换为整型秒
RETURN INT
AS
ret NUMBER;
iDate DATE;
tDate varchar2(30);
BEGIN
if(length(strDate))>=18 then
tDate:='YYYY-MM-DD HH24:MI:SS';
elsif (length(strDate))>7 then
tDate:='YYYY-MM-DD';
else
tDate:='YYYY-MM';
end if;
iDate:=TO_DATE(strDate,tDate);
ret := (iDate-TO_DATE('1970-1-1 8:0:0','YYYY-MM-DD HH24:MI:SS'))*(24*60*60);
RETURN ret;
END ;
物化视图的创建
create materialized view COGNOS_AL_CJS_MV
refresh complete on demand
as
select distinct
w.object_id 变更ID,
sec_to_date(t.open_date) 时间,
get_per_site_name(w.ASSIGNEE) 单位,
GET_PER_DEPT_NAME(w.ASSIGNEE) 部门,
GET_analyst(w.ASSIGNEE) 经手人ID,
GET_person(w.ASSIGNEE) 经手人,
get_person(w.group_id) 经手组,
w.ASSIGNEE ID,
t.status 类型
from chg t, wf w
where t.id = w.object_id
and t.status <> 'CL'
普通视图的创建
create or replace view cognos_fissue_v as
select
t.parent parent_id,
t.ref_num 任务单号,
t.z_string7 任务名称,
(select last_name from ca_contact where contact_uuid=t.z_string56) 任务负责人,
t.z_string15 负责人电话,
t.z_string8 功能需求编号,
(select name from ca_site where id=t.z_site1) 功能需求部门,
t.z_string10 主要业务功能描述,
t.status 状态,
sec_to_date(t.open_date) 建单时间,
sec_to_date(t.close_date) 关单时间,
t.z_string38 功能开发负责人
from issue t where t.category='CHG993'
查看某用户表空间的大小
创建表空间的大小
SELECT
DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM
DBA_TABLESPACES TS
查看某表的列数
select count(*) from dba_tab_columns where owner='' and table_name='';
select count(1)
from user_col_comments
where table_name = upper('xs_rxb');
扩充使用中的表空间的大小
Oracle字符集设置
客户端&服务端
Oracle中执行存储过程，遇见创建表时报错：权限不足(已经是dba权限)，解决方法
grant create any table to mdbadmin;
oracle判断表是否存在
--判断表是否存在，如果存在则删除
declare
num number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'EMP' and OWNER='SCOTT';
if num=1 then
execute immediate 'drop table EMP';
end if;
end;
/
两个库之间通过比较用户对象进行静态的统一
查看job的运行情况
select job, last_date, las