精华内容
下载资源
问答
  • 详细的 Oracle 创建用户 删除用户 创建表空间 删除表空间 数据库导入导出语句 复制直接可用
  • Oracle 建库脚本导出

    千次阅读 2014-03-30 10:50:52
    相信大多数人都碰到过如何从oracle导出建库脚本的问题,这事说起来其实不难,但挺麻烦的。总体而言有两种方式: 1、通过dbms_metadata包导出。最简单的方式如下: SELECT DBMS_METADATA.GET_DDL('TABLE','...

    相信大多数人都碰到过如何从oracle导出建库脚本的问题,这事说起来其实不难,但挺麻烦的。总体而言有两种方式:


    1、通过dbms_metadata包导出。最简单的方式如下:


    SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE', 'SCOTT') FROM dual;
    


    这样就得到了SCOTT用户下EMPLOYEE的建表脚本:


    CREATE TABLE "SCOTT"."EMP" 
       (	"EMPNO" NUMBER(4,0), 
    	"ENAME" VARCHAR2(10), 
    	"JOB" VARCHAR2(9), 
    	"MGR" NUMBER(4,0), 
    	"HIREDATE" DATE, 
    	"SAL" NUMBER(7,2), 
    	"COMM" NUMBER(7,2), 
    	"DEPTNO" NUMBER(2,0), 
    	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"  ENABLE, 
    	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
    	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS" 


    但这样得到的脚本是不完全的,没有包括表上面的索引和注释,所以还需要继续获取每个索引的DDL:


    SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_EMP_JOB', 'SCOTT') FROM dual;
    


    这就比较麻烦了,一般要从dba_indexes中获取表上所有索引的名称,然后循环调用。这其中还要排除掉主键,因为主键定义在表DDL已经生成了,这又需要从dba_constraints中提出主键并排除。另外一个问题是上面的语句太“完整”了,一般情况下我们其实希望得到一个简洁的结果,去掉没必要关心的参数。要达到这种效果,需要使用DBMS_METADATA.SET_TRANSFORM_PARAM方法,例如如果我们希望把段相关的信息全部去掉,就在GET_DDL之前调用:


    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
    


    然后GET_DDL的输出结果就变成这样:

     CREATE TABLE "SCOTT"."EMP" 
       (	"EMPNO" NUMBER(4,0), 
    	"ENAME" VARCHAR2(10), 
    	"JOB" VARCHAR2(9), 
    	"MGR" NUMBER(4,0), 
    	"HIREDATE" DATE, 
    	"SAL" NUMBER(7,2), 
    	"COMM" NUMBER(7,2), 
    	"DEPTNO" NUMBER(2,0), 
    	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE, 
    	 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
    	  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
       ) 
     

     除了上例的SEGMENT_ATTRIBUTES外,还有很多对象类型可以设置,比如TABLESPACE、STORAGE、CONSTRAINTS_AS_ALTER等等。可以控制相应的参数是否应该被生成出来。还有的时候,我们需要改变SCHEMA的名称,比如建表脚本中我们希望用SCOTT2替换SCOTT,这当然可以用文本编辑器来做到,但对于一个几千张表、索引、函数DDL组成的文件,你真的有信心能替换正确吗?还有一些时候我们不希望吧存储相关的信息抹掉,而是进行替换,比如将USERS表空间的表创建到其他表空间上,等等。上面这些都能做到,通过相应的DBMS_METADATA.SET_REMAP_PARAM、DBMS_METADATA.ADD_TRANSFORM、 DBMS_METADATA.SET_TRANSFORM_PARAM等函数,可以实现转换SCHEMA、TABLESPACE等。一个简单的切换SCHEMA例子如下:


    DECLARE
      V_TYPE VARCHAR2( 50);
      V_SCHEMA VARCHAR2( 50);
      V_NEWSCHEMA VARCHAR2( 50);
      V_TABLENAME VARCHAR2( 50);
     
      V_META_HNDL NUMBER;
      V_TRAN_MDFY_HNDL  NUMBER;
      V_TRAN_DDL_HNDL  NUMBER;
    BEGIN
      V_TYPE:='TABLE';    --类型是表
      V_SCHEMA:= 'SCOTT'; --原始SCHEMA名称
      V_NEWSCHEMA:= 'SCOTT1';  --要转换的目标SCHEMA名称
      V_TABLENAME:= 'EMP';  --表名称
     
      V_META_HNDL:=DBMS_METADATA.OPEN (V_TYPE); 
     
      DBMS_METADATA.SET_FILTER(V_META_HNDL, 'SCHEMA',V_SCHEMA);
      DBMS_METADATA.SET_FILTER(V_META_HNDL, 'NAME',V_TABLENAME);
    
      V_TRAN_MDFY_HNDL  := DBMS_METADATA.ADD_TRANSFORM (V_META_HNDL, 'MODIFY');
      DBMS_METADATA.SET_REMAP_PARAM(V_TRAN_MDFY_HNDL,'REMAP_SCHEMA' ,V_SCHEMA,V_NEWSCHEMA);
     
      V_TRAN_DDL_HNDL  := DBMS_METADATA.ADD_TRANSFORM (V_META_HNDL, 'DDL');
    
      DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL, 'SQLTERMINATOR',TRUE );
     
      IF(V_TYPE = 'TABLE') THEN
        DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL, 'SEGMENT_ATTRIBUTES',FALSE );   
        DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL, 'PARTITIONING',FALSE );
        DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL, 'STORAGE',FALSE );
        DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL,'CONSTRAINTS_AS_ALTER' ,FALSE);
        DBMS_METADATA.SET_TRANSFORM_PARAM(V_TRAN_DDL_HNDL, 'TABLESPACE',FALSE );
      END IF;
    
      DBMS_OUTPUT.PUT_LINE( DBMS_METADATA.FETCH_CLOB(V_META_HNDL));
     
    END;
    /
    


    总体而言,使用DBMS_METADATA包来创建DDL的话,四个字概括:繁琐低效。如果针对少量表还好,大不了就用最简单方式生成出来手工改下好了,但如果针对一个大库,这就极其痛苦了,你需要从dba_tables、dba_indexes等字典表中先取得要生成的对象名称,然后逐个调用函数,这其中还要判断各种依赖关系,比如外键关联造成的创建顺序等等。这里我们介绍第二种方法:

    2、使用expdp/impdp用impdp从expdp的导出文件生成sql脚本很简单,并且提供诸多选项可以自动转换表名称、SCHEMA、表名等,相当方便。比如:


    impdp / directory=dmp_output dumpfile=db.dmp  sqlfile=db.sql remap_schema=scott:scott1  remap_tablespace=users:users1 


    这样就生成了一个建库的脚本,并且其中所有scott自动转换成scott1,表空间users自动转换成users1。但这个生成的脚本一般来说不是我们想要的结果,impdp/expdp主要是用来导入数据的,生成脚本只是一个附加的功能,并且生成的脚本实际上就是导入数据时执行的脚本(数据本身除外),所以其中包括了很多我们不需要的信息,典型的就是统计信息也会一起生成,例如生成的脚本里面会包括这样的sql:


    DECLARE I_N VARCHAR2(60); 
      I_O VARCHAR2(60); 
      c DBMS_METADATA.T_VAR_COLL; 
      df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; 
    BEGIN
      DELETE FROM "SYS"."IMPDP_STATS"; 
      i_n := 'PK_EMPLOYEE'; 
      i_o := 'OA'; 
      INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES ('I',5,2,I_N,NULL,NULL,I_O,3071715,19335,3071715,1,1,251060,2,614343,NULL,NULL,NULL,NULL,TO_DATE('2014-03-15 02:17:44',df),NULL);
    
      DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); 
      DELETE FROM "SYS"."IMPDP_STATS"; 
    END; 
    /

    作用是将源库上PK_EMPLOYEE这个主键的统计信息也一起导入到目标库中。观察impdp的输出:


    Processing object type DATABASE_EXPORT/SCHEMA/USER
    Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
    Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
    Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
    Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
    Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
    Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
    Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
    Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY


    这个输出结果告诉我们impdp都导出了一些什么对象类型,完整的对象类型列表可以从dba_export_objects中查询到。对于上面提到的统计信息,对应的是DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS。所以,如果我们希望只导出我们想要的简洁sql,可以用两种方式告诉impdp,include或exclude选项(这两个选项是互斥的,也就是说不能同时使用)。比如我们希望只导出建表及索引语句,可以这样写:


    impdp / directory=dmp_output dumpfile=db.dmp  sqlfile=db.sql remap_schema=scott:scott1 remap_tablespace=users:users1 include=DATABASE_EXPORT/SCHEMA/TABLE/TABLE,DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
    

    如果我们希望在结果中排除所有统计信息,那就这样:


    impdp / directory=dmp_output dumpfile=db.dmp  sqlfile=db.sql remap_schema=scott:scott1  remap_tablespace=users:users1 exclude=DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS,DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS


    等等。这样就能简单高效的得到我们需要的建库脚本了。唯一不足的是,里面还是会包括了存储相关的信息,比如建表的STORAGE子句,另外生成的脚本文件头几行ALTER SESSION SET EVENTS...语句对我们建库没什么用处,可以手工删除,其中有几个事件号比如25475,除了知道“Reserved for Rules Engine”,根本不清楚具体什么作用,还是删掉来得保险一点。

    展开全文
  • 1.安装Oracle数据库  2.配置Oracle数据库监听程序 Net configuration Assistant 点重新配置 其他都下一步即可 3.创建表空间 create tablespace 表空间名 datafile 'C:app\Administrator\orcl\xxxx1.dbf' size 50...

    1.安装Oracle数据库 

    2.配置Oracle数据库监听程序 Net configuration Assistant 点重新配置 其他都下一步即可

    3.创建表空间

    create tablespace 表空间名 datafile 'C:app\Administrator\orcl\xxxx1.dbf' size 50M

    autoextend on next 10M maxsize unlimited logging  segment space management auto.

    4.用SYSTEM以sysdba身份登录

    Create user xxx indentified by 密码 default tablespace 表空间名

    grant dba to xxx with admin option

    5.导出库(WINDOWS中的CMD内运行)

    exp 用户名/密码@192.168.1.1:1521/ORCL file=D:\1.dmp statistics=none log=D:\ORC_1.log owner=用户名

    导出库时有空表导不出来参照:https://mp.csdn.net/postedit/83932679

    6.导入库(WINDOWS中的CMD内运行)

    imp 用户名/密码@192.168.1.1:1521/ORCL file=D:\1.dmp fromuser=xxx用户名 touser=xxx用户名 statistics=none log=D:\ORC_1.log

    连接Oracle数据库比较好用的软件为Navicat Premium_11.2.7简体中文版

     

    展开全文
  • Oracle手工建库及配置监听

    千次阅读 2014-02-02 19:30:10
    [oracle@redhat4~]$ vi .bash_profile exportORACLE_SID=powter 使其生效并验证: [oracle@redhat4~]$ source .bash_profile [oracle@redhat4~]$ env |grep -i sid ORACLE_SID=powter 三、创建目录 [ora...

    一、关闭数据库

    二、修改SID

    [oracle@redhat4~]$ vi .bash_profile

    exportORACLE_SID=powter

    使其生效并验证:

    [oracle@redhat4~]$ source .bash_profile

    [oracle@redhat4~]$ env |grep -i sid

    ORACLE_SID=powter

    三、创建目录

    [oracle@redhat4admin]$ cd $ORACLE_BASE/admin/

    [oracle@redhat4admin]$ mkdir powter

    [oracle@redhat4admin]$ cd powter/

    [oracle@redhat4powter]$ pwd

    /u01/app/oracle/admin/powter

    [oracle@redhat4powter]$ mkdir {a,b,c,u}dump

    [oracle@redhat4oradata]$ cd $ORACLE_BASE/oradata

    [oracle@redhat4oradata]$ pwd

    /u01/app/oracle/oradata

    [oracle@redhat4oradata]$ mkdir powter

    到刚创建的powter目录下创建3个目录,模拟3个磁盘

    [oracle@redhat4 oradata]$ cd powter/

    [oracle@redhat4 powter]$ mkdirDisk{1,2,3}

    四、添加图形化界面信息

    修改/etc/oratab文件,添加要创建数据库的信息,这样在dbca中就可以看到此DB,并可以对此进行图形界面管理。

    [oracle@redhat4 powter]$ vi /etc/oratab

    追加:

    powter:/u01/app/oracle/product/10.2.0/db_1:N

     

    为了在终端可以打开图形界面,需要安装“Xmanager”,并且进行以下修改

    [oracle@redhat4~]$ w

     16:33:42 up 2 days, 12:38,  6 users, load average: 0.04, 0.05, 0.01

    USER     TTY     FROM              LOGIN@   IDLE  JCPU   PCPU WHAT

    root     pts/2   192.168.0.111    16:33    0.00s 0.02s  0.00s w

    root     :0      -                12:44   ?xdm? 29:45   0.24s /usr/bin/gnome-

    root     pts/3   192.168.0.111    12:44    2:54m 1.34s  1.09s rman

    root     pts/5   192.168.0.111    12:50    2:56m 0.08s  0.06s sqlplus   as sy

    root     pts/6   192.168.0.111    15:54    2:18  5.19s  0.00s /bin/sh -f /u01

    root     pts/1   :0.0             16:07   25:41  0.02s  0.00s -bash

    [oracle@redhat4~]$ export DISPLAY=192.168.0.111:0.0

    192.168.0.111属于(已安装Xmanager)客户端的IP(我这里是windows7中虚拟机虚拟网卡的IP)

    测试:

    [oracle@redhat4 powter]$ dbca

     

    可以看到,此时已经可以在dbca中看到powter数据库了。

    五、添加初始化文件

    [oracle@redhat4~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/

    [oracle@redhat4dbs]$ cat init.ora|grep -v ^#|grep -v ^$>initpowter.ora

    [oracle@redhat4dbs]$ ls

    hc_wolex.dat  init.ora        lkWOLEX     snapcf_wolex.f

    initdw.ora    initpowter.ora  orapwwolex spfilewolex.ora

    [oracle@redhat4dbs]$ vi initpowter.ora

    db_name=powter

    db_files = 80                                                        

    db_file_multiblock_read_count = 8                                   

    shared_pool_size = 3500000                                           

    log_checkpoint_interval = 10000

    processes = 50                                                       

    parallel_max_servers = 5                                             

    max_dump_file_size = 10240     

    global_names = TRUE

    control_files = (     /u01/app/oracle/oradata/powter/Disk1/ctl01.ctl,

    /u01/app/oracle/oradata/powter/Disk2/ctl02.ctl,

    /u01/app/oracle/oradata/powter/Disk3/ctl03.ctl)

     

    sga_max_size=300M

    sga_target=300M

    undo_tablespace=undotbs

    undo_management=auto

    六、创建数据库脚本

    (参见《Administrator'sGuide》第二章第7节“Step 7: Issue the CREATE DATABASEStatement”)

    [oracle@redhat4~]$ cd $ORACLE_BASE

    [oracle@redhat4oracle]$ gedit createdb_powter,sql

    CREATE DATABASE powter

       USER SYS IDENTIFIED BY oracle

       USER SYSTEM IDENTIFIED BY oracle

       LOGFILE GROUP 1 ('/u01/app/oracle/oradata/powter/Disk1/redo01_01.log',

                          '/u01/app/oracle/oradata/powter/Disk2/redo01_02.log') SIZE 100M,

               GROUP 2 ('/u01/app/oracle/oradata/powter/Disk1/redo02_01.log',

                          '/u01/app/oracle/oradata/powter/Disk2/redo02_02.log') SIZE 100M,

               GROUP 3 ('/u01/app/oracle/oradata/powter/Disk1/redo03_01.log',

                          '/u01/app/oracle/oradata/powter/Disk2/redo03_02.log') SIZE 100M

       MAXLOGFILES 200

       MAXLOGMEMBERS 5

       MAXLOGHISTORY 200

       MAXDATAFILES 100

       MAXINSTANCES 2

       CHARACTER SET US7ASCII

       NATIONAL CHARACTER SET AL16UTF16

       DATAFILE '/u01/app/oracle/oradata/powter/Disk1/system01.dbf' SIZE 325M REUSE

       EXTENT MANAGEMENT LOCAL

       SYSAUX DATAFILE '/u01/app/oracle/oradata/powter/Disk1/sysaux01.dbf' SIZE 325M REUSE

       DEFAULT TEMPORARY TABLESPACE tempts1

          TEMPFILE '/u01/app/oracle/oradata/powter/Disk1/temp01.dbf'

          SIZE 20M REUSE

       UNDO TABLESPACE undotbs

          DATAFILE '/u01/app/oracle/oradata/powter/Disk1/undotbs01.dbf'

          SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    执行脚本:

    [oracle@redhat4~]$ cd $ORACLE_BASE

    [oracle@redhat4oracle]$ sqlplus / as sysdba

    此处顺便创建spfile:

    SQL>create spfile from pfile;

     

    File created.

     

    以nomount状态启动数据库:

    SQL>startup nomount

    ORACLEinstance started.

     

    Total SystemGlobal Area  314572800 bytes

    FixedSize                  2020480 bytes

    VariableSize              67111808 bytes

    DatabaseBuffers          243269632 bytes

    RedoBuffers                2170880 bytes

     

    SQL> !ls

    admin        createdb_powter,sql~  log_archive_area  oraInventory

    createdb_powter,sql  flash_recovery_area   oradata           product

     

    SQL>@createdb_powter.sql

     

    Databasecreated.

    查看当前连接数据库的实例名

    SQL>select instance_name from v$instance;

     

    INSTANCE_NAME

    --------------------------------

    powter

    查看当前数据库开启状态

    SQL>select open_mode from v$database;

     

    OPEN_MODE

    --------------------

    READ WRITE

    运行两个脚本,创建必要的视图和过程、包等等。

    [oracle@redhat4admin]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/rdbms/admin

    [oracle@redhat4admin]$ sqlplus / as sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 21:25:30 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

     

    Connected to:

    OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL>@catalog.sql

    ……

    PL/SQLprocedure successfully completed.

     

    SQL>@catproc.sql

    ……

    PL/SQLprocedure successfully completed.

    七、创建SCOTT/TIGER练习用户

    跑utlsaml.sql脚本,以创建SCOTT用户

    [oracle@redhat4admin]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/rdbms/admin

    [oracle@redhat4admin]$ sqlplus / as sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 22:10:29 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

     

    Connected to:

    OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL> @utlsampl.sql

    Disconnectedfrom Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bitProduction

    With thePartitioning, OLAP and Data Mining options

    修改SCOTT用户的密码以及将其解锁

    SQL> show user 

    USER is"SYS

    SQL> alter user scott identified bytiger account unlock;

     

    User altered.

    以system用户连接跑pupbld.sql脚本

    [oracle@redhat4admin]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin

    [oracle@redhat4admin]$ sqlplus system/oracle

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 22:16:01 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

     

    Connected to:

    OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL> @pupbld.sql

    ……

    ● 另:也可以把另外一个数据库中的SCOTT用户导入到其他数据库中

    数据库open状态下,

    [oracle@redhat4~]$ exp scott/tiger owner=scott file=scott.dmp

    关闭当前数据库,更改SID

    SQL> shutdown immediate

    Databaseclosed.

    Databasedismounted.

    ORACLEinstance shut down.

    SQL> !

    [oracle@redhat4~]$ vi .bash_profile

    exportORACLE_SID=wolex

    [oracle@redhat4~]$ source .bash_profile

     

    [oracle@redhat4~]$ !sql

    sqlplus / assysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 23:42:19 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

    Connected to anidle instance.

     

    SQL> startup     

    ORACLEinstance started.

     

    Total SystemGlobal Area  314572800 bytes

    FixedSize                  2020480 bytes

    VariableSize             121637760 bytes

    DatabaseBuffers          188743680 bytes

    RedoBuffers                2170880 bytes

    Databasemounted.

    Databaseopened.

    查看SID为wolex的数据库中有多少个用户

    SQL> select username from all_users;

     

    USERNAME

    ------------------------------------------------------------

    DBSNMP

    TSMSYS

    DIP

    OUTLN

    SYSTEM

    SYS

     

    6 rowsselected.

     

    导入到当前(wolex)数据库中:

    SQL> !      

    [oracle@redhat4~]$ imp scott/tiger file=scott.dmp

    SQL> select username from all_users;

     

    USERNAME

    ------------------------------------------------------------

    SCOTT

    DBSNMP

    TSMSYS

    DIP

    OUTLN

    SYSTEM

    SYS

     

    6 rowsselected.

    尝试连接SCOTT/TIGER:

    SQL> conn scott/tiger

    Connected.

    SQL> show user   

    USER is"SCOTT"

    如果是远程导入导出的话,则不需要关闭任何一个数据库,导入导出时加入SID(数据库名?):

    exp scott/tiger@powter owner=scottfile=scott.dmp

    imp scott/tiger@wolex file=scott.dmp

    exppowter_test/powter_test@192.168.0.200:1521/powter owner=powter_testfile=powter_test.dmp

    在实例名为wolex的数据库中创建powter_test并且赋予相应的权限

    $ imppowter_test/powter_test@192.168.0.200:1521/wolex file=powter_test.dmp

     

     

    八、模拟远程登录数据库

    1、创建口令文件

    [oracle@redhat4dbs]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/dbs

    [oracle@redhat4dbs]$ orapwd file=orapwpowter password=oracle

    2、配置监听

    ● 图形化界面配置

    (1)Oracle NetManager

    [oracle@redhat4admin]$ netmgr

     

    (2)Oracle NetConfiguration Assistant

    [oracle@redhat4admin]$ netca

     

    2、代码行配置

    [oracle@redhat4admin]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/network/admin

    [oracle@redhat4admin]$ vi listener.ora

    在原来已经存在的listener监听中添加powter数据库(静态注册),再添加一个动态注册监听lsnr2

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = wolex)

          (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

          (SID_NAME = wolex)

        )

        (SID_DESC =

          (GLOBAL_DBNAME = powter)

          (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

          (SID_NAME = powter)

        )

      )

     

    LISTENER =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))

      )

     

     

    LSNR2 =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1522))

      )

    为listener监听添加服务器名(别名),lsnr2不添加

    [oracle@redhat4admin]$ vi tnsnames.ora

    POWTER_ALIAS =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))

        )

        (CONNECT_DATA =

          (SERVICE_NAME = powter)

        )

      )

     

    WOLEX_ALIAS =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))

        )

        (CONNECT_DATA =

          (SERVICE_NAME = wolex)

        )

      )

    测试别名能否ping通:

    [oracle@redhat4admin]$ tnsping powter_alias

     

    TNS Ping Utilityfor Linux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:41:50

     

    Copyright (c)1997, 2005, Oracle.  All rights reserved.

     

    Used parameterfiles:

     

     

    Used TNSNAMESadapter to resolve the alias

    Attempting tocontact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = powter)))

    OK (10 msec)

    启动监听:

    [oracle@redhat4~]$ lsnrctl start

     

    LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:37:33

     

    Copyright (c)1991, 2005, Oracle.  All rights reserved.

     

    TNS-01106:Listener using listener name LISTENER has already been started

    [oracle@redhat4~]$ lsnrctl status

     

    LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:37:40

     

    Copyright (c)1991, 2005, Oracle.  All rights reserved.

     

    Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1521)))

    STATUS of theLISTENER

    ------------------------

    Alias                     LISTENER

    Version                   TNSLSNR for Linux: Version10.2.0.1.0 - Production

    Start Date                20-JUL-2012 01:09:40

    Uptime                    0 days 0 hr. 27 min. 59 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    ListenerParameter File  /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

    Listener LogFile        /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

    ListeningEndpoints Summary...

     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))

    ServicesSummary...

    Service"powter" has 2 instance(s).

      Instance "powter", status UNKNOWN,has 1 handler(s) for this service...

      Instance "powter", status READY, has 1 handler(s)for this service...

    Service"powter_XPT" has 1 instance(s).

      Instance "powter", status READY,has 1 handler(s) for this service...

    Service"wolex" has 2 instance(s).

      Instance "wolex", status UNKNOWN,has 1 handler(s) for this service...

      Instance "wolex", status READY, has1 handler(s) for this service...

    Service"wolexXDB" has 1 instance(s).

      Instance "wolex", status READY, has1 handler(s) for this service...

    Service"wolex_XPT" has 1 instance(s).

      Instance "wolex", status READY, has1 handler(s) for this service...

    The commandcompleted successfully

    通过IP和端口号和实例名模拟远程连接

    [oracle@redhat4dbs]$ sqlplus sys/oracle@192.168.0.200:1521/powteras sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Fri Jul 20 01:28:28 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

     

    Connected to:

    Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

    通过别名简化连接

    SQL> !

    [oracle@redhat4dbs]$ cd

    [oracle@redhat4~]$ sqlplus sys/oracle@powter_alias as sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Fri Jul 20 01:30:12 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

     

    Connected to:

    Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL>

    开启lsnr2监听

    [oracle@redhat4~]$ lsnrctl start lsnr2

    [oracle@redhat4~]$ lsnrctl status lsnr2

     

    LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:43:47

     

    Copyright (c)1991, 2005, Oracle.  All rights reserved.

     

    Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1522)))

    STATUS of theLISTENER

    ------------------------

    Alias                     lsnr2

    Version                   TNSLSNR for Linux: Version10.2.0.1.0 - Production

    Start Date                20-JUL-2012 01:43:34

    Uptime                    0 days 0 hr. 0 min. 12 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    ListenerParameter File  /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

    Listener LogFile        /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log

    ListeningEndpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1522)))

    The listener supports no services

    The commandcompleted successfully

    将数据库中的默认端口号设置为lsnr2的端口号,然后动态注册

    SQL>show parameter local_

     

    NAME             TYPE            VALUE

    ------------------------------ ------------------------------------

    local_listener     string

    SQL>alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1522))" scope=memory;

     

    Systemaltered.

     

    SQL>show parameter local_

     

    NAME             TYPE            VALUE

    ------------------------------ ------------------------------------

    local_listener     string            (ADDRESS= (PROTOCOL = TCP)(HOST

                                      =192.168.0.200)(PORT = 1522))

     

    SQL>alter system register;

     

    System altered.

     

    [oracle@redhat4admin]$ lsnrctl status lsnr2

     

    LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:57:07

     

    Copyright (c)1991, 2005, Oracle.  All rights reserved.

     

    Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1522)))

    STATUS of the LISTENER

    ------------------------

    Alias                    lsnr2

    Version                  TNSLSNR for Linux: Version 10.2.0.1.0 - Production

    Start Date               20-JUL-2012 01:43:34

    Uptime                   0 days 0 hr. 13 min. 32 sec

    Trace Level              off

    Security                  ON: Local OS Authentication

    SNMP                     OFF

    Listener Parameter File  /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

    Listener Log File        /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log

    Listening EndpointsSummary...

     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1522)))

    ServicesSummary...

    Service"powter" has 1 instance(s).

      Instance "powter", status READY, has 1 handler(s)for this service...

    Service"powter_XPT" has 1 instance(s).

      Instance "powter", status READY,has 1 handler(s) for this service...

    The commandcompleted successfully

     

    九、小技巧

    1、在SQL>中使用vi编辑

    (1)、

    [oracle@redhat4~]$ vi .bash_profile

    export EDITOR=vi

    [oracle@redhat4~]$ source .bash_profile

    SQL>edit

    Wrote file afiedt.buf

    以上方法并没有完全像vi环境一样?不知道是否因为数据库类型(事务DB、数据仓库等)不同?

    (2)、修改登录参数

    [oracle@localhost~]$ cd $ORACLE_HOME/sqlplus/admin/

    在glogin.sql文件中追加:

    [oracle@localhostadmin]$ vi glogin.sql

    DEFINE _EDITOR =vi

    保存退出即可,以上修改后,在sqlplus中输入ed(it)即可进入vi编辑器修改上一条SQL语句。

    (3)、在SQL*Plus中临时修改:

    SQL>define _editor=/bin/vi

     

    2、查看所有68个系统变量值

    SQL>show all

    3、列出上一条缓存的SQL语句

    ●     l

    ●     list

    ●     ;

    4、改变终端提示符:

    SQL>show all

    sqlprompt "SQL> "

    SQL> setsqlprompt "redhat@wolex> "

    redhat@wolex>

    5、对上一条缓存的SQL语句进行部分修改

    ●     c[hange]

    6、执行上一条缓存的SQL语句

    ●     /

    7、修改系统变量,并在所有会话中生效

    [oracle@redhat4admin]$ pwd

    /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin

    [oracle@redhat4admin]$ vi glogin.sql

    例如追加:

    set linesize 120

    set pagesize 30

    col[umn]ename for[mat] a10

    8、通过错误代码查看信息

    [oracle@redhat4~]$ oerr ora 04031

    04031, 00000,"unable to allocate %s bytes of shared memory(\"%s\",\"%s\",\"%s\",\"%s\")"

    // *Cause:  More shared memory is needed than wasallocated in the shared

    //          pool.

    // *Action: Ifthe shared pool is out of memory, either use the

    //          dbms_shared_pool package to pin largepackages,

    //          reduce your use of shared memory, orincrease the amount of

    //          available shared memory by increasingthe value of the

    //          INIT.ORA parameters"shared_pool_reserved_size" and

    //          "shared_pool_size".

    //          If the large pool is out of memory,increase the INIT.ORA

    //          parameter"large_pool_size".

    9、连接sql developer

    (1)配置密码文件?

    (2)启动监听

    (3)关闭Linux的防火墙

    [root@redhat4~]# /sbin/iptables -F

    十、两个SID连接两个DB

    SID为powter的实例启动powter数据库:

    [oracle@redhat4~]$ echo $ORACLE_SID

    powter

    [oracle@redhat4~]$ sqlplus / as sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Fri Aug 10 08:47:11 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL> selectinstance_name from v$instance;

     

    INSTANCE_NAME

    --------------------------------

    powter

     

    SID为wolex的实例启动wolex_db数据库:

    [oracle@redhat4~]$ export ORACLE_SID=wolex

    [oracle@redhat4~]$ echo $ORACLE_SID

    wolex

    [oracle@redhat4~]$ sqlplus / as sysdba

     

    SQL*Plus:Release 10.2.0.1.0 - Production on Fri Aug 10 08:49:13 2012

     

    Copyright (c)1982, 2005, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

    With thePartitioning, OLAP and Data Mining options

     

    SQL> selectinstance_name from v$instance;

     

    INSTANCE_NAME

    ----------------

    wolex

    展开全文
  • 1、Oracle建库--创建数据表空间--create tablespace TESTloggingdatafile 'F:\app\zt\oradata\orcl\TEST.dbf'size 50mautoextend onnext 50m maxsize 20480mextent management local;--删除创建表空间及其文件--drop...

    1、Oracle建库

    --创建数据表空间--

    create tablespace TEST

    logging

    datafile 'F:\app\zt\oradata\orcl\TEST.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m

    extent management local;

    --删除创建表空间及其文件--

    drop tablespace SDE_TEST including contents and datafiles cascade constraint;

    --创建多个数据库文件--

    create tablespace TEST

    logging

    datafile 'F:\app\zt\oradata\orcl\TEST1.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m,

    'F:\app\zt\oradata\orcl\TEST2.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m,

    'F:\app\zt\oradata\orcl\TEST3.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m,

    'F:\app\zt\oradata\orcl\TEST4.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m,

    'F:\app\zt\oradata\orcl\TEST5.dbf'

    size 50m

    autoextend on

    next 50m maxsize 20480m

    extent management local;

    --增加一个数据库文件--

    ALTER TABLESPACE "TEST" ADD DATAFILE 'F:\app\zt\oradata\orcl\TEST2.DBF' SIZE 2G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

    2、Oracle建用户

    --创建用户--

    create user SDE_TEST identified by SDE_TEST

    default tablespace TEST

    temporary tablespace temp;

    --修改密码--

    alter user SDE_TEST identified by tf;

    --删除用户--

    select username,sid,serial# from v$session; --查看用户的连接状态

    alter system kill session '74,91'; --找到要删除用户的sid和serial并杀死

    drop user SDE_TEST cascade; --删除用户,及级联关系也删除掉

    --删除用户异常--

    Oracle ORA-01940 无法删除当前已连接用户

    3、Oracle赋权限

    grant connect,resource,dba to SDE_TEST;

    4、cmd 执行 Sql语句

    4.1 进入命令行

    sqlplus / as sysdba

    4.2 SDE_TEST /SDE_TEST 登录

    conn SDE_TEST/SDE_TEST;

    4.3导入SQL语句,导入创建好的表

    @G:\20190321.sql

    5、导出dmp文件,导入dmp文件数据

    5.1 导出指定表

    exp SDE_TEST/SDE_TEST@localhost/orcl file=c:\temp\exp20190625.dmp tables=(tb_sys_log,tb_build)

    5.2导入所有表

    imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp full=y

    5.2只导入表数据

    imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp data_only=y

    6、快速清除当前用户下的所有表数据

    SELECT 'DELETE FROM '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;

    7、导入批量数据

    imp SDE_TEST/SDE_TEST@localhost/orcl file=E:\20190321.dmp full=y data_only=y commit=y feedback=10000 buffer=10240000

    8、expdp / impdp 命令批量导入导出

    expdp命令:expdp SDE_TEST/SDE_TEST@localhost/orcl schemas=SDE_TEST DIRECTORY=tmpdir DUMPFILE=SDE_TEST_expdp_20190402.dmp logfile=SDE_TEST_expdp_20190402.log --导出当前命名空间下表结构和数据 --

    展开全文
  • 经本人测试如果静默建模版,在其它的数据库服务器里面调用需要拷到数据库的绝对路径,为了实施方便,这边使用图形化建模。...静默建库 查看下帮助 这边调用的模版就是我们刚才建的oa模版,库名和...
  • create temporarytablespace SP_MINES_TMPtempfile‘E:\Oracle\oradata\orcl\SP_MINES_TMP.dbf‘size 50mautoextendonnext 50maxsize 2048mextent management local;createtablespace SP_MINESloggingdatafile‘E:\...
  • 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中  exp system/manager@... 2 将数据库中system用户与sys用户的表导出  exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
  • Oracle建库脚本

    2013-03-15 23:22:51
    用于Oracle创建数据库的脚本代码,使用它可以快速的构建您自己的ORacle数据库
  • 1、oracle导出建库脚本(只导表结构,不包含数据时) 在pl/sql developer中操作(其他操作方式都没这个方便): 选择tools(工具)->import user object(导出用户对象),页面中不选“include storage”,在...
  • 此文档包含 sde建库 Oracle建库 导入导出命令 数据泵导入导出数据 希望对大家有所帮助
  • 0.Oracle数据库相关: 需开启服务:OracleServiceLfgstest和xxxListener 数据库,如:lfgstest(和SID保持一致) 表空间,如:LFGS 创建用户:lfgs(一般和表空间保持一致) 给用户授权: --------------------...
  • Oracle建库及导入mdb

    2013-11-28 08:58:48
    经过整理及实践,按此步骤执行即可成功构建数据库。如有不足或待完美之处,请留言。
  • 例如:create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; (4)创建表空间   ...
  • Oracle建库模板

    2011-05-18 15:28:15
    源于一次安装oracle,当时不知道开发那边给的是10.2.0.3的模板,安装的是10.2.0.1的软件,就是报错,说参数不符合本软件。最终查下来是给的建库模板的问题。生成建库模板 ***.dbc的方法:dbca-管理模板--...
  • oracle建库配置规范

    2018-01-30 16:13:57
    工具无法导出表,故尽量关闭。 _optimiter_use_feedback 该参数默认值经常导致 SQL 语句的执行计划频繁改变,故尽量关闭。 _clusterwide_global_transcations 该参数经常触发 bug ,或者在使用 ...
  • oracle数据库——建库,备份

    千次阅读 2010-01-06 15:30:00
    一是使用Oracle建库工且DBCA,这是一个图形界面工且,使用起来方便且很容易理解,因为它的界面友好、美观,而且提示也比较齐全。在Windows系统中,这个工具可以在Oracle程序组中打开(”开始”—“程序”—“ ...
  • oracle建表空间建库

    2012-10-08 17:43:26
    --李丹给的 建表空间和数据库的语句 ...create tablespace cmcs_hb_test datafile 'E:\oracle\product\10.2.0\oradata\ORCL\cmcs_hb_test.DBF' size 1000m /*(autoextend on)*/ autoextend on next 32m...
  • 导出: windows命令行窗口输入。 导出整个库: exp system/密码 file=路径+名称... 以system管理员身份导出某用户下的所有文件,并指定导出路径和名称: ...一、创建名为mhkg的表空间,数据文件路径和名称为H:\oracle..
  • 1521/orcl file=/home/oracle/dmp/HMBANK0902.dmp full=y log=/home/oracle/dmp/hmimp3.log 注意事项: Oracle创建directory 一般创建directory都是为了用数据泵导入/导出数据用,其实directory还有很多别的用处,...
  • oracle 11g手工建库

    千次阅读 2014-12-16 16:19:02
    手工建库前必须安装好oracle软件 -、手动建库 (Instance + Database, Instance:进程+内存,Database:文件,数据文件+控制文件+Redo文件+其他文件) (1)、启动实例(spfile) (2)、建库 (3)、跑脚本 1.参数...
  • oracle导出已存在表的建表语句

    千次阅读 2017-03-01 17:05:04
    当初需要将8个50多个字段的表从oracle迁移到mysql,当时自己也没多想,问了下dba能不能直接把表搬过来,得到否定的答案后又问了一个同事,得到自己建的答案后就傻傻的自己一个一个字段的建表了,完全没有想到可以用...
  • 一、Oracle数据库操作 1、创建数据库 create database databasename 2、删除数据库 drop database dbname 3、备份数据库 完全备份 exp demo/demo@orcl buffer=1024 file=d:\back.dmp full=y demo:用户名、密码 ...
  • Oracle(11g)建库、建表空间、建用户并授权、导入导出数据表 参考博客 http://blog.csdn.net/sindyintel/article/details/55272261  http://www.cnblogs.com/smartvessel/archive/2009/07/06/1517690.html  Oracle...
  • Oracle 忘记密码并建库

    2018-12-07 13:47:09
    问题起源:公司一个同事有一个很急的 BUG 需要调,但是服务器上的数据库(Oracle)非常不稳定,总是断,所以被逼无奈,打算复制一份到本地,并且电脑是老电脑,也并不知道装没装 Oracle。以下是全部检查以及解决步骤...
  • 用于导入导出oracle数据库的脚本,直接下来,配置用户名密码以及数据库名,备份的文件名等双击即可执行。
  • 本文包含的内容:使用命令操作oracle、postgres、mysql的导入导出,登录到数据,创建用户 注:我在公司使用的是Center OS操作系统,所以oracle和postgres均是在Linux使用,在其他工具中未测试;mysql是在自己的...
  • oracle建库与日期格式用法安装oracle 实例名orcl,system密码system/*建库步骤:1.安装oracle,填写实例名orcl,最后创建实例数据库后结束。2.系统运行中输入cmd回车打开命令窗口,输入命令 sqlplus ,输入用户名 ...
  • 一、数据库故障描述今天给大家分享一个Oracle数据库故障数据恢复案例,数据库故障表现为ASM磁盘组掉线,ASM实例不能mount。数据库管理员自己尝试进行简单的数据库修复后没有成功,于是联系到北京当地的数据恢复公司...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,136
精华内容 854
关键字:

oracle导出建库