oracle数据基本操作,Oracle数据库基本操作

1.查询存在的主键约束

select segment_name,partition_name,tablespace_name from dba_extents where tablespace_name=upper(‘zjgltest‘);

2.查询(生成)要删除主键的sql

select ‘alter table ‘||owner||‘.‘||table_name||‘ drop constraint ‘||constraint_name||‘;‘ from dba_constraints where constraint_type in (‘U‘,‘P‘) and (index_owner,index_name) in (select owner,segment_name from dba_segments where tablespace_name=upper(‘MYDB‘));

3.删除主键约束

执行 2 中查询出来的sql 脚本。

如:

alter table MYDB.TROLE drop constraint TROLE_PK;

4.删除对应的表空间 ---> 先删除主键

drop tablespace 表空间名字 including contents and datafiles cascade constraints;

--

一.登录system用户

--创建表空间 (新建一个表空间就新建一个数据文件)

create tablespace yqzijin DATAFILE ‘F:appochildoradataorcl

yqzijin_data.dbf‘ size 2G AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED;

--新建用户

create user hxzjgl_20190416 identified by hxzjgl_20190416 default tablespace zijin;

grant dba to hxzjgl_20190416 ;

--删除用户

drop user zjgl cascade;

--导入

imp hxzjgl_20190416/[email protected] full=y ignore=y file=d:hxzjgl_20190416.dmp

imp jxbanksc/ [email protected] full=y file=G:201906162100.dmp

--导出

exp zjgl/[email protected] file=d:zjgl.dmp

-- 数据库用户解锁

1.登录system用户

再执行

alter user test account unlock; --test 用户

--创建用户(测试用户)create user mytest identified by mytest default tablespace users;grant dba to mytest;

1.查询存在的主键约束 select segment_name,partition_name,tablespace_name from dba_extents where tablespace_name=upper(‘zjgltest‘); 2.查询(生成)要删除主键的sql select ‘alter table ‘||owner||‘.‘||table_name||‘ drop constraint ‘||constraint_name||‘;‘ from dba_constraints where constraint_type in (‘U‘,‘P‘) and (index_owner,index_name) in (select owner,segment_name from dba_segments where tablespace_name=upper(‘MYDB‘)); 3.删除主键约束 执行 2 中查询出来的sql 脚本。 如: alter table MYDB.TROLE drop constraint TROLE_PK; 4.删除对应的表空间 ---> 先删除主键 drop tablespace 表空间名字 including contents and datafiles cascade constraints; -- 一.登录system用户 --创建表空间 (新建一个表空间就新建一个数据文件) create tablespace yqzijin DATAFILE ‘F:appochildoradataorcl yqzijin_data.dbf‘ size 2G AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED; --新建用户 create user hxzjgl_20190416 identified by hxzjgl_20190416 default tablespace zijin; grant dba to hxzjgl_20190416 ; --删除用户 drop user zjgl cascade; --导入 imp hxzjgl_20190416/[email protected] full=y ignore=y file=d:hxzjgl_20190416.dmp imp jxbanksc/ [email protected] full=y file=G:201906162100.dmp --导出 exp zjgl/[email protected] file=d:zjgl.dmp -- 数据库用户解锁 1.登录system用户 再执行 alter user test account unlock; --test 用户 --创建用户(测试用户)create user mytest identified by mytest default tablespace users;grant dba to mytest;

免费搭建微信查券返利机器人来轻松赚佣金