ORACLE 数据库操作

/*第1步：创建临时表空间 */
CREATE TEMPORARY TABLESPACE NAME_TEMP
TEMPFILE ‘name_temp.dbf’
SIZE 1024m
AUTOEXTEND ON
NEXT 1024M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

/*第2步：创建数据表空间 */
CREATE TABLESPACE NAME_DATA
DATAFILE ‘name_data.dbf’
SIZE 1024m
AUTOEXTEND ON
NEXT 1024M MAXSIZE UNLIMITED
ONLINE
LOGGING
FORCE LOGGING
EXTENT MANAGEMENT LOCAL;

/*第3步：创建用户并指定表空间 */
CREATE USER OracleName IDENTIFIED BY OraclePassword
DEFAULT TABLESPACE NAME_DATA
TEMPORARY TABLESPACE NAME_TEMP;

/*第4步：给用户授予权限 */
GRANT CONNECT,RESOURCE,DROP ANY TABLE,CREATE TABLESPACE,DROP TABLESPACE TO OracleName;

/*第5步：给用户授予创建视图权限 */
grant create any view to OracleName;

/查找读写权限位置/
select * from dba_directories;
grant read,write on directory DATA_PUMP_DIR to OracleName;

/*导出dmp文件两种方式 */
exp OracleName/OraclePassword@172.16.170.44/orcl file=D:/OracleName.dmp
expdp OracleName/OraclePassword@orcl DIRECTORY=DATA_PUMP_DIR schemas=OraclePath dumpfile=OracleName.dmp;

/导入存在权限问题可执行解除限制/
grant unlimited tablespace to C##ZHZF4_20200813

/*导入dmp文件两种方式 */
imp OracleName/OraclePassword@orcl full=y file=F:\OracleName.dmp ignore=y
impdp ZHZF4_20200613/ZHZF4_20200613 table_exists_action=replace directory=DATA_PUMP_DIR file=ZHZF4_20191021.dmp

/*查找报错表权限问题 /
select cu. from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘P’ and au.constraint_name=‘SYS_C0021447’;

//删除数据库
drop user ZHZF_XJ cascade;

//删除数据库表空间
drop tablespace zhzf_xj_xbkf_DATA including contents and datafiles cascade constraint;
drop tablespace zhzf_xj_xbkf_TEMP including contents and datafiles cascade constraint;

//查看用户列表
select username from dba_users;
select * from dba_users;

//查找数据库编码格式
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=‘NLS_CHARACTERSET’;
select userenv(‘language’) from dual;

//修改用户密码
alter user C##ZHZF4_20200803 identified by ZHZF4_20200803

//修改数据库密码
sqlplus /nolog
conn /as sysdba
alter user zhzf_yn_2019 identified by zhzf_yn_2019

//为用户解锁
alter user zhzf_yn_2019 account unlock

//通过时间节点恢复数据库删除的数据
flashback table PYKH_STAFF to timestamp to_timestamp(‘2020-06-22 10:00:00’,‘yyyy-mm-dd hh24:mi:ss’);
//因为未启用行移动功能 不能闪回表
alter table PYKH_STAFF enable row movement;

//查看oracle服务器端的字符集
select userenv(‘language’) from dual;
//本地字符集
select * from V$NLS_PARAMETERS;
查看是否对应 如果不对应配置电脑环境变量
设置变量名：NLS_LANG，变量值与oracle服务器端查到的值一致。
重启plsql