JZTXT

Oracle 操作命令

发布时间 2023-11-16 12:10:17作者: KLAPT

切换到数据库用户： su - oracle

登录数据库  ：sqlplus / as sysdba 

查看实例PDB1 ：show pdbs;

设置会话  ：alter session set container=PDB1;

查看数据文件：select file_name from dba_data_files;

创建数据库空间  ：create tablespace orms_TBS datafile '/dddddd' size 1G autoextend on next 100M maxsize unlimited extent management local AUTOALLOCATE SEGMENT space MANAGEMENT AUTO;

创建索引表空间：create tablespace orms_TBS_INDEX datafile '/dddddd' size 1G autoextend on next 100M maxsize unlimited extent management local AUTOALLOCATE SEGMENT space MANAGEMENT AUTO;

创建临时表空间：create temporary tablespace tmp_data tempfile '/dddddd' size 1G reuse autoextend on next 100M maxsize unlimited ;

创建用户：create user orms identified by orms123 account unlock; /// create user hke identified by hke defaulte tablespace HKE_TBS temporary tablespace temp;

赋予角色：grant connect，resource to orms

分配权限
grant create procedure to hke with admin option;
grant create sequence to hke ;
grant create session to hke;
grant create synonym to hke;
grant create table to hke;
grant create trigger to hke;
grant create type to hke;
grant create view to hke;
grant unlimited tablespace to hke;
grant create job to hke;

grant select on V_$SQLAREA to hke;
grant select on V_$SESSION to hke;
grant select on V_$LOCKED_OBJECT to hke;
grant select on V_$PARAMETER to hke;
grant select on V_$PROCESS to hke;
grant select on V_$RECOVERY_FILE_DEST to hke;
grant select on DBA_FREE_SPACE to hke;
grant select on DBA_DATA_FILES to hke ；

创建逻辑目录：create or replace directory dp_dir as '/oradata'

赋予权限：grant read,write on directory dp_dir to ORMS;

 

修改库名 ：ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

修改原字段名： ALTER TABLE 表名 RENAME COLUMN 字段名 TO 字段名1;

添加一个字段：ALTER TABLE 表名 ADD 字段名 VARCHAR2(30);

查询当前登录用户下的所有表的名称  ：SELECT TABLE_NAME FROM USER_TABLES;

 

 

删除用户： drop user orms cascade 

删除tablespace : DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES

查询所有用户：select * from all_users;   select * from dba_users;

 

 


导出：expdp orms_uat/orms_uat_123@192.168.10.138:1521/pdb1 directory=DP_DIR dumpfile=orms_uat20231111.dmp logfile=orms_uat20231111.log SCHEMAS=orms_uat parallel=4 version=19.3.0.0;

 导入：impdp orms_uat/orms_uat_123@192.168.10.138:1521/pdb1 directory=DP_DIR dumpfile=orms_uat20231111.dmp logfile=orms_uat20231111.log SCHEMAS=orms_uat parallel=4 version=19.3.0.0;