Oracle 操作命令
切换到数据库用户: 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;
查看字符集 : select userenv('language') from dual
查看锁表 :select * from V$locked_object ;
杀对应进程:alter system kill session '111'
查看表数据类型:select * from all_tab_columns where table_name=upper('表名') and owner=upper('数据库登录用户')
导入.sql 使用@命令,后面跟上.sql文件的路径和文件名,按回车键执行导入操作
查询导入的表:select table_name from user tables;
创建临时表:CREATE GLOBAL TEMPORARY TABLE (指定表空间只能指定临时表空间)
查询结果导入的新表:(1)INSERT INTO table_name(column1,column2,…) SELECT column1,column2… FROM existing_table_name;
(2)SELECT column1, column2,… INTO new_table_name[IN externaldb] FROM existing_table_name WHERE [condition];
(3)CREATE TABLE new_table_name AS SELECT column1,column2,… FROM existing_table_name WHERE [condition];
(4) create table ... ON COMMIT子句用于指定在事务提交时临时表中的数据的行为。
你可以选择 PRESERVE ROMS 保留数(默认选项),这意味着在事务提交后临时表中的数据将保留,
或者选择 DELETE ROMS 删除数据,这意味着在事务提后临时表中的数据将被删除。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2021-11-16 架构演进
2021-11-16 高并发 IO 模型
2021-11-16 git导出历史日志