0. 使用PL/SQL配置移植目录中的 database configuration assitistant 工具新建一个数据库实例,第一次的时候需要创建实力模板;
1. 用最高权限登录不同的数据库实例: sqlplus / as sysdba@DATA10;
2. 登录后创建用户:create user username identified by password;
3 .授予最高权限 : grant dba to username;
4. 导入数据库数据:IMP shopxx/shopxx@ORCL BUFFER=64000 FILE=D:\app\data0125.dmp full=y;注意区分大小写
导出数据库 EXP watershopxx/watershopxx@THERP BUFFER=64000 FILE=d:\app\data0125.DMP FULL=Y
远程导出 : EXP watershopxx/watershopxx@113.12.201.178:1521/THERP FILE=d:\app\0107.dmp FULL=Y
5.例子:创建表空间 create tablespace shopxx datafile 'F:\app\Administrator\oradata\CPDATABASE\SHOPXX.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
删除表空间
drop tablespace DEMOSPACE including contents and datafiles
6. 更改表所在空间 :alter table table_name move tablespace tablespace_name
7. 查询字段所在的表 select * from user_tab_columns where column_name='ITEM_DESC';
8.增加列:alter table ckp_subject add teacher_id int
删除列:alter table ckp_subject drop column subject_teacher
列改名:alter table cms_teacher rename column teacher_id to cms_teacher_id
9. 删除序列:delete sequence SQ_CMS_TEACHER
创建序列:CREATE SEQUENCE seq_itv_collection
INCREMENT BY 1 -- 每次加几个
START WITH 1399 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10;
10 drop user user_name cascade; 删除用户及其名下的所有表
11 select * from dba_data_files 查看表空间位置
create tablespace chenp datafile 'F:\APP\ADMINISTRATOR\ORADATA\CPDATABASE\CHENP.DBF' size 50m autoextend on next 50m maxsize 20480m extent management local;
11 查看完整性约束
select a.constraint_name, a.table_name, b.constraint_name
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name
and a.constraint_name = 'FK25E6B94F7C62EDF8'
12 查看主键
select * from user_constraints where constraint_name = 'AK_AK_KEY_2_CMS_ASCH_CMS_ASCH';
1. 用最高权限登录不同的数据库实例: sqlplus / as sysdba@DATA10;
2. 登录后创建用户:create user username identified by password;
3 .授予最高权限 : grant dba to username;
4. 导入数据库数据:IMP shopxx/shopxx@ORCL BUFFER=64000 FILE=D:\app\data0125.dmp full=y;注意区分大小写
导出数据库 EXP watershopxx/watershopxx@THERP BUFFER=64000 FILE=d:\app\data0125.DMP FULL=Y
远程导出 : EXP watershopxx/watershopxx@113.12.201.178:1521/THERP FILE=d:\app\0107.dmp FULL=Y
5.例子:创建表空间 create tablespace shopxx datafile 'F:\app\Administrator\oradata\CPDATABASE\SHOPXX.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
删除表空间
drop tablespace DEMOSPACE including contents and datafiles
6. 更改表所在空间 :alter table table_name move tablespace tablespace_name
7. 查询字段所在的表 select * from user_tab_columns where column_name='ITEM_DESC';
8.增加列:alter table ckp_subject add teacher_id int
删除列:alter table ckp_subject drop column subject_teacher
列改名:alter table cms_teacher rename column teacher_id to cms_teacher_id
9. 删除序列:delete sequence SQ_CMS_TEACHER
创建序列:CREATE SEQUENCE seq_itv_collection
INCREMENT BY 1 -- 每次加几个
START WITH 1399 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10;
10 drop user user_name cascade; 删除用户及其名下的所有表
11 select * from dba_data_files 查看表空间位置
create tablespace chenp datafile 'F:\APP\ADMINISTRATOR\ORADATA\CPDATABASE\CHENP.DBF' size 50m autoextend on next 50m maxsize 20480m extent management local;
11 查看完整性约束
select a.constraint_name, a.table_name, b.constraint_name
from user_constraints a, user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name
and a.constraint_name = 'FK25E6B94F7C62EDF8'
12 查看主键
select * from user_constraints where constraint_name = 'AK_AK_KEY_2_CMS_ASCH_CMS_ASCH';