Oracle数据库相关操作

Oracle客户端程序名 instantclient
查看 select userenv('language') from dual

Oracle数据库相关操作
以下均在服务器CMD界面执行
一、本地登录
SQLPLUS 用户名/密码@服务名 as sysdba
#SQLPLUS xxxxxxx/xxxxxxx as sysdba
安装Client的,远程登录
//C:\Users\Orange>sqlplus system/Labsoftoracle1@192.168.1.253:1521/orcl
//>C:\Users\Orange> sqlplus mydb/mydb@127.0.0.1:1521/orcl
二、删除用户
drop user 用户名 cascade
#drop user xxxxxxx cascade
三、删除表空间
DROP TABLESPACE ORCL_DATA INCLUDING CONTENTS AND DATAFILES;
删除临时表空间（可选）
DROP TABLESPACE ORCL_TEMP INCLUDING CONTENTS AND DATAFILES;
四、创建表空间
CREATE TABLESPACE ORCL_DATA
DATAFILE ‘D:\app\DataSpace\ORCL_DATA.DBF’ SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
创建临时表空间（可选）
CREATE TEMPORARY TABLESPACE ORCL_TEMP
TEMPFILE ‘D:\app\DataSpace\ORCL_TEMP.DBF’ SIZE 80M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M;

五、创建用户
create user 用户名 identified by trm default tablespace ORCL_DATA temporary tablespace ORCL_TEMP
给用户赋予权限: grant dba to 用户名 with admin option;
例子: create user xxxxxxx identified by trm default tablespace ORCL_DATA temporary tablespace ORCL_TEMP; grant dba to xxxxxxx with admin option;
六、解锁
alter user 用户名 account unlock;
commit;
alter user xxxxxxx account unlock;
commit;

七、登录
conn 用户名/密码

sys连接
connect sys/sys as sysdba;
修改密码
alter user 用户名 identified by 新密码;commit;
alter user xxxxxxx identified by xxxxxxx;commit;

alter user soctt account lock; // 把 scott用户锁定

alter user scott account unlock; //把scott用户解锁

整库的导出(很慢,一般不用)
exp system/password full=y
八、按用户导出相应数据库
exp ORATDIKO/sr2019N@ORCL owner=ORATDIKO file=D:\base2022011901-oracle.dmp
九、按用户导入相应数据库
imp ORATDIKO/sr2019N@ORCL fromuser=ORATDIKO touser=ORATDIKO file=D:\base20220119-oracle.dmp ignore=y
十、按表导出,用tables参数指定导出的表,如果多个表用逗号分隔
exp userName/password file=a.dmp tables=t_account,a_area
十一、按表导入
imp userName/password file=a.dmp tables=t_account,a_area

Oracle服务相关操作

lsnrctl status 查看监听状态
lsnrctl start 启动监听
lsnrctl stop 停止监听
show con_name pdbs; 查看PDB