oracle常用命令汇总

system
Wq1995119

 

1）创建用户：
Create user C##WQ identified by 123456;

2）为用户授权：
grant aq_administrator_role to C##WQ with admin option;
grant dba to C##WQ with admin option;
grant mgmt_user to C##WQ;
grant create materialized view to C##WQ;
grant create table to C##WQ;
grant global query rewrite to C##WQ;
grant select any table to C##WQ;
grant unlimited tablespace to C##WQ with admin option;

//drop user CESHIA cascade;(删除用户)
select sid,serial# from v$session where username='C##WQ'
alter system kill session '70,23621';
alter system kill session '133,62313';
drop user C##WQ cascade;

修改system密码
运行cmd命令行

?
录入
sqlplus /nolog? 无用户名登录

??
conn /as sysdba? 连接到数据本地数据

????
alter user system identified by password;?修改System 密码?为password

或者打开sqlplus软件:

imp CESHI/123456@orcl file=c:\qc.dmp log=dible_db full=y ignore=y

imp C##CESHI/123456@192.168.0.157:1521/orcl file=D:\158.dmp log=dible_db full=y ignore=y（远程倒入）

exp C##WQ/123456@192.168.0.73:1521/orcl file=d:\2019-1-12.dmp feedback=10000 buffer=10240000（远程倒出）

UPDATE SYS_USER set PASSWORD='12bbf9ea33d79e0da04df1fd042c5a3e66a521dc98688ef5e6e23a54' where id='t1'

33 19
WIN-B0GC1KLS48C

123456
-Dfile.encoding=UTF-8"

解决导出空表问题空表
oracle空表导出前，先通过此语句，生成所有空表的分配表空间的sql，把sql语句复制出来，全部执行一遍就可以了

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
exp c##bskj/123456@172.24.240.116:1521/orcl file=c:\all.dmp statistics=none

dbms_lob.substr(remarks)
select ID, VN_ID, PC_NAME, PA_NAME, NAME, FILE_NO, UPDATE_COUNT, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, DEL_FLAG, USEABLE, COMPANY_ID, SDA_NAME, dbms_lob.substr(remarks), INDEX_TEMP_NAME ,REVISION_DATE from QC_SD_INDEX_TEMP

exp c##nis/yanfabu123@192.168.0.79:1521/orcl tables=(AE%) rows=n file=C:\Users\Administrator\Desktop\ae.dmp 导出什么类型开头的空表
exp c##nis/yanfabu123@192.168.0.79:1521/orcl tables=(AE%) file=C:\Users\Administrator\Desktop\ae.dmp 导出表数据

查看哪些类型的表
SELECT
a.TABLE_NAME,b.COMMENTS
FROM
user_tables a,user_tab_comments b
WHERE
a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME like 'AE_%'
ORDER BY
TABLE_NAME
查看

exp c##test/123456@192.168.0.178:1521/orcl tables=(ae_sys_options,ae_sys_aequestion,ae_sys_questions,ae_sys_content) file=E:\1221.dmp

--处理表空间不足 ORA-01658
alter tablespace USERS add datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' size 20000M;
ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' RESIZE 1024M;
ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
update ae_rep_info set PAT_YEAR = replace(PAT_YEAR,'岁',''); 批量替换字段中的数据 把字段中的岁，改完空
update ae_sys_options set name = replace(name,'院感科','院感-预报科');
update ae_sys_content set HTML = replace(HTML,'院感科','院感-预报科');

update ae_sys_options set name = replace(name,'药学部','药剂科');
update ae_sys_content set HTML = replace(HTML,'药学部','药剂科');

update ae_sys_options set name = replace(name,'保卫科','安防科');
update ae_sys_content set HTML = replace(HTML,'保卫科','安防科');

update ae_sys_options set name = replace(name,'设备科','设备物资供应科');
update ae_sys_content set HTML = replace(HTML,'设备科','设备物资供应科');

update ae_sys_aename set NAME='1'||NAME