知识库总目录: No.0 Web开发知识库
(不断累积中)
某些问题可能有多种解决方案,文中一般仅提供一种。
若有谬误或不全的,望大家一起补充下:)
问题 | 解决方式 |
导出dmp | exp expuser/password file=/dmp/mydb20081030.dmp |
导入dmp | imp impuser/password file=/dmp/mydb20081030.dmp fromuser=expuser touser=impuser |
快速备份某个表 | create table saa_usergrade_20111214 as select * from saa_usergrade; |
快速删除某表所有数据(无回滚、吃水线归零) | TRUNCATE table dbName.tableName; |
快速将两个同构库之间某表的数据进行迁移 | sys用户进行如下操作(将B.tablename中数据灌至A.tablename): insert /*+append*/ into A.tablename NOLOGGING select * from B.tablename; |
删表字段 | alter table tableName set unused( unusedCol1); alter table tableName set unused( unusedCol2); alter table tableName drop unused columns ; |
导数SQL | SELECT CASE SEX WHEN 'M' THEN '1' WHEN '1' THEN '1' WHEN '9' THEN '9' WHEN '3' THEN '2' WHEN 'F' THEN '2' END FROM PrpDuserSub WHERE flag = 'old'; |
创建实例间Link | -- Create database link create database link TEST connect to TRANSFER identified by TRANSFER using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.22.222)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb) ) )'; |
查看oracle版本 | 1)用客户端连接到数据库,执行select * from v$instance 查看version项 2)select * from product_component_version 3)或查询V$VERSION查看组件级信息 |
10G修改Sys密码 | 到服务器上 sqlplus "/ as sysdba" 然后 alter user sys identified by 密码 |
给A用户调试存储过程的权限 | update user$ set name='B' where name='A'; alter system checkpoint; alter system flush shared_pool; |
将用户名称由A改为B并立即生效 | grant debug any procedure to A; GRANT debug any procedure, debug connect session TO A;
|
ORA-12170 | 修改服务器listener.ora中HOST的值为实际IP(可能会默认配成机器名,但客户端不一定能访问到) |