第53和54次：2014-10-25

botang · 发表于 2014-10-29 16:28:26

本帖最后由 botang 于 2014-11-1 20:06 编辑

2014-10-25-A.txt

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL>
[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL>
[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL>
[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL>

复制代码

2014-10-25-B.txt

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL> create table t05110_x( a number , b number ) ;
Table created.
SQL> alter table t05110_x add constraint chk_t05110_x check ( a +b =1 ) ;
Table altered.
SQL> insert into t05110_x values ( 0.5, 0.5 ) ;
1 row created.
SQL> insert into t05110_x values ( 0.5, 0.4 ) ;
insert into t05110_x values ( 0.5, 0.4 )
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHK_T05110_X) violated
SQL> create table t05110_w ( a number );
Table created.
SQL> insert into t05110_w (1);
insert into t05110_w (1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into t05110_w values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=2 where a=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=1 where a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05110_w;
A
----------
1
SQL> update t05110_w set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=1;
update t05110_w set a=1
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of 20001 is out of
range
ORA-06512: at "HR.TRG1", line 4
ORA-04088: error during execution of trigger 'HR.TRG1'
SQL> update t05110_w set a=1;
update t05110_w set a=1
*
ERROR at line 1:
ORA-20001: YOU ARE WRONG
ORA-06512: at "HR.TRG1", line 4
ORA-04088: error during execution of trigger 'HR.TRG1'
SQL> create table t05110_u( a number ) ;
Table created.
SQL> insert into t05110_u values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t05110_u rename column a to b;
Table altered.
SQL> select * from t05110_u;
B
----------
1
SQL> rename t05110_u to t05110_v;
Table renamed.
SQL> select * from t05110_v;
B
----------
1
SQL> update t05110_v set b=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05110_v read only;
Table altered.
SQL> update t05110_v set b=3;
update t05110_v set b=3
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."T05110_V"
SQL> alter table t05110_v rename b to c;
alter table t05110_v rename b to c
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
SQL> alter table t05110_v rename column b to c;
alter table t05110_v rename column b to c
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."T05110_V"
SQL> alter table t05110_v add constraint chk1 check ( a >1000) ;
alter table t05110_v add constraint chk1 check ( a >1000)
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
SQL> alter table t05110_v add constraint chk1 check ( b >1000) ;
alter table t05110_v add constraint chk1 check ( b >1000)
*
ERROR at line 1:
ORA-02293: cannot validate (HR.CHK1) - check constraint violated
SQL> alter table t05110_v add constraint chk1 check ( b <1000) ;
Table altered.
SQL> alter table t05110_v read write;
Table altered.
SQL> update t05110_v set b=3;
1 row updated.
SQL> commit;
Commit complete.
SQL>
[root@station90 ~]# su - oracle
[oracle@station90 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://station90.example.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ..... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/station90.example.com_orcl/sysman/log
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 09:25:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05110_a( a timestamp) ;
Table created.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commitl
SP2-0042: unknown command "commitl" - rest of line ignored.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493 AM
SQL> alter table t05110_a modify ( a timestamp(9));
Table altered.
SQL> insert into t05110_a values ( systimestamp) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05110_a;
A
---------------------------------------------------------------------------
25-OCT-14 09.26.38.282493000 AM
25-OCT-14 09.28.26.877417000 AM
SQL> create table t05110_b ( a INTERVAL YEAR TO MONTH
2 ) ;
create table t05110_b ( a INTERVAL YEAR TO MONTH
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05110_z ( a INTERVAL YEAR TO MONTH ) ;
Table created.
SQL> insert into t05110_z values ( interval '99-1' YEAR TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_z;
SYSDATE+A
------------------
25-NOV-13
SQL> select to_char(sysdate+a,'YYYY-MM-DD') from t05110_z;
TO_CHAR(SY
----------
2113-11-25
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_z modify ( a INTERVAL YEAR(3) to MONTH ) ;
Table altered.
SQL> insert into t05110_z values ( interval '100-1' YEAR TO MONTH ) ;
insert into t05110_z values ( interval '100-1' YEAR TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_z values ( interval '100-1' YEAR(3) TO MONTH ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH ) ;
insert into t05110_z values ( interval '1000-1' YEAR(4) TO MONTH )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> create table t05110_y( a interval day to second ) ;
Table created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1' day to second ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select sysdate+a from t05110_y;
SYSDATE+A
------------------
04-NOV-14
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y;
select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SSSSSS' ) from t05110_y
*
ERROR at line 1:
ORA-01821: date format not recognized
SQL> select to_char(sysdate+a, 'YYYY-MM-DD:HH24:MI:SS' ) from t05110_y;
TO_CHAR(SYSDATE+A,'
-------------------
2014-11-04:10:41:33
SQL> insert into t05110_y values ( interval '10 1:1:1.111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.1111' day to second )
2 ;
1 row created.
SQL> insert into t05110_y values ( interval '10 1:1:1.111111111' day to second )
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
2 ;
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> alter table t05110_y modify ( a interval day (3) to second ) ;
Table altered.
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day to second );
insert into t05110_y values ( interval '100 1:1:1.111111111' day to second )
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into t05110_y values ( interval '100 1:1:1.111111111' day(3) to second );
1 row created.
SQL> commit;
Commit complete.
SQL> create table t05110_x( a number , b number ) ;
Table created.
SQL> alter table t05110_x add constraint chk_t05110_x check ( a +b =1 ) ;
Table altered.
SQL> insert into t05110_x values ( 0.5, 0.5 ) ;
1 row created.
SQL> insert into t05110_x values ( 0.5, 0.4 ) ;
insert into t05110_x values ( 0.5, 0.4 )
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHK_T05110_X) violated
SQL> create table t05110_w ( a number );
Table created.
SQL> insert into t05110_w (1);
insert into t05110_w (1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into t05110_w values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=2 where a=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=1 where a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t05110_w;
A
----------
1
SQL> update t05110_w set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05110_w set a=1;
update t05110_w set a=1
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of 20001 is out of
range
ORA-06512: at "HR.TRG1", line 4
ORA-04088: error during execution of trigger 'HR.TRG1'
SQL> update t05110_w set a=1;
update t05110_w set a=1
*
ERROR at line 1:
ORA-20001: YOU ARE WRONG
ORA-06512: at "HR.TRG1", line 4
ORA-04088: error during execution of trigger 'HR.TRG1'
SQL> create table t05110_u( a number ) ;
Table created.
SQL> insert into t05110_u values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t05110_u rename column a to b;
Table altered.
SQL> select * from t05110_u;
B
----------
1
SQL> rename t05110_u to t05110_v;
Table renamed.
SQL> select * from t05110_v;
B
----------
1
SQL> update t05110_v set b=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05110_v read only;
Table altered.
SQL> update t05110_v set b=3;
update t05110_v set b=3
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."T05110_V"
SQL> alter table t05110_v rename b to c;
alter table t05110_v rename b to c
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
SQL> alter table t05110_v rename column b to c;
alter table t05110_v rename column b to c
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."T05110_V"
SQL> alter table t05110_v add constraint chk1 check ( a >1000) ;
alter table t05110_v add constraint chk1 check ( a >1000)
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
SQL> alter table t05110_v add constraint chk1 check ( b >1000) ;
alter table t05110_v add constraint chk1 check ( b >1000)
*
ERROR at line 1:
ORA-02293: cannot validate (HR.CHK1) - check constraint violated
SQL> alter table t05110_v add constraint chk1 check ( b <1000) ;
Table altered.
SQL> alter table t05110_v read write;
Table altered.
SQL> update t05110_v set b=3;
1 row updated.
SQL> commit;
Commit complete.
SQL>

复制代码

2014-10-25-C.txt

drop trigger trg1;
create or replace trigger trg1
after update of a on t05110_w
referencing new as new old as old
for each row
begin
if :old.a > :new.a
then
raise_application_error('-20001','YOU ARE WRONG');
end if;
end;

复制代码

2014-10-25-D.txt

SQL> select seq1.nextval from dual;
NEXTVAL
----------
2
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select seq1.nextval from dual;
NEXTVAL
----------
4
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05111_a;
A
----------
1
3
5
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
12 rows selected.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
15
16
17
18
19
17 rows selected.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
15
16
17
18
19
20
18 rows selected.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
Database opened.
SQL> conn hr/oracle_4U
Connected.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
15
16
17
18
19
20
21
19 rows selected.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
15
16
17
18
19
20
21
41
20 rows selected.
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
Database opened.
SQL> conn hr/oracle_4U
Connected.
SQL> insert into t05111_a values ( seq1.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05111_a;
A
----------
1
3
5
6
7
8
9
10
11
12
13
A
----------
14
15
16
17
18
19
20
21
42
41
21 rows selected.
SQL> alter sequence seq1 order ;
Sequence altered.
SQL> alter sequence seq1 maxvalue 100 ;
Sequence altered.
SQL> alter sequence seq1 nocycle ;
Sequence altered.
SQL> alter sequence seq1 cycle ;
Sequence altered.
SQL> alter sequence seq1 increment by 10 ;
alter sequence seq1 increment by 10
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle
SQL> alter sequence seq1 nocache;
Sequence altered.
SQL> alter sequence seq1 increment by 10 ;
Sequence altered.
SQL> alter sequence seq1 start with 20;
alter sequence seq1 start with 20
*
ERROR at line 1:
ORA-02283: cannot alter starting sequence number
SQL> alter sequence seq1 minvalue 20 ;
Sequence altered.
SQL> select seq1.nextval from dual;
NEXTVAL
----------
52
SQL> /
NEXTVAL
----------
62
SQL> /
NEXTVAL
----------
72
SQL> /
NEXTVAL
----------
82
SQL> /
NEXTVAL
----------
92
SQL> /
NEXTVAL
----------
20
SQL> /
NEXTVAL
----------
30
SQL> /
NEXTVAL
----------
40
SQL> /
NEXTVAL
----------
50
SQL> /
NEXTVAL
----------
60
SQL> /
NEXTVAL
----------
70
SQL> /
NEXTVAL
----------
80
SQL> /
NEXTVAL
----------
90
SQL> /
NEXTVAL
----------
100
SQL> /
NEXTVAL
----------
20
SQL> /
NEXTVAL
----------
30
SQL> /
NEXTVAL
----------
40
SQL> /
NEXTVAL
----------
50
SQL> /
NEXTVAL
----------
60
SQL> /
NEXTVAL
----------
70
SQL> /
NEXTVAL
----------
80
SQL> alter sequence seq1 cache 10;
alter sequence seq1 cache 10
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle
SQL> alter sequence seq1 cache 7;
Sequence altered.
SQL>

复制代码

2014-10-25-E.txt

CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
select * from salvu50;
update salvu50 set ANN_SALARY=31201 where ID_NUMBER=198;
update salvu50 set ID_NUMBER=999 where ID_NUMBER=198;
select * from employees order by 1;
select * from user_sequences;
grant create public synonym to hr;

复制代码

2014-10-25-F.txt

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 15:19:25 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05310_a ( a number ) ;
Table created.
SQL> insert into t05310_a values (1) ;
1 row created.
SQL> !ps
PID TTY TIME CMD
16217 pts/2 00:00:00 sqlplus
16328 pts/2 00:00:00 ps
SQL> commit;
Commit complete.
SQL> flashback table t05310_a to scn 2132750;
flashback table t05310_a to scn 2132750
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table t05310_a enable row movement;
Table altered.
SQL> flashback table t05310_a to scn 2132750;
Flashback complete.
SQL> select * from t05310_a;
no rows selected
SQL> flashback table t05310_a to scn 2142619;
Flashback complete.
SQL> select * from t05310_a;
A
----------
1
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> update employees set salary=25000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
SQL> update employees set salary=26000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
SQL> update employees set salary=27000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
SQL> update employees set salary=28000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table employees enable row movement ;
Table altered.
SQL> flashback table employees to scn 2143354;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
26000
SQL> select versions_xid, versions_start_scn , salary
2 from employees
3 versions between minvalue and maxvalue
4 where employee_id=100;
versions between minvalue and maxvalue
*
ERROR at line 3:
ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_start_scn , salary
2 from employees
3 versions between minscn and maxscn
4* where employee_id=100
SQL> /
versions between minscn and maxscn
*
ERROR at line 3:
ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_start_scn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4* where employee_id=100
SQL> /
select versions_xid, versions_start_scn , salary
*
ERROR at line 1:
ORA-00904: "VERSIONS_START_SCN": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_startscn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4* where employee_id=100
SQL> /
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0400060069050000 2144145 26000
0400060069050000 2144145 28000
09001A0002060000 2143370 28000
0A00160019050000 2143357 27000
0500140091060000 2143353 26000
0200200002060000 2143338 25000
24000
7 rows selected.
SQL> flashback table employees to scn 2143338;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
25000
SQL> flashback table employees to scn 2143337;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> flashback table employees to scn 2143352;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
25000
SQL> flashback table employees to scn 2143353;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
26000
SQL> select versions_xid, versions_startscn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4 where employee_id=100;
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0A0009001A050000 2144731 26000
0A0009001A050000 2144731 25000
0800030074050000 2144699 25000
0800030074050000 2144699 24000
03000700E9050000 2144666 24000
03000700E9050000 2144666 25000
08000E0072050000 2144562 25000
08000E0072050000 2144562 26000
0400060069050000 2144145 26000
0400060069050000 2144145 28000
09001A0002060000 2143370 28000
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0A00160019050000 2143357 27000
0500140091060000 2143353 26000
0200200002060000 2143338 25000
24000
15 rows selected.
SQL> create table t05310_b ( a number ) ;
Table created.
SQL> insert into t05310_b values ( 1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=5;
1 row updated.
SQL> alter table t05310_b add ( b varchar2(20)) ;
Table altered.
SQL> update t05310_b set a=5;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> update t05310_b set a=6;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_b drop (b) ;
Table altered.
SQL> update t05310_b set a=7;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=8;
1 row updated.
SQL> commit;
Commit complete.
SQL> drop table t05310_b;
Table dropped.
SQL> create table t05310_c ( a number ) ;
Table created.
SQL> insert into t05310_c values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_c enable row movement;
Table altered.
SQL> alter table t05310_c skrink space;
alter table t05310_c skrink space
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table t05310_c shrink space;
Table altered.
SQL>

复制代码

2014-10-25-G.txt

select * from v$session where terminal='pts/2';
select * from v$transaction;
select * from flashback_transaction_query;
select d.SUPPLEMENTAL_LOG_DATA_MIN,
d.SUPPLEMENTAL_LOG_DATA_PK,
d.SUPPLEMENTAL_LOG_DATA_UI,
d.SUPPLEMENTAL_LOG_DATA_FK,
d.SUPPLEMENTAL_LOG_DATA_ALL
from v_$database d ;
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
select * from flashback_transaction_query ftq
where ftq.xid='0600060015060000' ;
select t.row_movement from dba_tables t where t.owner='HR' and t.table_name='T05310_A';
alter table hr.t05310_a disable row movement;
select * from flashback_transaction_query ftq where ftq.undo_sql like '%EMPLOYEES%';
select d.SUPPLEMENTAL_LOG_DATA_MIN,
d.SUPPLEMENTAL_LOG_DATA_PK,
d.SUPPLEMENTAL_LOG_DATA_UI,
d.SUPPLEMENTAL_LOG_DATA_FK,
d.SUPPLEMENTAL_LOG_DATA_ALL
from v_$database d ;
alter database drop supplemental log data;
alter database drop supplemental log data (primary key) columns;
select versions_xid, versions_startscn , versions_operation , a
from hr.t05310_b
versions between scn minvalue and maxvalue;
select versions_xid, versions_startscn , versions_operation , a
from hr.t05310_c
versions between scn minvalue and maxvalue;

复制代码

2014-10-25-H.txt

select versions_xid, versions_startscn , versions_operation , a
from hr.t05310_d
versions between scn minvalue and maxvalue;
select * from dba_recyclebin;
purge dba_recyclebin;
select * from dba_indexes i where i.owner='HR' and i.table_name='T05311_B';
select * from dba_constraints c where c.owner='HR' and c.table_name='T05311_B';
alter index hr."BIN$Bjw8DVeVZy/gU1oAqMBjeA==$0" rename to pk_t05311_b;
create tablespace tbs05311_a datafile size 256K autoextend on ;
select * from dba_recyclebin;
select df.tablespace_name , df.file_name, df.bytes/1024/1024 from dba_data_files df where df.tablespace_name='TBS05311_A';

复制代码

2014-10-25-I.txt

ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_start_scn , salary
2 from employees
3 versions between minscn and maxscn
4* where employee_id=100
SQL> /
versions between minscn and maxscn
*
ERROR at line 3:
ORA-00905: missing keyword
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_start_scn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4* where employee_id=100
SQL> /
select versions_xid, versions_start_scn , salary
*
ERROR at line 1:
ORA-00904: "VERSIONS_START_SCN": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 select versions_xid, versions_startscn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4* where employee_id=100
SQL> /
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0400060069050000 2144145 26000
0400060069050000 2144145 28000
09001A0002060000 2143370 28000
0A00160019050000 2143357 27000
0500140091060000 2143353 26000
0200200002060000 2143338 25000
24000
7 rows selected.
SQL> flashback table employees to scn 2143338;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
25000
SQL> flashback table employees to scn 2143337;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> flashback table employees to scn 2143352;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
25000
SQL> flashback table employees to scn 2143353;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
26000
SQL> select versions_xid, versions_startscn , salary
2 from employees
3 versions between scn minvalue and maxvalue
4 where employee_id=100;
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0A0009001A050000 2144731 26000
0A0009001A050000 2144731 25000
0800030074050000 2144699 25000
0800030074050000 2144699 24000
03000700E9050000 2144666 24000
03000700E9050000 2144666 25000
08000E0072050000 2144562 25000
08000E0072050000 2144562 26000
0400060069050000 2144145 26000
0400060069050000 2144145 28000
09001A0002060000 2143370 28000
VERSIONS_XID VERSIONS_STARTSCN SALARY
---------------- ----------------- ----------
0A00160019050000 2143357 27000
0500140091060000 2143353 26000
0200200002060000 2143338 25000
24000
15 rows selected.
SQL> create table t05310_b ( a number ) ;
Table created.
SQL> insert into t05310_b values ( 1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=5;
1 row updated.
SQL> alter table t05310_b add ( b varchar2(20)) ;
Table altered.
SQL> update t05310_b set a=5;
1 row updated.
SQL> rollback;
Rollback complete.
SQL> update t05310_b set a=6;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_b drop (b) ;
Table altered.
SQL> update t05310_b set a=7;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_b set a=8;
1 row updated.
SQL> commit;
Commit complete.
SQL> drop table t05310_b;
Table dropped.
SQL> create table t05310_c ( a number ) ;
Table created.
SQL> insert into t05310_c values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_c set a=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_c enable row movement;
Table altered.
SQL> alter table t05310_c skrink space;
alter table t05310_c skrink space
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table t05310_c shrink space;
Table altered.
SQL> show user
USER is "HR"
SQL> create table t05310_d( a number ) ;
Table created.
SQL> insert into t05310_d values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> update t05310_d set a=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_d set a=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_d set a=4;
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05310_d set a=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t05310_d enable row movement ;
Table altered.
SQL> select * from t05310_d;
A
----------
1
SQL> conn / as sysdba
Connected.
SQL> show parameter recycle
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05311_a ( a number ) ;
Table created.
SQL> insert into t05311_a values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_a;
Table dropped.
SQL> select * from BIN$Bjw8DVeQZy/gU1oAqMBjeA==$0;
select * from BIN$Bjw8DVeQZy/gU1oAqMBjeA==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from "BIN$Bjw8DVeQZy/gU1oAqMBjeA==$0";
A
----------
1
SQL> flashback table t05311_a to before drop;
Flashback complete.
SQL> select * from t05311_a;
A
----------
1
SQL> drop table t05311_a;
Table dropped.
SQL> create table t05311_a( b varchar2(20)) ;
Table created.
SQL> insert into t05311_a values ( 'A') ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_a;
Table dropped.
SQL> flashback table t05311_a to before drop;
Flashback complete.
SQL> select * from t05311_a;
B
--------------------
A
SQL> drop table t05311_a;
Table dropped.
SQL> flashback table "BIN$Bjw8DVeRZy/gU1oAqMBjeA==$0" to before drop;
Flashback complete.
SQL> select * from t05311_a;
A
----------
1
SQL> flashback table t05311_a to before drop;
flashback table t05311_a to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL>
SQL> flashback table "BIN$Bjw8DVeTZy/gU1oAqMBjeA==$0" to before drop;
flashback table "BIN$Bjw8DVeTZy/gU1oAqMBjeA==$0" to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> flashback table "BIN$Bjw8DVeTZy/gU1oAqMBjeA==$0" to before drop rename to t05311_a2;
Flashback complete.
SQL> select * from t05311_a2;
B
--------------------
A
SQL> select * from t05311_a;
A
----------
1
SQL> create table t05311_b ( a number constraint pk_t05311_b primary key ) ;
Table created.
SQL> insert into t05311_b (1) ;
insert into t05311_b (1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into t05311_b values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_b;
Table dropped.
SQL> flashback table t05311_b to before drop;
Flashback complete.
SQL> show user
USER is "HR"
SQL> create table t05311_c ( a number ) tablespace tbs05311_a;
Table created.
SQL> insert into t05311_c values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_c;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> create table hr.t05311_d tablespace tbs05311_a as select * from dba_source ;
Table created.
SQL> conn hr/oracle_$U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn hr/oracle_4U
Connected.
SQL> flashback table t05311_c to before drop;
flashback table t05311_c to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL>

复制代码

2014-10-25-J.txt

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 25 17:25:17 2014
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05311_i( a number );
Table created.
SQL> insert into t05311_i values(1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311_i;
Table dropped.
SQL> flashback table t05311_i to before drop;
flashback table t05311_i to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> drop table t05311_j;
Table dropped.
SQL> drop table t05311_k;
Table dropped.
SQL> flashback table t05311_j to before drop;
Flashback complete.
SQL> flashback t05311_k to before drop;
flashback t05311_k to before drop
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> flashback tables t05311_k to before drop;
flashback tables t05311_k to before drop
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> flashback table t05311_k to before drop;
Flashback complete.
SQL> create table t05311_l( a number ) ;
Table created.
SQL> insert into t05311_l values (1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> create materialized view log on t05311_l with sequence,rowid including new values;
Materialized view log created.
SQL> drop table t05311_l;
Table dropped.
SQL> flashback table t05311_l to before drop;
Flashback complete.
SQL>

复制代码

2014-10-25-K.txt

select * from user_recyclebin;
purge tablespace users user sh ;
purge tablespace users;
select * from dba_audit_policies;
begin
dbms_fga.add_policy(object_schema => 'HR',
object_name => 'T05311_I',
policy_name => 'P1',audit_condition => 'a>5',audit_column => 'A',
statement_types => 'SELECT,INSERT');
end;
create table t05311_j( a number constraint pk_t05311_j primary key , b varchar2(20) ) ;
create table t05311_k( a number , c date) ;
create bitmap index i05311_k on t05311_k(j.b)
from t05311_j j , t05311_k k
where j.a=k.a;
select /*+ index(k, i05311_k) */ j.a,b,c from t05311_j j , t05311_k k
where j.a=k.a;
select * from user_indexes where table_name='T05311_K';
select * from user_mview_logs;
select * from MLOG$_T05311_L;

复制代码