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 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 --导入

--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'

--查找第一个列的名称
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