oracle 监听一个字段,ORACLE 监听配置，ORA-01086，事务，序列，savepoint，flashback,修改表数据和结构...

最新推荐文章于 2022-05-21 16:20:28 发布

偏偏无理取闹

最新推荐文章于 2022-05-21 16:20:28 发布

阅读量359

点赞数

文章标签： oracle 监听一个字段

1.数据库启动过程 (1)startup nomount;

参数文件

位置在$ORACLE_HOME/dbs/init[$ORACLE_SID].ora

(2)alter database mount;

打开并验证控制文件

(3)alter database open;

读取控制文件，并打开对应的DBF和日志文件文件

2.服务器端配监听

(1)配置监听及Server name [oracle@h1 dbhome_2]$ export DISPLAY=192.168.5.222:0.0

[oracle@h1 dbhome_2]$ export LANG="" #------或export LANG="en_US.UTF-8"#导出语言字符集

[oracle@h1 dbhome_2]$ netmgr

#---------------windows上没有netmgr

---------------端口

-----1521(sql)

-----5560(EM)

#---------netmgr 配置监听可以配数据库选项，监听位置

#---------------netmgr 配置

#---------------配置监听完成后，重启监听(lsnrctl stop,lsnrctl start)

#---------------与netca不同，netmgr可以创建监听；netca可以配置监听部分属性

#---------------与netca不同，netmgr可以创建namesapce,验证时默认为scott/tiger；

netca可以配置namespace部分属性,验证时默认为system

注册就是把ORACLE数据库作为一个服务注册到监听程序！客户端不用关心数据库名和实例名，只要知道服务名，就可以申请这个服务，监听程序获得这个申请，把用户分配给数据库进程，这样客户端就可以连上数据库实例了！

@.静态注册就是实例启动时读取listener.ora文件的配置，将实例注册到监听程序。

@.动态注册根本不需要listener.ora文件，是根据参数service_names,instance_name的值进行注册，根据这两个参数的值知道实例名和服务名，向监听程序注册；是这样的。

@.动态注册不需要一定先启动监听，再启动实例，如果监听后启动，动态注册会有延迟而已；

alter system register

@.动态注册默认只注册到默认的监听(名称是LISTENER、端口是1521、协议是TCP)，如果需要向非默认监听注册，则需要配置local_listener参数！

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

在数据库服务器启动过程中，数据库服务器会向监听程序注册相应的服务(无论何时启动一个数据库，默认地都有两条信息注册到监听器中：数据库服务器对应的实例和服务。)

相当于是这样：在数据库服务器和客户端之间有一监听程序(Listener)，在监听程序中，会记录相应数据库对应的服务名(一个数据库可能对应有多个服务名)，当客户端需要连接数据库时，只需要提供服务名，就可以建立客户端和服务器之间的连接。

(3)动态注册

动态注册是在instance启动的时候PMON进程根据init.ora中的instance_name,service_names两个参数将实例和服务动态注册到listener中。

首先要在init.ora中指定instance_name,service_names两个参数的值。在sqlplus下通过show parameter service_names 和show parameter instance_name可以查看这两个参数的值。

注册到监听器中的实例值从init.ora文件中的instance_name参数取得。如果该参数没有设定值，那么它将取init.ora文件中的db_name的值。如果在RAC中配置，您必须将集群中每个实例的instance_name参数设置为一个唯一的值。

注册到监听器中的服务值从init.ora文件中的参数service_names取得。如果该参数没有设定值，数据库将拼接init.ora文件中的 db_name和db_domain的值来注册自己。如果选择提供service_names值，您可以使用完全限定的名称(比如 orcl.oracle.com)或缩写的名称(比如orcl)。如果选择缩写的名称并设置了db_domain参数，注册到监听器中的服务将是 service_name值和db_domain值的拼接。

(4)静态监听

静态注册就是实例启动时读取listener.ora文件的配置，将实例和服务注册到监听程序。无论何时启动一个数据库，默认地都有两条信息注册到监听器中：数据库服务器对应的实例和服务。

