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 删除数据,这意味着在事务提后临时表中的数据将被删除。

posted @   KLAPT  阅读(38)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
历史上的今天:
2021-11-16 架构演进
2021-11-16 高并发 IO 模型
2021-11-16 git导出历史日志
点击右上角即可分享
微信分享提示