oracle常用操作
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
查看
alter user Scott account unlock;解锁用户
PATIENTDATA 指标统计报表排班
【推荐】还在用 ECharts 开发大屏?试试这款永久免费的开源 BI 工具!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步