静态注册时，listener.ora中的GLOBAL_DBNAME向外提供服务名，listener.ora中的SID_NAME提供注册的实例名。

(5)配置多ORACLE实例监听

在多个oracle实例的环境下，下述两种监听的配置哪个是最佳的：

1)每个实例配一套监听，对应一个监听端口。

2)多个实例共享一个监听端口，也就是说一个监听端口同时监听多个oracle实例 #------------已知本地实例orcl,test

(1)netmgr 建立监听ORCL ,监听位置192.168.5.130:1522,数据库是ORCL

(2)查看监听

[oracle@h1 ~]$ lsnrctl start orcl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 20:57:23

Starting /app/oracle/product/11.2.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production

System parameter file is /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Log messages written to /app/oracle/diag/tnslsnr/h1/orcl/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1522)))

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

STATUS of the LISTENER

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

Alias orcl

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 20:57:23

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/orcl/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "orcl" has 1 instance(s).

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

The command completed successfully

#-----------------以上情况,数据库ORCL启动，监听启动,ORCL数据库监听未注册

(3)注册监听

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 11 21:00:04 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 501059584 bytes

Fixed Size 2214736 bytes

Variable Size 373294256 bytes

Database Buffers 121634816 bytes

Redo Buffers 3915776 bytes

Database mounted.

Database opened.

#------------------查看当前实例

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

orcl

#----------------------查看当前监听

SQL> show parameter listener;

NAME TYPE VALUE

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

listener_networks string

local_listener string LISTENER_ORCL

remote_listener string

SQL> show parameter service_names;

NAME TYPE VALUE

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

service_names string orcl

#------------------查看当前实例

SQL> show parameter instance_name;

NAME TYPE VALUE

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

instance_name string orcl

#----------------修改监听

SQL> alter system set local_listener="ORCL";

System altered.

SQL> alter system register;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@h1 ~]$ lsnrctl status ORCL

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 21:07:37

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

STATUS of the LISTENER

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

Alias orcl

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 20:57:23

Uptime 0 days 0 hr. 10 min. 14 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/orcl/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "orcl" has 2 instance(s).

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

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

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

#----------------以上监听注册成功

#------------------重启数据库,使Spfile生效

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 11 21:07:56 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 501059584 bytes

Fixed Size 2214736 bytes

Variable Size 373294256 bytes

Database Buffers 121634816 bytes

Redo Buffers 3915776 bytes

Database mounted.

Database opened.

SQL> show parameter listener;

NAME TYPE VALUE

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

listener_networks string

local_listener string ORCL

remote_listener string

SQL> exit

同理配置test #---------------1.修改环境变量$ORACLE_SID

[oracle@h1 ~]$ export ORACLE_SID=test

[oracle@h1 ~]$ echo $ORACLE_SID

test

#---------------2.启动数据库

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 11 21:09:51 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2215064 bytes

Variable Size 327156584 bytes

Database Buffers 201326592 bytes

Redo Buffers 3764224 bytes

Database mounted.

Database opened.

SQL> show parameter instance_name;

NAME TYPE VALUE

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

instance_name string test

#---------------3.查看监听

SQL> show parameter listener;

NAME TYPE VALUE

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

listener_networks string

local_listener string

remote_listener string

#---------------4.修改监听

SQL> alter system set local_listener=test;

System altered.

SQL> alter system register;

System altered.

#---------------5.重启

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2215064 bytes

Variable Size 327156584 bytes

Database Buffers 201326592 bytes

Redo Buffers 3764224 bytes

Database mounted.

Database opened.

SQL> show parameter listener;

NAME TYPE VALUE

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

listener_networks string

local_listener string TEST

remote_listener string

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

#---------------6.配置监听文件

netmgr 建立监听TEST ,监听位置192.168.5.130:1523,数据库是TEST

#---------------7.启监听

[oracle@h1 ~]$ lsnrctl start test

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 21:16:55

Starting /app/oracle/product/11.2.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production

System parameter file is /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Log messages written to /app/oracle/diag/tnslsnr/h1/test/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1523)))

STATUS of the LISTENER

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

Alias test

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 21:16:55

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/test/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1523)))

