常用oracle数据库命令

来源：互联网发布：凸包算法代码编辑：程序博客网时间：2024/04/28 11:51

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

0 0