ORACLE常用脚本总结

/ 技术 / 0 条评论 / 10浏览

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'

EXP，IMP导出 导入

exp   XXXXX/sa@feng full=n file=d:\BF.dmp  --导出 带数据

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

imp  XXXXX/sa@feng full=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

查所有用户名

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' resize 1000M; 
--设置已有表空间文件自增大小

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;

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;

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2018/08/24 17:39