Services Summary...

Service "test" has 1 instance(s).

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

The command completed successfully

#------------------监听启动，未注册

#---------------8.查看所有监听情况[oracle@h1 ~]$ lsnrctl status orcl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 21:17:31

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

STATUS of the LISTENER

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

Alias orcl

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 21:16:48

Uptime 0 days 0 hr. 0 min. 43 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/orcl/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "orcl" has 2 instance(s).

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

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

Service "orclXDB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...The command completed successfully

[oracle@h1 ~]$ lsnrctl status test

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 21:17:36

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1523)))

STATUS of the LISTENER

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

Alias test

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 21:16:55

Uptime 0 days 0 hr. 0 min. 41 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/test/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1523)))

Services Summary...

Service "test" has 1 instance(s).

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

The command completed successfully

#---------------9.数据库重启，监听注册

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 11 21:17:48 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter listener;

NAME TYPE VALUE

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

listener_networks string

local_listener string TEST

remote_listener string

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 534462464 bytes

Fixed Size 2215064 bytes

Variable Size 327156584 bytes

Database Buffers 201326592 bytes

Redo Buffers 3764224 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@h1 ~]$ lsnrctl status test

oracle@h1 ~]$ lsnrctl status test

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2012 22:09:08

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1523)))

STATUS of the LISTENER

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

Alias test

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 11-NOV-2012 21:16:55

Uptime 0 days 0 hr. 52 min. 18 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File /app/oracle/diag/tnslsnr/h1/test/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.130)(PORT=1523)))

Services Summary...

Service "test" has 2 instance(s).

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

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

Service "testXDB" has 1 instance(s).

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

The command completed successfully

-------------------------------注册步骤

a)netmgr或netca配置监听

b)监听启动

c)数据库启动,修改监听参数，重启使Spfile生效

4.查看数据库实例

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 00:32:42 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

orcl OPEN

SQL>

5.手动建库

(1)建库

[oracle@h1 dbhome_2]$ export DISPLAY=192.168.5.222:0.0

[oracle@h1 dbhome_2]$ export LANG="" #------或export LANG=""

[oracle@h1 dbhome_2]$ dbca

#----------------数据库安装工具

#--------------安装test库

#------------------安装后server上要两个ORACLE实例：orcl和test

(2)切换库 [oracle@h1 dbhome_2]$ echo $ORACLE_SID

orcl

[oracle@h1 dbhome_2]$ export ORACLE_SID=test

[oracle@h1 dbhome_2]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 01:10:29 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

test

SQL> host echo $ORACLE_SID

test

SQL>

6.事务

(1).rollback ,commit [oracle@h1 dbhome_2]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 01:45:35 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user u1 identified by abc;

User created.

SQL> grant dba to u1;

Grant succeeded.

SQL> conn u1/abc

Connected.

SQL> create table u1(id int,name varchar2(20));

Table created.

SQL> insert into u1 values(1,'ORACLE');

1 row created.

SQL> commit;

Commit complete.

SQL> insert all

2 into u1 values(2,'java')

3 into u1 values(3,'redhat')

4 select * from dual;

2 rows created.

SQL> select * from u1;

ID NAME

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

1 ORACLE

2 java

3 redhat

SQL> rollback;

Rollback complete.

SQL> select * from u1;

ID NAME

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

1 ORACLE

(2).savepoint 只能想前ROLLBACK，不可以向后ROLLBACK SQL> create table t2 as select * from u1 where 1=2;

Table created.

SQL> select * from t2;

no rows selected

SQL> insert into t2 values(1,'ORACLE');

1 row created.

SQL> savepoint a;

Savepoint created.

SQL> insert all

2 into t2 values(2,'JAVA')

3 into t2 values(3,'redhat')

4 select * from dual;

2 rows created.

SQL> savepoint b;

Savepoint created.

SQL> insert into t2 values(4,'MCITP');

1 row created.

SQL> savepoint c;

Savepoint created.

SQL> insert into t2 values(5,'MVA');

1 row created.

SQL> savepoint d;

Savepoint created.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 redhat

4 MCITP

