正文
oracle视图如何迁移 oracle视图传入参数
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
oracle中怎样将视图数据传到表中
直接把select出来的结果插入表中
insert into total(表) select total(试图列) from view1
相关问答
Q1: 如何将ORACLE数据库数据文件迁移到其它目录
您好，很高兴为您解答。
源数据库数据文件位置：/u01/oradata/orcl
实验中想把数据文件迁移到的位置：/u01/oradata/orcl_test
源数据库数据文件位置：
sys@ORCL select file_name from dba_data_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL col member for a30
sys@ORCL select member from v$logfile ;
MEMBER
------------------------------
/u01/oradata/orcl/redo01.log
/u01/oradata/orcl/redo02.log
/u01/oradata/orcl/redo03.log
需要移动所有的数据文件，采用alter database 方法
1、关闭数据库
sys@ORCL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2、移动数据文件到/u01/oradata/orcl_test目录
[oracle@ora10gserv orcl]$ pwd
/u01/oradata/orcl
[oracle@ora10gserv orcl]$ mv * /u01/oradata/orcl_test/
[oracle@ora10gserv orcl]$ ls
[oracle@ora10gserv orcl]$ cd /u01/oradata/orcl_test/
[oracle@ora10gserv orcl_test]$ ls
control01.ctl control03.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
3、修改控制文件位置
[oracle@ora10gserv /]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL conn /as sysdba
Connected to an idle instance.
SQL startup nomount
ORACLE instance started.
Total System Global Area 465567744 bytes
Fixed Size 1220024 bytes
Variable Size 146801224 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
SQL create pfile='/u01/initorcl.ora' from spfile;
File created.
SQL host vi /u01/initorcl.ora
orcl.__db_cache_size=314572800
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=0
*.audit_file_dest='/dba/admin/orcl/adump'
*.background_dump_dest='/dba/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='/u01/oradata/orcl_test/control01.ctl','/u01/oradata/orcl_test/control02.ctl','/u01/oradata/orcl_test/control03.ctl'
*.core_dump_dest='/dba/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain='lsf.com.cn'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/dba/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch_orcl'
*.open_cursors=300
*.pga_aggregate_target=154140672
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2000
*.sga_target=463470592
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/dba/admin/orcl/udump'
SQL shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL create spfile from pfile='/u01/initorcl.ora';
File created.
4、启动数据库到mount状态，更改数据文件位置
idle startup mount
ORACLE instance started.
Total System Global Area 465567744 bytes
Fixed Size 1220024 bytes
Variable Size 146801224 bytes
Database Buffers 314572800 bytes
Redo Buffers 2973696 bytes
Database mounted.
idle alter database rename file '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/orcl_test/system01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/orcl_test/undotbs01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/orcl_test/sysaux01.dbf';
Database altered.
idle alter database rename file '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/orcl_test/users01.dbf';
Database altered.
5、继续接着第4步，更改联机日志文件位置
idle alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/orcl_test/redo01.log';
Database altered.
idle alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/orcl_test/redo02.log';
Database altered.
idle alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/orcl_test/redo03.log';
Database altered.
6、打开数据库
idle alter database open;
Database altered.
idle conn /as sysdba
Connected.
sys@ORCL select file_name from dba_data_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/system01.dbf
/u01/oradata/orcl_test/undotbs01.dbf
/u01/oradata/orcl_test/sysaux01.dbf
/u01/oradata/orcl_test/users01.dbf
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL col member for a40
sys@ORCL select member from v$logfile;
MEMBER
----------------------------------------
/u01/oradata/orcl_test/redo01.log
/u01/oradata/orcl_test/redo02.log
/u01/oradata/orcl_test/redo03.log
sys@ORCL col name for a50
sys@ORCL select name from v$controlfile;
NAME
--------------------------------------------------
/u01/oradata/orcl_test/control01.ctl
/u01/oradata/orcl_test/control02.ctl
/u01/oradata/orcl_test/control03.ctl
7、临时文件的处理
由于控制文件不记录临时文件的信息，所以不能使用alter database rename file 命令完成，只能删除掉原来的再创建一个
sys@ORCL create temporary tablespace temp02 tempfile '/u01/oradata/orcl_test/temp02.dbf' size 200m autoextend on extent management local uniform size 1m;
Tablespace created.
sys@ORCL alter database default temporary tablespace temp02;
Database altered.
sys@ORCL drop tablespace temp including contents and datafiles;
Tablespace dropped.
sys@ORCL select file_name from dba_temp_files;
FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/temp02.dbf
[oracle@ora10gserv orcl_test]$ ls -l
total 1427572
-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 2 00:20 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:20 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:15 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 2 00:15 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Aug 2 00:20 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Aug 2 00:20 system01.dbf
-rw-r----- 1 oracle oinstall 209723392 Jul 21 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug 2 00:20 temp02.dbf
-rw-r----- 1 oracle oinstall 419438592 Aug 2 00:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug 2 00:15 users01.dbf
[oracle@ora10gserv orcl_test]$ rm -rf temp01.dbf
sys@ORCL select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP02
SYS TEMP02
SYSTEM TEMP02
DBSNMP TEMP02
SYSMAN TEMP02
OUTLN TEMP02
MDSYS TEMP02
ORDSYS TEMP02
CTXSYS TEMP02
ANONYMOUS TEMP02
EXFSYS TEMP02
DMSYS TEMP02
WMSYS TEMP02
XDB TEMP02
ORDPLUGINS TEMP02
SI_INFORMTN_SCHEMA TEMP02
OLAPSYS TEMP02
MDDATA TEMP02
DIP TEMP02
TSMSYS TEMP02
20 rows selected.
如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】
希望我的回答对您有所帮助，望采纳！
~ O(∩_∩)O~
Q2: oracle数据库，不同实例，如何做用户迁移？
简单oracle视图如何迁移的说一下过程oracle视图如何迁移：
首先要创建迁移的用户和表空间oracle视图如何迁移；
备份要迁移的数据；
将要迁移的数据表空间remove到新建的表空间中即可；
如果oracle视图如何迁移你只需要用户访问数据oracle视图如何迁移，直接授权就行；(?)
如果要迁移表空间，还有很多事项要注意，网络上有很多资料；
最后删除之前的表空间和不要的用户。
Q3: 用oracle sql developer进行数据迁移，从mysql数据库迁移到oracle数据库，视图没有迁移成功。
我前段时间做过这样的迁移，mysqltooracle 这个工具应该可以帮到你，但是迁移过去可能还是会有好多问题，你要自己慢慢的改。
Q4: 怎么将oracle视图同步到sqlserver2008视图
如果你导入到oracle里变成表，数据虽然现在都一样
但实际上已经没法随着表里的数据变化而变化了
不行你就把sqlserver的建立视图脚本拿出来，有语法错误的话，改成oracle的就好了
oracle视图如何迁移的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于oracle视图传入参数、oracle视图如何迁移的信息别忘了在本站进行查找喔。
