Oracle EBS
获取组织（OU）对应的公司名称SELECT hou.organization_id,hou.default_legal_context_id
,hou.name ou
,gll.legal_entity_name company_name
FROM org_organization_definitionsood
,hr_operating_units hou
,hr_organization_information_v hoi
,gl_ledger_le_v gll
WHERE ood.operating_unit = hou.organization_id
AND hoi.org_information_context = 'Operating Unit Information'
AND ood.organization_id = hoi.organization_id
AND hou.default_legal_context_id = gll.legal_entity_id
AND hoi.org_information3 = gll.ledger_id
AND ood.organization_id = 81;
表空间
1、查看表空间的名称及大小
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;
2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
3、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
通过For循环对数据进行分批
-- set batch_id
v_count := 1;
FOR rec IN (SELECT ROWID FROM xxinv_item_imp_tab WHERE error_flag IS NULL ORDER BY organization_code,segment1)
LOOP
UPDATE xxinv_item_imp_tab
SET batch_id = ceil(v_count/200)
WHEREROWID = rec.ROWID;
v_count := v_count + 1;
END LOOP;
查询数据库对象的依赖关系
SELECT * FROM dba_dependencies dd WHERE upper(dd.name) = 'XXBOM_MBOM_IMPORT_PKG';
查看锁表进程并杀死
1、查找被锁表
SELECT dob.object_name table_name
,lo.session_id || ', ' || vss.serial# 删除号
,lo.locked_mode
,lo.session_id
,vss.serial#
,vss.action action
,vss.osuser osuser
,vss.logon_time
,vss.process ap_pid
,vps.spid db_pid
,vss.*
FROM v$locked_object lo, dba_objects dob, v$session vss, v$process vps
WHERE lo.object_id = dob.object_id
AND upper(dob.object_name) = 'xxxxxxxxxxxxxx' --MTL_SYSTEM_ITEMS_INTERFACE
AND lo.session_id = vss.sid
AND vss.paddr = vps.addr
ORDER BY 2, 3, dob.object_name
2、删除进程，如之前的“删除号”查找出的结果为“286, 2184”，则运行以下SQL
ALTER system kill session '286, 2184'
修改表列名
没有直接的方法改变。但是可以通过其他方法达到改变列名的目的。
例如：
表A结构如下：
ID(NUMBER) NAME(VARCHAR2(20)
------------------------------------
1 TOM
2 MIKE
3 JHON
将列名NAME改变为NAME1
方法1.列复制法
1.增加一个与NAME相同结构的字段NAME1
Alter table A add(NAME1 varchar2(20));
2.将NAME中的数据复制到NAME1中
Update A Set NAME1=NAME;
3.删除NAME列
Alter table A drop column NAME;
4.修改完成
方法2.表复制法
1.将表A改名
Alter table A rename to A1
2.创建新表并复制数据
Create table A(ID,NAME1) as Select * from A1
3.删除表A1
4.修改完成
通过上面两种方法，
重新检索表A结果如下：
ID(NUMBER) NAME1(VARCHAR2(20)
------------------------------------
1 TOM
2 MIKE
3 JHON
修改表主键
--修改主键
CREATE TABLE xxtest1(
user_id NUMBER NOT NULL,
user_name VARCHAR2(24) NOT NULL,
pass_word VARCHAR2(24) NOT NULL);
SELECT * FROM xxtest1;
ALTER TABLE xxtest1 ADD (CONSTRAINT PK_1 PRIMARY KEY(user_id));
ALTER TABLE xxtest1 DROP CONSTRAINT PK_1;
ALTER TABLE xxtest1 ADD (CONSTRAINT PK_2 PRIMARY KEY(user_name));
页:
[1]