5 MVA

SQL> rollback to savepoint c;

Rollback complete.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 redhat

4 MCITP

SQL> rollback to savepoint a;

Rollback complete.

SQL> select * from t2;

ID NAME

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

1 ORACLE

SQL> rollback to savepoint a;

Rollback complete.

SQL> select * from t2;

ID NAME

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

1 ORACLE

SQL> rollback to savepoint d;

rollback to savepoint d

ERROR at line 1:

ORA-01086: savepoint 'D' never established in this session or is invalid

(3)savepoint 只在当前session或事务有效

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

SQL> insert into t2 values(3,'MCITP');

1 row created.

SQL> savepoint a;

Savepoint created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@h1 dbs]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 02:17:48 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn u1/abc

Connected.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

SQL> rollback to savepoint a;

rollback to savepoint a

ERROR at line 1:

ORA-01086: savepoint 'A' never established in this session or is invalid

(3)当rollback,事务自动回滚到上一个事务commit,所有savepoint失效；使用DDL语句或强制session断开，当前事务默认提交 SQL> insert into t2 values(4,'redhat');

1 row created.

SQL> savepoint a;

Savepoint created.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

4 redhat

SQL> insert into t2 values(5,'MVA')

2 ;

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

4 redhat

5 MVA

SQL> insert into t2 values(6,'ITILV3');

1 row created.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

4 redhat

5 MVA

6 ITILV3

6 rows selected.

SQL> rollback to savepoint b;

Rollback complete.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

4 redhat

5 MVA

SQL> rllback;

SP2-0042: unknown command "rllback" - rest of line ignored.

SQL> rollback;

Rollback complete.

SQL> select * from t2;

ID NAME

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

1 ORACLE

2 JAVA

3 MCITP

SQL>

7.tables,sequence

(1)dual [oracle@h1 dbhome_2]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 10 04:13:39 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select user from dual

2 ;

USER

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

SYS

SQL> select user from dual;

USER

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

SYS

SQL> create user u01 identified by abc;

User created.

SQL> grant dba to u01;

Grant succeeded.

SQL> conn u01/abc

Connected.

SQL> select user from dual;

USER

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

U01

#------------------dual表结构

SQL> desc dual

Name Null? Type

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

DUMMY VARCHAR2(1)

SQL> select SYSDATE from dual;

SYSDATE

---------

10-NOV-12

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2012-11-10 04:17:14

SQL> select 1+2 from dual;

1+2

----------

(2).序列 SQL> create sequence sq1 increment by 1 start with 1;

Sequence created.

SQL> select sq1.nextval from dual;

NEXTVAL

----------

SQL> select sq1.nextval from dual;

NEXTVAL

----------

SQL> select sq1.nextval from dual;

NEXTVAL

----------

SQL> select sq1.currval from dual;

CURRVAL

----------

SQL>

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

#----------------序列建表

CREATE SEQUENCE SEQ_001 START WITH 9999 INCREMENT BY 9;

CREATE TABLE A004(ID INT);

--------------1

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL);

INSERT INTO A004 VALUES(SEQ_001.CURRVAL+1);

select * from A004;

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

1 10035

2 10036

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

--------------2

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL);

INSERT INTO A004 VALUES(SEQ_001.CURRVAL+1);

select * from A004;

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

1 10035

2 10036

3 10044

4 10045

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

--------------3

INSERT INTO A004 VALUES(SEQ_001.CURRVAL+1);

select * from A004;

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

1 10035

2 10036

3 10044

4 10045

5 10045

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

-------------4

INSERT INTO A004 VALUES(SEQ_001.CURRVAL);

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL);

select * from A004;

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

1 10035

2 10036

3 10044

4 10045

5 10045

6 10044

7 10053

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

-------------5

INSERT INTO A004 VALUES(SEQ_001.CURRVAL-2);

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL);

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL-2);

INSERT INTO A004 VALUES(SEQ_001.CURRVAL);

select * from A004;

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

1 10035

2 10036

3 10044

4 10045

5 10045

6 10044

7 10053

8 10051

9 10062

10 10069

11 10071

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

