SQL> set auto commit off
SP2-0158: unknown SET autocommit option "commit"
Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }
--设置自动提交，如果设置为on，则要小心一些删除的操作
SQL> set autocommit off;
--oracle在执行一个脚本的时候是否显示脚本中正在执行的sql语句
SQL> set echo on
--显示当前的sql查询和修改的行数
SQL> set feedback on
SQL>
SQL>
--显示执行所花费的时间
SQL> set timing on
SQL>
--查询数据库当前使用的语言
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
US
1 row selected.
Elapsed: 00:00:00.12
--查询数据库支持的语言
SQL> select * from v$nls_parameters;
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
AMERICAN
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
yyyy-mm-dd hh24:mi:ss
NLS_DATE_LANGUAGE
AMERICAN
NLS_CHARACTERSET
ZHS16GBK
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
$
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
19 rows selected.
Elapsed: 00:00:00.27
SQL> conn / as sysdba
Connected.
SQL>
--设置streams的初始参数
--启用队列消息的时间监视，指定时间延迟和失效属性
SQL> alter system set aq_tm_processes=2 scope=both;
System altered.
Elapsed: 00:00:00.36
--设置global_name
SQL> alter system set global_names=true scope=both;
System altered.
Elapsed: 00:00:00.25
--设置作业进程数
SQL> alter system set job_queue_processes=10 scope=both;
System altered.
Elapsed: 00:00:00.21
--系统允许最大的并发进程数
SQL> alter system set parallel_max_servers=20 scope=both;
System altered.
Elapsed: 00:00:01.01
--用以控制事务提交之后undo(撤销)信息的保留时间
SQL> alter system set undo_retention=3600 scope=both;
System altered.
Elapsed: 00:00:00.80
--设置日期的格式
SQL> alter system set nls_date_format='yyyy-mm--dd hh24:mi:ss' scope=spfile;
System altered.
Elapsed: 00:00:00.01
--设置stream池的大小
SQL> alter system set streams_pool_size=25m scope=both;
System altered.
Elapsed: 00:00:02.18
--设置utl_file_dir函数
SQL> alter system set utl_file_dir='*' scope=spfile;
System altered.
Elapsed: 00:00:00.04
--设置系统的最大连接数
SQL> alter system set open_links=4 scope=spfile;
System altered.
Elapsed: 00:00:00.21
--显示归档信息
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
--设置归档日志的路径
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.
Elapsed: 00:00:00.06
--设置归档文件的格式
SQL> alter system set log_archive_format='arch%t_%r_%s.arc' scope=spfile;
System altered.
Elapsed: 00:00:00.01
--再次确认归档路径
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
--设置每行显示的字数
SQL> col filename format a100;
--查看控制文件及路径
SQL> select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/opt/oracle/product/11.2.0/oradata/orcl/control01.ctl
NO 16384 594
/opt/oracle/product/11.2.0/flash_recovery_area/orcl/control02.ctl
NO 16384 594
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
2 rows selected.
Elapsed: 00:00:00.02
--创建表空间，及文件。
SQL> create tablespace tbs_stream datafile '/home/oracle/tbs_streams.dbf' size 100m autoextend on maxsize unlimited segment space management auto;
create tablespace tbs_stream datafile '/home/oracle/tbs_streams.dbf' size 100m autoextend on maxsize unlimited segment space management auto
*
ERROR at line 1:
ORA-01543: tablespace 'TBS_STREAM' already exists
Elapsed: 00:00:00.59
SQL> create tablespace tbs_streams datafile '/home/oracle/tbs_streamss.dbf' size 100m autoextend on maxsize unlimited segment space management auto;
Tablespace created.
Elapsed: 00:00:03.86
--将logmnir的数据字典从system表空间转移到新建的表空间，防止数据撑满
SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 245
ORA-06512: at line 1
Elapsed: 00:00:00.12
--重新启动数据库
SQL> shutdown immediate;
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
--强制关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 176164436 bytes
Database Buffers 58720256 bytes
Redo Buffers 2310144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select status from v$database;
select status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Elapsed: 00:00:00.00
SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.02
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
Elapsed: 00:00:00.02
--导致失败的原因是一个数据文件丢失
SQL> drop tablespace tbs_stream;
drop tablespace tbs_stream
*
ERROR at line 1:
ORA-01109: database not open
Elapsed: 00:00:00.03
SQL> alter database datafile '/home/oracle/tbs_steam01.dbf' offline drop;
Database altered.
Elapsed: 00:00:00.12
SQL> alter database open;
Database altered.
Elapsed: 00:00:35.51
SQL> drop tablespace tbs_streams include contents;
drop tablespace tbs_streams include contents
*
ERROR at line 1:
ORA-02173: invalid option for DROP TABLESPACE
Elapsed: 00:00:00.27
SQL> drop tablespace tbs_streams;
Tablespace dropped.
Elapsed: 00:00:02.71
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 238530560 bytes
Fixed Size 1335724 bytes
Variable Size 150998612 bytes
Database Buffers 83886080 bytes
Redo Buffers 2310144 bytes
Database mounted.
SQL> select v1.group#,member,sequence#,first_change# from v$log v1,v$logfile v2 where v1.group#=v2.group#;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
SEQUENCE# FIRST_CHANGE#
---------- -------------
1
/opt/oracle/product/11.2.0/oradata/orcl/redo01.log
22 1103495
3
/opt/oracle/product/11.2.0/oradata/orcl/redo03.log
24 1155949
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
SEQUENCE# FIRST_CHANGE#
---------- -------------
2
/opt/oracle/product/11.2.0/oradata/orcl/redo02.log
23 1127044
3 rows selected.
Elapsed: 00:00:00.08
SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOGMNR_D.SET_TABLESPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.26
SQL> select status from v$database;
select status from v$database
*
ERROR at line 1:
ORA-00904: "STATUS": invalid identifier
Elapsed: 00:00:00.09
SQL> select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.02
SQL> alter database open;
Database altered.
Elapsed: 00:00:11.82
SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;
*
ERROR at line 1:
ORA-00959: tablespace 'TBS_STREAMS' does not exist
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 245
ORA-06512: at line 1
Elapsed: 00:00:01.23
SQL> execute dbms_logmnr_d.set_tablespace('tbs_streamss');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streamss'); END;
*
ERROR at line 1:
ORA-00959: tablespace 'TBS_STREAMSS' does not exist
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 245
ORA-06512: at line 1
Elapsed: 00:00:00.02
SQL> execute dbms_logmnr_d.set_tablespace('tbs_streams');
BEGIN dbms_logmnr_d.set_tablespace('tbs_streams'); END;
*
ERROR at line 1:
ORA-00959: tablespace 'TBS_STREAMS' does not exist
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 245
ORA-06512: at line 1
Elapsed: 00:00:00.01
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TBS_STREAM
7 rows selected.
Elapsed: 00:00:00.17
SQL> execute dbms_logmnr_d.set_tablespace('tbs_stream');
BEGIN dbms_logmnr_d.set_tablespace('tbs_stream'); END;
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/home/oracle/tbs_steam01.dbf'
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 245
ORA-06512: at line 1
Elapsed: 00:00:01.65
SQL> drop tablespace tbs_stream;
drop tablespace tbs_stream
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
Elapsed: 00:00:00.67
SQL> drop tablespace tbs_stream including contents;
drop tablespace tbs_stream including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small
Elapsed: 00:00:12.49
SQL> select username,default_tablespace,temporary_tablespace from dba_users where (defualt_tablespace='system' or temporary_tablespace='system') and username not in ('system','sys');
select username,default_tablespace,temporary_tablespace from dba_users where (defualt_tablespace='system' or temporary_tablespace='system') and username not in ('system','sys')
*
ERROR at line 1:
ORA-00904: "DEFUALT_TABLESPACE": invalid identifier
Elapsed: 00:00:00.17
SQL> select username,default_tablespace,temporary_tablespace from dba_users where (default_tablespace='system' or temporary_tablespace='system') and username not in ('system','sys');
no rows selected
Elapsed: 00:00:00.18
SQL> select tablespace_name,(bytes/1024/1024)m from dba_data_files;
TABLESPACE_NAME M
------------------------------ ----------
USERS 5
UNDOTBS1 155
SYSAUX 520
SYSTEM 700
EXAMPLE 100
TBS_STREAM
6 rows selected.
Elapsed: 00:00:00.08
SQL> spool off；
--导致的原因是表空间太小，一下有几种方式解决
--1，为tbs_stream表空间在新增一个数据文件
--2，把tbs_stream放到别的地方
--3，重新设置tbs_stream表空间的大小，此场景不适用
--付方法：
alter database datafile '/../tbs_stream01.dbf' resize xxxm;
