ORACLE常用脚本总结

--创建表空间
create tablespace namespacefeng2
datafile
'D:namespacefeng2.DBF' size 100M autoextend on next 30M maxsize unlimited
logging extent management local segment space management auto

--创建用户
create user test identified by salis default tablespace namespacefeng1

--为用户分配权限
grant dba to feng -- 管理员权限
grant resource to namespacefeng -- 开发者权限（建表||删除表||增删改查数据）
grant connect to namespacefeng -- 浏览者权限（查看）

--删除表空间
drop tablespace namespacefeng;

--删除表空间及数据库文件
drop tablespace namespacefeng2 including contents and datafiles;

--删除用户
drop user feng cascade;

select * from dba_data_files t where t.file_name = 'D:namespacefeng1'

--删除表空间
drop tablespace namespacefeng1 including contents and datafiles cascade constraints;

--EXP，IMP导出 导入
exp XXXXX/sa@fengfull=n file=d:BF.dmp --导出 带数据

exp XXXXX/sa@fengfull=n file=d:BF.dmp rows=n --导出空表

impfengfull=y file=d:BF.dmp --导入、

--数据泵导入导出

create or replace directory tempdumpdir as 'D:备份'; --创建数据泵目录

impdp dzywk/salis@orcl DIRECTORY=MYIMPFILE DUMPFILE=DZYWK_2017213_1112456.DMP LOGFILE=impDZYWK.log --导入

expdp CDDCJ_META/salis@orcl directory=tempdumpdir dumpfile=CDDCJ_META.DMP logfile=expCDDCJ_META.log version=10.2.0.1.0--导出

--Oracle查看用户下的表信息
select table_name,tablespace_name,temporary from
user_tables where tablespace_name = ''

--ORACLE查询字段信息
select column_name
from user_tab_columns where table_name= 'CQK_PHSS_ZHD' and column_name not like 'SM%'

--ORACLE主键触发器
BEGIN SELECT XL_CQS_CQJSB.NEXTVAL INTO :new.MBBSM FROM dual; END;

--ORACLE解决误删 数据库登陆不进去
SQL> conn sys/passwd as sysdba;

SQL>alter database open resetlogs

SQL>alter database datafile 11 offline drop

SQL>shutdown normal

SQL>startup mount

--ORACLE解决跨用户创建视图权限不足

GRANT CREATE ANY TABLE TO USER1;
GRANT SELECT ANY TABLE TO USER1;
GRANT COMMENT ANY TABLE TO USER1;
GRANT LOCK ANY TABLE TO USER1;
GRANT SELECT ANY DICTIONARY TO USER1;

--查所有用户名
select * from dba_users order by username

--查某用户下的表名
select table_name from dba_tables

--ORACLE查询字段信息
select *
from DBA_tab_columns where OWNER = '用户名' and table_name = ‘表名'

--查询某用户下视图信息
select * from DBA_views where OWNER = '用户名''

--查询某用户下函数信息
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='FUNCTION' and OWNER = '用户名''

--查询否用户下存储过程名
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' and OWNER = '用户名''

--查找主键名称
select column_name from user_cons_columns cu,user_constraints au where cu.constraint_name =
au.constraint_name and au.constraint_type = 'P' and au.table_name = 'AFTEST'

--根据表名查询此表全部字段和类型SQL版

select COlUMN_NAME as columnName,DATA_TYPE as columnType from information_schema.columns where table_name = 'tableName'

--根据表名查询此表全部字段和类型ORACLE版

select COlUMN_NAME as columnName,DATA_TYPE as columnType from DBA_tab_columns where OWNER = '{0}' and table_name = 'tableName'

--查找第一个列的名称
select column_name from user_tab_columns where table_name= 'AFTEST' and column_ID = '1'

--设置表的某列非空
ALTER TABLE "YDK"."AFTEST" MODIFY ( "TEST" NUMBER NOT NULL) ;

--设置表的某列为主键
ALTER TABLE "YDK"."AFTEST" ADD PRIMARY KEY ("TEST");

--建立序列
CREATE SEQUENCE "YDK"."XL_AFTEST"
MINVALUE 1 MAXVALUE 99999999999 INCREMENT
BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE

--建立ORACLE主键自增触发器
CREATE OR REPLACE TRIGGER "YDK"."ZJ_AFTEST"
BEFORE INSERT ON "YDK"."AFTEST"
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
BEGIN SELECT XL_AFTEST.NEXTVAL INTO :new.TEST FROM dual; END;

--改变已有表空间大小

--设置已有表空间文件自增大小

alter database datafile 'd:namespacefeng.dbf' autoextend on next 10m

--ORACLE设置级联删除

ALTER TABLE YSS_NJDD DROP CONSTRAINT FK_YSS_NJDD_YSXMBH;
ALTER TABLE YSS_NJDD ADD CONSTRAINT FK_YSS_NJDD_YSXMBH FOREIGN KEY (YSXMBH) REFERENCES YSS_SQB (YSXMBH) ON DELETE CASCADE;

--SQLSERVER设置级联删除

alter table BPS_KCDJ drop constraint FK_BPS_KCDJ_BPXMBH; alter table BPS_KCDJ add constraint FK_BPS_KCDJ_BPXMBH foreign key (BPXMBH) references BPS_XMXX(BPXMBH) ON DELETE CASCADE;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!