-------------6

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL*2);

INSERT INTO A004 VALUES(SEQ_001.NEXTVAL/2);

INSERT INTO A004 VALUES(SEQ_001.CURRVAL);

select * from A004;

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

1 10035

2 10036

3 10044

4 10045

5 10045

6 10044

7 10053

8 10051

9 10062

10 10069

11 10071

12 20160

13 5045

14 10089

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

(3).分区表 SQL> conn u01/abc

Connected.

SQL> create table t_new(id int,time date) partition by range(time)

2 (partition p1 values less than (to_date('2004-7-1','yyyy-mm-dd')),

3 partition p2 values less than (to_date('2005-7-1','yyyy-mm-dd')),

4 partition p3 values less than (to_date('2006-7-1','yyyy-mm-dd')),

5 partition p4 values less than (maxvalue));

Table created.

SQL> insert into t_new values (1,to_date('2003-01-01','yyyy_mm_dd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_new;

ID TIME

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

1 01-JAN-03

SQL>

(4).修改表结构，及注释添加列 Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as system@TEST

SQL> create table ttt(id int ,name varchar(30) ,age int);

Table created

SQL> insert into ttt values(1,'TOM',25);

1 row inserted

SQL> alter table ttt add email varchar(30 char);

Table altered

SQL> desc ttt;

Name Type Nullable Default Comments

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

ID INTEGER Y

NAME VARCHAR2(30) Y

AGE INTEGER Y

EMAIL VARCHAR2(30 CHAR) Y 修改列默认值 SQL> alter table ttt modify age default 24;

Table altered

SQL> desc ttt;

Name Type Nullable Default Comments

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

ID INTEGER Y

NAME VARCHAR2(30) Y

AGE INTEGER Y 24

EMAIL VARCHAR2(30 CHAR) Y

#-----------varchar(30)是长度为30的变长字符串

#-----------varchar(30 CHAR)是长度为30bit的变长字符串修改列长度 SQL> alter table ttt modify email varchar(31 char);

Table altered 删除字段 SQL> alter table ttt drop column email;

Table altered 表重命名 SQL> alter table emp rename to emp1;

Table altered 建主键 SQL> alter table emp add primary key(empno);

Table altered 注释 SQL> create table emp as select * from scott.emp;

Table created

SQL> desc emp;

Name Type Nullable Default Comments

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

EMPNO NUMBER(4) Y

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2) Y

#---------------表注释

SQL> comment on table emp is "EMP";

comment on table emp is "EMP"

ORA-01780: string literal required

#---------------列注释

SQL> comment on table emp is 'EMP';

Comment added

SQL> comment on column emp.empno is 'EMP NUMBER';

Comment added

SQL>

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

SQL> desc emp

Name Type Nullable Default Comments

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

EMPNO NUMBER(4) Y EMP NUMBER

ENAME VARCHAR2(10) Y

JOB VARCHAR2(9) Y

MGR NUMBER(4) Y

HIREDATE DATE Y

SAL NUMBER(7,2) Y

COMM NUMBER(7,2) Y

DEPTNO NUMBER(2) Y

#-----------------------sql查询-----------------------

----(1)所有用户(表注释)select * from all_tab_comments where lower(table_name)='emp' and owner='U1';

------------(列注释)

select * from all_col_comments where lower(table_name)='emp' and owner='U1'；

-----(2)当前用户(表注释)select * from user_tab_comments where lower(table_name)='emp';

------------(列注释)

select * from user_col_comments where lower(table_name)='emp';

truncate默认提交事务 SQL> create table emp as select * from scott.emp;

Table created

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> select count(*) from ttt;

COUNT(*)

----------

SQL> insert into ttt(id,name) values(12,'mary')；

2 ；

SQL> insert into ttt(id,name) values(12,'mary');

1 row inserted

SQL> select count(*) from ttt;

COUNT(*)

----------

SQL> truncate table emp;

Table truncated

SQL> select count(*) from ttt;

COUNT(*)

----------

SQL> rollback;

Rollback complete

SQL> select count(*) from ttt;

COUNT(*)

----------

4 闪回 SQL>insert into emp select * from SCOTT.emp where 1=1;

14 rows inserted

SQL> drop table emp;

#--------------回收站查询

SQL> select object_name,ORIGINAL_NAME from recyclebin;

OBJECT_NAME ORIGINAL_NAME

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

BIN$zjpBnCu9DqLgQKjAggUa9Q==$0 EMP

Table dropped

SQL> flashback table emp to before drop;

Done

---------------------------回收站结构-------------------------

SQL> desc recyclebin;

------------------------------truncate不可以使用recyclebin-----------------------------------

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> truncate table emp;

Table truncated

SQL> select object_name from recyclebin;

OBJECT_NAME

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

#------------truncate清除表数据，不清除表结构，自动提交事务(DDL),不可以使用recyclebin恢复数据

#------------drop同时清除表数据和结构，自动提交事务(DDL),可以使用recyclebin恢复数据

#-------------表彻底删除，不进入recyclebin

SQL> drop table emp purge;

Table dropped

9.public databse link

#-------------------192.168.5.130上有orcl和test数据库

#--------------in orcl

#--------------1.建立public database link

[oracle@h1 dbs]$ export ORACLE_SID=orcl

[oracle@h1 dbs]$ echo $ORACLE_SID

orcl

SQL> create public database link conn_test connect to system identified by manager using 'test';

Database link created.

SQL>

#--------------2.删除public database link

SQL> drop public database link conn_test ;

Database link dropped.

#--------------3.查看public database link视图

-------------------------------------in test-------------------

SQL> create public database link conn_orcl connect to system identified by manager using 'orcl';

Database link created.

SQL> select owner, db_link from dba_db_links;

#--------------4.使用public database link

-------------------------------------in orcl-------------------

SQL> create table emp as select * from scott.emp;

Table created

-------------------------------------in test-------------------

SQL>select * from emp@conn_orcl;

-------------------------------------in orcl-------------------

SQL> create public database link conn_test connect to u01 identified by abc

3 using 'test'

4 ;

Database link created

SQL> select owner, db_link from dba_db_links;

-------------------------------------in test-------------------

#----------------在远程数据库中只能使用DML(insert,update,delete),不能使用DDL(create,drop,alter)

SQL> insert into emp@conn_orcl(empno,ename) values(17,'TOM');

1 row inserted

SQL> commit;

Commit complete

SQL> select count(*) from emp@conn_orcl;

COUNT(*)

----------

----------------------in orcl

SQL> create user u01 identified by abc;

User created

SQL> grant dba to u01;

Grant succeeded

#-----------------------------in orcl namespace是orcl_130

SQL> conn u01/abc@orcl_130

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as u01@orcl_130

#-----------------------------在test上建立分区表

SQL> create table t_new(id int,time date) partition by range(time)

2 (partition p1 values less than (to_date('2004-7-1','yyyy-mm-dd')),

3 partition p2 values less than (to_date('2005-7-1','yyyy-mm-dd')),

4 partition p3 values less than (to_date('2006-7-1','yyyy-mm-dd')),

5 partition p4 values less than (maxvalue));

Table created

#-----------------------------在orcl远程修改test

SQL> insert into t_new@conn_test values (1,to_date('2003-01-01','yyyy_mm_dd'));

1 row inserted

SQL> commit;

Commit complete

#-------------------------------------------

SQL> select * from t_new@conn_test;

ID TIME

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

1 2003/1/1

#--------------------------------------------

SQL> delete from t_new@conn_test;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from t_new@conn_test;

ID TIME

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

SQL>

偏偏无理取闹

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 监听一个字段,ORACLE 监听配置，ORA-01086，事务，序列，savepoint，flashback,修改表数据和结构...

1.数据库启动过程 (1)startup nomount;参数文件位置在$ORACLE_HOME/dbs/init[$ORACLE_SID].ora(2)alter database mount;打开并验证控制文件(3)alter database open;读取控制文件，并打开对应的DBF和日志文件文件2.服务器端配监听(1)配置监听及Server name [oracle@h1 dbhom...
复制链接

扫一扫