Oracle DBA 必须掌握的查询脚本：

0：启动与关闭

                       orcle 数据库的启动与关闭
1：连接数据库

2：数据库开启状态的实现步骤：
       2-1：启动数据库
          2-1-1正常启动数据库: SQL> startup

          特点：启动数据库，分配内存，打开实例，启动后台进程，数据库处于open状态
         2-1-2：进入nomount状态 SQL> startup nomount

             特点：打开实例，分配内存，启动后台进程
             装载数据库：SQL> alter database mount;

             特点：
            查询数据库状态：SQL> select status from v$instance;

             特点：数据库处于mounted 状态
                     打开数据库 SQL> alter database open;

              特点：数据库处于打开状态
              查看数据库状态：SQL> select status from v$instance;

   startup nomount ---> alter database mount -->alter database open
   数据库进入nomount状态            数据库进入mount状态                       数据库进入open状态


                                 |   参数文件（读取控制文件的物理位置）
即：数据库打开的三个过程用到了 |   控制文件（读取数据文件、日志文件的物理位置）
数据库启动过程
                                 |   数据文件、日志文件

3:关闭数据库 SQL> shutdown immediate


        数据库关闭过程：

   强制的关闭然后在打开数据库         sql>startup force
    执行命令时不会进行检查点的监控
    在重启时要进行实例的恢复操作；


                              立即          事务            常规
数据库关闭类型           abort       immediate    transactional   (shutdown )normal
不允许用于一个新的连接       No           No              No             No
等待当前会话结束             No           No              No             Yes
等待当前事务的结束           No           No              Yes            Yes
执行检查点并关闭打开的文件   No           Yes             Yes            Yes

描述：何为“执行检查点并关闭打开的文件”的操作？《即：一致性关闭数据库》
   即：当数据库在执行关闭命令时，数据库会去检查日志文件、数据文件、控制文件的数据保持一致后才进行数据库的相应文件的关闭操作；该写入磁盘的数据都写入磁盘。

在用shutdown abort 命令进行关闭数据时，没有执行数据库的监测点的一致性操作；当再一次打开数据库时，当数据库再一次启动时候调用了smo系统进程进行了一次最近操作的实例恢复的操作。
查看smo 后台进程
[root@localhost orcl]# ps -ef|grep ora_smo


    4:数据库启动操作：示例

      查看如何打开nomoun状态的参数文件的操作步骤：
1：打开环境变量 cat .bash_profile
2：打开oracle 安装的dbs目录

当oracle数据库的参数文件丢失，oracle数据库不能进入nomoun状态；
当oracle数据库的控制文件丢失，oracle数据库不能进入moun状态；
当oracle数据库的数据文件或日志文件丢失，oracle数据库不能进入open状态；

练习一：当丢失参数文件，orcle数据库不能进入nomoun状态时，参数文件如何恢复：
一：创建丢失场景
   1：先关闭数据库

2：修改配置文件的名称，造成配置文件丢失额场景；
      2-1：造成文件丢失的假象

     2-2：启动数据库，显示错误信息的状态

二：处理相对应的问题的方法
二-1：如果只是文件名被修改了，而且该文件的参数没有被修改，则则需要把文件名
       以正确的命名修改回来即可启动数据库；
   操作：1：进入linux系统的dbs文件中：
          [root@localhost app]# cd oracle/product/11.2.0/db_1/dbs/

                2：修改文件名：
                  [root@localhost dbs]# mv pfileorcl.ora.bak    pfileorcl.ora

                3：启动数据库：SQL> startup nomount

         二-2:整个文件丢失或则损坏，在没有备份情况下怎么恢复？在有备份情况下如何恢复？

       练习二：丢失控制文件，oracle数据库不能进入moun状态，控制文件如何恢复？
         一：创建丢失场景：
          1：先关闭数据库

2：修改配置文件的名称，造成配置文件丢失额场景；
    2-1：造成文件丢失的假象
    操作：进入系统中对应的文件位置

   修改文件名

启动数据库

            二：处理相对应的问题的方法
            二-1：如果只是文件名被修改了，而且该文件的参数没有被修改，则则需要把文件名
       以正确的命名修改回来即可启动数据库；
             操作：1：进入linux系统的dbs文件中：
          [root@localhost app]# cd /u01/app/oracle/fast_recovery_area/orcl/

                2：修改文件名：
                  [root@localhost dbs]# mv control02.ctl.bak control02.ctl

                3：启动数据库：SQL> startup nomount

     二-2:整个控制文件丢失或则损坏，在没有备份情况下怎么恢复？在有备份情况下如何恢复？

          练习场景三：数据文件（日志文件）丢失，oracle数据库不能进入open状态，
                  数据文件（日志文件）如何恢复？
            三-1：如果只是文件名被修改了，而且该文件的参数没有被修改，则则需要把文件                   名以正确的命名修改回来即可启动数据库；
            操作：1：进入linux系统的/u01/app/oracle/oradata/orcl/文件中,并修改文件名称

                 2：启动数据库后，数据库将不会进入open状态，并提示oracle的数据文件丢失

         二-1：如果只是文件名被修改了，而且该文件的参数没有被修改，则则需要把文件名
        以正确的命名修改回来即可启动数据库；
            操作：1：进入linux系统的/u01/app/oracle/oradata/orcl/文件中,并修改文件名称

                2：重新启动数据库；

         同理，日志文件也是这样练习修改的；
二-2:整个数据文件或日志文件丢失或则损坏，在没有备份情况下怎么恢复？
      在有备份情况下如何恢复？

1：通过v$parameter数据字段来查询oracle标准数据块的大小

  1 ----通过 v$parameter数据字典来查询oracle标准数据块的大小。
  2 SYS@orcl> startup
  3 ORACLE instance started.
  4 
  5 Total System Global Area 1221992448 bytes
  6 Fixed Size                  1344596 bytes
  7 Variable Size             771754924 bytes
  8 Database Buffers          436207616 bytes
  9 Redo Buffers               12685312 bytes
 10 Database mounted.
 11 Database opened.
 12 SYS@orcl> col name format a30;
 13 SYS@orcl> col value format a20;
 14 SYS@orcl> select name,value from v$parameter where name='db_block_size';
 15 
 16 NAME                           VALUE
 17 ------------------------------ --------------------
 18 db_block_size                  8192
 19 
 20 SYS@orcl> show parameter db_block
 21 
 22 NAME                                 TYPE        VALUE
 23 ------------------------------------ ----------- ------------------------------
 24 db_block_buffers                     integer     0
 25 db_block_checking                    string      FALSE
 26 db_block_checksum                    string      TYPICAL
 27 db_block_size                        integer     8192

2：通过　dict 查看数据库中数据字典的信息

  1 SYS@orcl> col table_name for a30;
  2 SYS@orcl> col comments for a30;
  3 SYS@orcl> select * from dict;
  4 
  5 TABLE_NAME                     COMMENTS
  6 ------------------------------ ------------------------------
  7 DBA_CONS_COLUMNS               Information about accessible c
  8                                olumns in constraint definitio
  9                                ns
 10 
 11 DBA_LOG_GROUP_COLUMNS          Information about columns in l
 12                                og group definitions
 13 
 14 DBA_LOBS                       Description of LOBs contained
 15                                in all tables
 16 
 17 DBA_CATALOG                    All database Tables, Views, Sy

3 : 通过 v$fixed_view_definition 查看数据库中内部系统表的信息

  1 SYS@orcl> col view_name format a15;
  2 SYS@orcl> col view_definition format a30000;
  3 SYS@orcl>  select * from v$fixed_view_definition where rownum<=10;
  4 
  5 VIEW_NAME              VIEW_DEFINITION
  6 ----------------------------------------------------------------------------------------------
  7 GV$WAITSTAT             select inst_id,decode(indx,1,'data block',2,'sort block',3,'save undo block', 4,
  8 'segment header',5,'save undo header',6,'free list',7,'extent map', 8,'1st level
  9  bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',12,'bitmap index b
 10 lock',13,'file header block',14,'unused', 15,'system undo header',16,'system und
 11 o block', 17,'undo header',18,'undo block'), count,time from x$kcbwait where ind
 12 x!=0

4：通过查询 dba_data_files 数据来了解Oracle系统的数据文件信息

  1 [oracle@localhost ~]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 8 23:27:12 2016
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> col file_name format a50;
 13 SYS@orcl> set linesize3000;
 14 SYS@orcl> select

file_name,tablespace_name

from

dba_data_files

where

rownum<=10;

 15 
 16 FILE_NAME                                          TABLESPACE_NAME
 17 -------------------------------------------------- ------------------------------
 18 /u01/app/oracle/oradata/orcl/users01.dbf           USERS
 19 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1
 20 /u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX
 21 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM
 22 /u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE
 23 
 24 SYS@orcl>

5：查看临时文件信息： dba_temp_files 、v$tempfile;

  1 SYS@orcl> col file_name format a50;
  2 SYS@orcl> col tablespace_name format a20;
  3 SYS@orcl> select file_name,tablespace_name from dba_temp_files;
  4 
  5 FILE_NAME                                          TABLESPACE_NAME
  6 -------------------------------------------------- --------------------
  7 /u01/app/oracle/oradata/orcl/temp01.dbf            TEMP
  8 
  9 SYS@orcl> 4：

View Code

  1 
  2 SYS@orcl> select * from  v$tempfile;
  3 
  4      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
  5 ---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  6          1           788143 30-NOV-16          3          1 ONLINE  READ WRITE   30408704       3712     20971520       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
  7 
  8 SYS@orcl>

View Code

6：获取控制文件信息： v$controlfile 数据字典

  1 SYS@orcl> col name fromat a60;
  2 SYS@orcl> col name format a60;
  3 SYS@orcl> col status format a20;
  4 SYS@orcl> col block_size format a60;
  5 SYS@orcl> col is_recoveris_recover format a60 ;
  6 SYS@orcl> col block_size format a150 ;
  7 SYS@orcl> select * from v$controlfile;
  8 
  9 STATUS               NAME                                                         IS_ BLOCK_SIZE FILE_SIZE_BLKS
 10 -------------------- ------------------------------------------------------------ --- ---------- --------------
 11                      /u01/app/oracle/oradata/orcl/control01.ctl                   NO  ##########            594
 12                      /u01/app/oracle/fast_recovery_area/orcl/control02.ctl        NO  ##########            594

View Code

7：查看日志文件信息： v$logfile;

  1 SYS@orcl> col member for a50;
  2 SYS@orcl> select * from v$logfile;
  3 
  4     GROUP# STATUS               TYPE    MEMBER                                             IS_
  5 ---------- -------------------- ------- -------------------------------------------------- ---
  6          3                      ONLINE  /u01/app/oracle/oradata/orcl/redo03.log            NO
  7          2                      ONLINE  /u01/app/oracle/oradata/orcl/redo02.log            NO
  8          1                      ONLINE  /u01/app/oracle/oradata/orcl/redo01.log            NO
  9 
 10 SYS@orcl>
 11

View Code

8：查看oacle 系统是否采用归档模式： v$database

  1 
  2 SYS@orcl> col name format a30;
  3 SYS@orcl> select dbid,name,log_mode from v$database;
  4 
  5       DBID NAME                           LOG_MODE
  6 ---------- ------------------------------ ------------
  7 1457025880 ORCL                           ARCHIVELOG

View Code

9：查询归档日志文件的所在路劲信息： show parameter log_archive_desc

  1 
  2 SYS@orcl> set pagesize 30;
  3 SYS@orcl> show parameter log_archive_dest;
  4 
  5 NAME                                 TYPE        VALUE
  6 ------------------------------------ ----------- ------------------------------
  7 log_archive_dest                     string
  8 log_archive_dest_1                   string
  9 log_archive_dest_10                  string
 10 log_archive_dest_11                  string
 11 log_archive_dest_12                  string
 12 log_archive_dest_13                  string
 13 log_archive_dest_14                  string
 14 log_archive_dest_15                  string
 15 log_archive_dest_16                  string
 16 log_archive_dest_17                  string
 17 log_archive_dest_18                  string
 18 log_archive_dest_19                  string
 19 log_archive_dest_2                   string
 20 log_archive_dest_20                  string
 21 log_archive_dest_21                  string
 22 log_archive_dest_22                  string
 23 log_archive_dest_23                  string
 24 log_archive_dest_24                  string
 25 log_archive_dest_25                  string
 26 log_archive_dest_26                  string
 27 log_archive_dest_27                  string
 28 log_archive_dest_28                  string
 29 log_archive_dest_29                  string
 30 log_archive_dest_3                   string
 31 log_archive_dest_30                  string
 32 log_archive_dest_31                  string
 33 log_archive_dest_4                   string
 34 
 35 NAME                                 TYPE        VALUE
 36 ------------------------------------ ----------- ------------------------------
 37 log_archive_dest_5                   string
 38 log_archive_dest_6                   string
 39 log_archive_dest_7                   string
 40 log_archive_dest_8                   string
 41 log_archive_dest_9                   string
 42 log_archive_dest_state_1             string      enable
 43 log_archive_dest_state_10            string      enable
 44 log_archive_dest_state_11            string      enable
 45 log_archive_dest_state_12            string      enable
 46 log_archive_dest_state_13            string      enable
 47 log_archive_dest_state_14            string      enable
 48 log_archive_dest_state_15            string      enable
 49 log_archive_dest_state_16            string      enable
 50 log_archive_dest_state_17            string      enable
 51 log_archive_dest_state_18            string      enable
 52 log_archive_dest_state_19            string      enable
 53 log_archive_dest_state_2             string      enable
 54 log_archive_dest_state_20            string      enable
 55 log_archive_dest_state_21            string      enable
 56 log_archive_dest_state_22            string      enable
 57 log_archive_dest_state_23            string      enable
 58 log_archive_dest_state_24            string      enable
 59 log_archive_dest_state_25            string      enable
 60 log_archive_dest_state_26            string      enable
 61 log_archive_dest_state_27            string      enable
 62 log_archive_dest_state_28            string      enable
 63 log_archive_dest_state_29            string      enable
 64 
 65 NAME                                 TYPE        VALUE
 66 ------------------------------------ ----------- ------------------------------
 67 log_archive_dest_state_3             string      enable
 68 log_archive_dest_state_30            string      enable
 69 log_archive_dest_state_31            string      enable
 70 log_archive_dest_state_4             string      enable
 71 log_archive_dest_state_5             string      enable
 72 log_archive_dest_state_6             string      enable
 73 log_archive_dest_state_7             string      enable
 74 log_archive_dest_state_8             string      enable
 75 log_archive_dest_state_9             string      enable
 76 SYS@orcl>

View Code

10：查询视图v$parameter 进行确定参数的默认值是否修改过

  1 SYS@orcl> col name for a30;
  2 SYS@orcl> col value for a30;
  3 SYS@orcl> select name ,value,ismodified from v$parameter where rownum <=20;
  4 
  5 NAME                           VALUE                          ISMODIFIED
  6 ------------------------------ ------------------------------ ----------
  7 lock_name_space                                               FALSE
  8 processes                      150                            FALSE
  9 sessions                       248                            FALSE
 10 timed_statistics               TRUE                           FALSE
 11 timed_os_statistics            0                              FALSE
 12 resource_limit                 FALSE                          FALSE
 13 license_max_sessions           0                              FALSE
 14 license_sessions_warning       0                              FALSE
 15 cpu_count                      4                              FALSE
 16 instance_groups                                               FALSE
 17 event                                                         FALSE
 18 sga_max_size                   1224736768                     FALSE
 19 use_large_pages                TRUE                           FALSE
 20 pre_page_sga                   FALSE                          FALSE
 21 shared_memory_address          0                              FALSE
 22 hi_shared_memory_address       0                              FALSE
 23 use_indirect_data_buffers      FALSE                          FALSE
 24 lock_sga                       FALSE                          FALSE
 25 processor_group_name                                          FALSE
 26 shared_pool_size               0                              FALSE
 27 
 28 20 rows selected.
 29 
 30 SYS@orcl>

View Code

  1 SYS@orcl> select name ,value,ismodified from v$parameter where  ismodified = 'true' ;
  2 
  3 no rows selected
  4 
  5 SYS@orcl>

View Code

11：修改系统参数：

  1 --查看Cursor相关参数
  2 SYS@orcl> show parameter cursor;
  3 
  4 NAME                                 TYPE        VALUE
  5 ------------------------------------ ----------- ------------------------------
  6 cursor_bind_capture_destination      string      memory+disk
  7 cursor_sharing                       string      EXACT
  8 cursor_space_for_time                boolean     FALSE
  9 open_cursors                         integer     300
 10 session_cached_cursors               integer     50
 11 
 12 ---修改相关参数 
 13 SYS@orcl> alter system set open_cursors=350 scope=both;
 14 
 15 System altered.
 16 
 17 SYS@orcl> alter system set session_cached_cursors=100 scope=spfile;
 18 
 19 System altered.
 20 
 21 SYS@orcl> alter system set cursor_space_for_time=true scope=spfile;
 22 
 23 System altered.
 24 /*
 25 scope 参数值有三个选项。
 26 scope=memory: 只改变当前实例运行，亦即初始化参数改变了只对当前实例有效，当实例重启之后，初始化参数值还原。
 27 scope=spfile：只改变spfile的设置。亦即改变初始化参数文件内容。实例重启后参数生效。
 28 scope=both：既改变实例也改变spfile。需要注意的是，如果修改静态参数，必须指定SPFILE参数（scope=spfile），否则会报错。
 29 */

View Code

12：创建一个密码文件其sys 口令为：oracle

  1 [oracle@localhost dbs]$ ls
  2 hc_orcl.dat  init.ora  initPROD1.ora  lkORCL  orapworcl  spfileorcl.ora
  3 [oracle@localhost dbs]$ pwd
  4 /u01/app/oracle/product/11.2.0/db_1/dbs
  5 [oracle@localhost dbs]$ orapwd file=dbsorapwPROD1 entries=30
  6 
  7 Enter password for SYS:  ---oracle
  8 [oracle@localhost dbs]$
  9

View Code

13:查询警告文件位置

  1 SYS@orcl> show parameter backg
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 background_core_dump                 string      partial
  6 background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orc
  7                                                  l/orcl/trace

View Code

14：查看跟踪文件的位置：

  1 SYS@orcl> show parameter user_dump
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 user_dump_dest                       string      /u01/app/oracle/diag/rdbms/orc
  6                                                  l/orcl/trace
  7 SYS@orcl>
  8

View Code

15：查看并修改共享池大小

  1 SYS@orcl> show parameter shared_pool
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 shared_pool_reserved_size            big integer 10905190
  6 shared_pool_size                     big integer 0
  7 SYS@orcl> alter system set shared_pool_size=30m;
  8 
  9 System altered.
 10 
 11 SYS@orcl> show parameter shred_pool
 12 SYS@orcl> show parameter shared_pool
 13 
 14 NAME                                 TYPE        VALUE
 15 ------------------------------------ ----------- ------------------------------
 16 shared_pool_reserved_size            big integer 10905190
 17 shared_pool_size                     big integer 32M
 18 SYS@orcl>
 19 SYS@orcl>

View Code

16：查看并修改大池的大小；

  1 SYS@orcl> show parameter large_pool
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 large_pool_size                      big integer 0
  6 SYS@orcl> alter system set large_pool_size=16m;
  7 
  8 System altered.
  9 
 10 SYS@orcl> show parameter large_pool
 11 
 12 NAME                                 TYPE        VALUE
 13 ------------------------------------ ----------- ------------------------------
 14 large_pool_size                      big integer 16M
 15 SYS@orcl>

View Code

17：查看Java 池信息

  1 SYS@orcl> show parameter java_pool
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 java_pool_size                       big integer 0
  6 SYS@orcl> alter system set java_pool_size=10m;
  7 
  8 System altered.
  9 
 10 SYS@orcl> show parameter java_
 11 
 12 NAME                                 TYPE        VALUE
 13 ------------------------------------ ----------- ------------------------------
 14 java_jit_enabled                     boolean     TRUE
 15 java_max_sessionspace_size           integer     0
 16 java_pool_size                       big integer 16M
 17 java_soft_sessionspace_limit         integer     0
 18 SYS@orcl>

View Code

18：显示当前用户进程PGA 信息：

  1 SYS@orcl> show parameter pga;
  2 
  3 NAME                                 TYPE        VALUE
  4 ------------------------------------ ----------- ------------------------------
  5 pga_aggregate_target                 big integer 0
  6 SYS@orcl>

View Code

19:查看当前实例数据库进程

  1 SYS@orcl> set pagesize 50;
  2 SYS@orcl> select name,description from v$bgprocess;
  3

`20：所有常用字典表

  1 -----基本数据字典及其说明
  2 select  * from dba_tablespaces;---关于表空间的信息
  3 select  * from dba_ts_quotas ;---所有用户表空间的限额
  4 select  * from dba_free_space;--所有表空间中自由分区
  5 select * from  dba_segments;--描述数据库中所有段的储存空间；
  6 select * from  dba_extents;--数据库中所有分区的信息
  7 select  * from  dba_tables;---数据库中所有数据表的描述
  8 select * from  dba_tab_columns;---所有表、视图 及簇的列
  9 select * from dba_views ;--数据库中所有的视图信息；
 10 select * from synonyms;--关于同义词的信息查询；
 11 select * from dba_sequences;--所有用户序列号信息
 12 select * from dba_constraints;--所有用户表的约束信息；
 13 select *  from dba_indexes;--关于数据库索引的描述；
 14 select * from dba_ind_columns;--在所有表及簇上压缩索引的列
 15 select * from dba_triggers;--所有用户的触发器信息 
 16 select  * from dba_source;--所有用户储存过程的信息；
 17 select * from dba_data_files;--查询关于数据库文件的信息；
 18 select  * from dba_tab_privs;--查询关于对象授权的信息
 19 select  * from  dba_objects;--数据库中所有的对象；
 20 select * from  dba_users;--关于数据库中所有用户的信息；
 21 
 22 
 23 ------------常用动态性能视图
 24 select * from v$database ;---描述关于数据库的相关信息
 25 select  * from v$datafile;--数据库使用的数据文件信息
 26 select * from v$log;--从控制文件中提取有关重做日志组的信息
 27 select * from v$logfile;--有关实例重置日志组文件名及其位置的信息
 28 select * from v$archived_log;--记录归档日志文件的基本信息
 29 select * from v$archive_dest;--记录归档日志文件的路径信息
 30 select * from v$controlfile ;--描述控制文件的相关信息
 31 select * from v$instance ; ---记录实例的基本信息
 32 select * from v$system_parameter;--显示实例当前有效的参数信息
 33 select * from v$sga;--显示实例的SGA区的大小
 34 select * from v$sgastat;--统计SGA使用情况的信息
 35 select * from v$parameter ;-- 记录初始化参数文件中所有项的值
 36 select * from v$lock ;--通过访问数据库会话，设置对象锁的所有信息
 37 select * from v$session;--有关会话的信息
 38 select * from v$sqltext;--记录SQL语句的信息
 39 select * from v$sql;--记录SQL语句的详细信息
 40 select * from  v$bgprocess;--显示后台进程信息；
 41 select * from v$process;--当前进程的信息
 42 
 43

21：如何查询oracle数据库中的各种角色

  1   --1. 查询oracle中所有用户信息
  2   select * from dba_users;
  3   --2. 只查询用户和密码
  4   select username,password from dba_users;
  5   --3. 查询当前用户信息
  6   select * from dba_ustats;
  7   --4. 查询用户可以访问的视图文本
  8   select * from dba_varrays;
  9   ---5. 查询数据库中所有视图的文本
 10   select * from dba_views;
 11   select distinct  tablespace_name from  SYS.DBA_FREE_SPACE  where tablespace_name like '%RB%';
 12 
 13   select * from   dba_objects where  object_name='JDE900_F0005';
 14 
 15   select  * from rbods.jde900_f0005;
 16   select * from  rbodm.dim_company;
 17 
 18   　　---查看当前用户的缺省表空间
 19   　　select username,default_tablespace from user_users
 20   　　---查看当前用户的角色
 21   　　select * from user_role_privs
 22   　　---查看当前用户的系统权限和表级权限
 23   　　select * from user_sys_privs
 24   　　select * from user_tab_privs
 25   　　----查看用户下所有的表
 26   　　select * from user_tables
 27   　　---显示用户信息(所属表空间)
 28   　　select default_tablespace,temporary_tablespace from dba_users
 29   　　--显示当前会话所具有的权限
 30   　　select * from session_privs
 31   　---　显示指定用户所具有的系统权限
 32   　　select * from dba_sys_privs
 33   　---　显示特权用户
 34   　　select * from v$pwfile_users
 35   　---　查看名称包含log字符的表
 36   　　select object_name,object_id from user_objects where instr(object_name,'log')>0
 37   　---　查看某表的创建时间
 38   　　select object_name,created from user_objects where object_name='ZW_YINGYEZ'
 39   　---　查看某表的大小
 40   　　select sum(bytes)/(1024*1024) tablesize from user_segments
 41   　　where segment_name='ZW_YINGYEZ'
 42   　---　查看放在ORACLE的内存区里的表
 43   　　select table_name,cache from user_tables where instr(cache,'Y')>0
 44   　---　查看索引个数和类别
 45   　　select index_name,index_type,table_name from user_indexes order by table_name
 46   　---　查看索引被索引的字段
 47   　　select * from user_ind_columns where table_name='CB_CHAOBIAOSJ201004'
 48   　---　查看索引的大小
 49   　　select sum(bytes)/(1024*1024) as indexsize from user_segments
 50   　　where segment_name=upper('AS_MENUINFO')
 51   　---　查看视图信息
 52   　　select * from user_views
 53   　---　查看同义词的名称
 54   　　select * from user_synonyms
 55   　--　查看函数和过程的状态
 56   　　select object_name,status from user_objects where object_type='FUNCTION'
 57   　　select object_name,status from user_objects where object_type='PROCEDURE'
 58   　---　查看函数和过程的源代码
 59   　　select text from all_source where owner=user and name='SF_SPLIT_STRING'
 60   　---　查看表字段
 61   　　select cname from col where tname='ZW_YINGYEZ'
 62   　　select column_name from user_tab_columns where table_name='ZW_YINGYEZ'
 63 
 64   ---查看oracle版本命令：
 65       select * from v$version

22：oracle 增加控制文件的方法：

1：查看参数文件存放位置，并关闭数据库实例 orcl：

  1 [oracle@localhost orcl]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 23:07:02 2018
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> show parameter control_files;
 13 
 14 NAME                                 TYPE        VALUE
 15 ------------------------------------ ----------- ------------------------------
 16 control_files                        string      /u01/app/oracle/oradata/orcl/c
 17                                                  ontrol01.ctl, /u01/app/oracle/
 18                                                  fast_recovery_area/orcl/contro
 19                                                  l02.ctl
 20 
 21 
 22 SYS@orcl> select name from v$controlfile;
 23 
 24 NAME
 25 --------------------------------------------------------------------------------
 26 /u01/app/oracle/oradata/orcl/control01.ctl
 27 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
 28 
 29 SYS@orcl> shutdown immediate;
 30 Database closed.
 31 Database dismounted.
 32 ORACLE instance shut down.
 33 
 34

2：退出sqlplus，复制文件。

  1 SYS@orcl> quit
  2 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
  3 With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4 [oracle@localhost ~]$ cd  /u01/app/oracle/fast_recovery_area/orcl
  5 [oracle@localhost orcl]$ pwd
  6 /u01/app/oracle/fast_recovery_area/orcl
  7 [oracle@localhost orcl]$ ls
  8 control02.ctl
  9 [oracle@localhost orcl]$ cp control02.ctl control03.ctl
 10 [oracle@localhost orcl]$ ls
 11 control02.ctl  control03.ctl

3：登录进入 sqplus ，启动数据库实例进入 nomount状态，并修改控制文件参数

  1 [oracle@localhost ~]$ sqlplus / as sysdba;
  2 
  3 SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 10 00:14:01 2018
  4 
  5 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  6 
  7 
  8 Connected to:
  9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 11 
 12 SYS@orcl> startup nomount;
 13 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 14 ORACLE instance started.
 15 
 16 Total System Global Area 1221992448 bytes
 17 Fixed Size                  1344596 bytes
 18 Variable Size             771754924 bytes
 19 Database Buffers          436207616 bytes
 20 Redo Buffers               12685312 bytes
 21 SYS@orcl> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl', '/u01/app/oracle/fast_recovery_area/orcl/control03.ctl' scope=spfile;
 22 
 23 System altered.
 24 
 25 SYS@orcl> shutdown immediate;
 26 ORA-01507: database not mounted
 27 
 28 
 29 ORACLE instance shut down.
 30 SYS@orcl> startup
 31 ORACLE instance started.
 32 
 33 Total System Global Area 1221992448 bytes
 34 Fixed Size                  1344596 bytes
 35 Variable Size             771754924 bytes
 36 Database Buffers          436207616 bytes
 37 Redo Buffers               12685312 bytes
 38 Database mounted.
 39 Database opened.
 40 SYS@orcl> create pfile from spfile;
 41 
 42 File created.
 43 
 44 SYS@orcl> select name from v$controlfile;
 45 
 46 NAME
 47 --------------------------------------------------------------------------------
 48 /u01/app/oracle/oradata/orcl/control01.ctl
 49 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
 50 /u01/app/oracle/fast_recovery_area/orcl/control03.ctl
 51 
 52 SYS@orcl>

  1 SYS@orcl> shutdown immediate;
  2 Database closed.
  3 Database dismounted.
  4 ORACLE instance shut down.
  5 SYS@orcl> startup
  6 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  7 ORACLE instance started.
  8 
  9 Total System Global Area 1221992448 bytes
 10 Fixed Size                  1344596 bytes
 11 Variable Size             771754924 bytes
 12 Database Buffers          436207616 bytes
 13 Redo Buffers               12685312 bytes
 14 Database mounted.
 15 Database opened.
 16 SYS@orcl>

23:Oracle 查看表空间的大小及使用情况sql语句

注：本段内容来自：《 Oracle 查看表空间的大小及使用情况sql语句》

  1 --1、查看表空间的名称及大小 
  2 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
  3 FROM dba_tablespaces t, dba_data_files d
  4 WHERE t.tablespace_name = d.tablespace_name
  5 GROUP BY t.tablespace_name;
  6 --2、查看表空间物理文件的名称及大小 
  7 SELECT tablespace_name,
  8 file_id,
  9 file_name,
 10 round(bytes / (1024 * 1024), 0) total_space
 11 FROM dba_data_files
 12 ORDER BY tablespace_name;
 13 --3、查看回滚段名称及大小 
 14 SELECT segment_name,
 15 tablespace_name,
 16 r.status,
 17 (initial_extent / 1024) initialextent,
 18 (next_extent / 1024) nextextent,
 19 max_extents,
 20 v.curext curextent
 21 FROM dba_rollback_segs r, v$rollstat v
 22 WHERE r.segment_id = v.usn(+)
 23 ORDER BY segment_name;
 24 --4、查看控制文件 
 25 SELECT NAME FROM v$controlfile;
 26 --5、查看日志文件 
 27 SELECT MEMBER FROM v$logfile;
 28 --6、查看表空间的使用情况 
 29 SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
 30 FROM dba_free_space
 31 GROUP BY tablespace_name;
 32 SELECT a.tablespace_name,
 33 a.bytes total,
 34 b.bytes used,
 35 c.bytes free,
 36 (b.bytes * 100) / a.bytes "% USED ",
 37 (c.bytes * 100) / a.bytes "% FREE "
 38 FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
 39 WHERE a.tablespace_name = b.tablespace_name
 40 AND a.tablespace_name = c.tablespace_name;
 41 --7、查看数据库库对象 
 42 SELECT owner, object_type, status, COUNT(*) count#
 43 FROM all_objects
 44 GROUP BY owner, object_type, status;
 45 --8、查看数据库的版本　 
 46 SELECT version
 47 FROM product_component_version
 48 WHERE substr(product, 1, 6) = 'Oracle';
 49 --9、查看数据库的创建日期和归档方式 
 50 SELECT created, log_mode, log_mode FROM v$database;
 51 --1G=1024MB 
 52 --1M=1024KB 
 53 --1K=1024Bytes 
 54 --1M=11048576Bytes 
 55 --1G=1024*11048576Bytes=11313741824Bytes 
 56 SELECT a.tablespace_name "表空间名",
 57 total "表空间大小",
 58 free "表空间剩余大小",
 59 (total - free) "表空间使用大小",
 60 total / (1024 * 1024 * 1024) "表空间大小(G)",
 61 free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
 62 (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
 63 round((total - free) / total, 4) * 100 "使用率 %"
 64 FROM (SELECT tablespace_name, SUM(bytes) free
 65 FROM dba_free_space
 66 GROUP BY tablespace_name) a,
 67 (SELECT tablespace_name, SUM(bytes) total
 68 FROM dba_data_files
 69 GROUP BY tablespace_name) b
 70 WHERE a.tablespace_name = b.tablespace_name
 71 
 72

 
-------- 统计表空间数据量大小
 select tablespace_name ,sum(bytes) / 1024 / 1024/1024 as GB　from dba_data_files group by tablespace_name;
 
 ----重新表空间下 的表信息
 select table_name from all_tables where TABLESPACE_NAME='POST_STADY' ;
 
 ------重新表空间下 的表信息 
 select * from user_tables where TABLESPACE_NAME='POST_STADY';
 
 ----重新数据库用户下的表信息 
 select * from all_tables where owner='POST_STADY';
 
 
 ----- 统计数据库用户下的表数据量大小
 select  aa.* from 
 
 (select a.segment_name,
       a.segment_type,
       a.bytes,
       a.bytes /1024/1024  byte_m,
       b.created
  from dba_segments a
 inner join all_objects b
    on b.object_type = 'TABLE'
   and a.owner = b.owner
   and a.segment_name = b.object_name
 where a.owner = 'POST_STADY'
   and a.segment_type = 'TABLE' /* and a.bytes>50000000*/
 ) aa ,all_tables b where aa.segment_name=b.table_name and  b.owner='POST_STADY'
 order by aa.byte_m asc  ;

查看表空间使用情况：

  1 --查看表空间使用情况： 
  2   SELECT tbs 表空间名,
  3          sum(totalM) 总共大小M,
  4          sum(usedM) 已使用空间M,
  5          sum(remainedM) 剩余空间M,
  6          sum(usedM) / sum(totalM) * 100 已使用百分比,
  7          sum(remainedM) / sum(totalM) * 100 剩余百分比
  8     FROM (SELECT b.file_id ID,
  9                  b.tablespace_name tbs,
 10                  b.file_name name,
 11                  b.bytes / 1024 / 1024 totalM,
 12                  (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 usedM,
 13                  sum(nvl(a.bytes, 0) / 1024 / 1024) remainedM,
 14                  sum(nvl(a.bytes, 0) / (b.bytes) * 100),
 15                  (100 - (sum(nvl(a.bytes, 0)) / (b.bytes) * 100))
 16             FROM dba_free_space a, dba_data_files b
 17            WHERE a.file_id = b.file_id
 18            GROUP BY b.tablespace_name, b.file_name, b.file_id, b.bytes
 19            ORDER BY b.tablespace_name)
 20    GROUP BY tbs
 21

  1 select b.tablespace_name "表空间",
  2        b.bytes / 1024 / 1024 "大小M",
  3        (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 "已使用M",
  4        substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) "利用率"
  5   from dba_free_space a, dba_data_files b
  6  where a.file_id = b.file_id
  7    --and b.tablespace_name = 'SYSTEM'
  8  group by b.tablespace_name, b.file_name, b.bytes
  9  order by b.tablespace_name;

oracle 表空间不足解决办法大全

  1 
  2 -- 【解决办法-原因一】
  3 --只要将表空间设置为足够大，并设置为自增长即可。
  4 --1、扩展表空间 
  5  alterdatabase datafile 'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' resize 50m;
  6  -- 注：50m，是表空间大小，可以根据实际需要加大，但最大不得超过32G
  7 --2、自动增长 
  8  alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' autoextend onnext 50m maxsize 500m;
  9 --【解决办法-原因二】
 10 --因为表空间中的数据文件已经足够大（达到32G），所以，这时仅仅增加表空间大小是不行的。 这个时候，我们可以增加该表空间的数据文件，这样表空间的大小即变为64G了。
 11 ALTER TABLESPACE aaa ADD DATAFILE 'E:\APP\ORACLE11GR2\ORADATA\ORCL\aaa_DATA02.DBF' SIZE 32767M;

查看system和sysaux表空间是否是自动扩展的。

  1 　SQL>select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files;
  2 
  3 　--　如果为自动扩展那么AUTOEXTENSIBLE字段的值应为　YES，是否为NO；INCREMENT_BY 这个为每次自动扩展的空间大小。
  4

开启或者关闭：表空间是否自动扩展

  1 ---开启自动扩展功能语法：
  2 alter database datafile '对应的数据文件路径信息' autoextend on;
  3 --关闭自动扩展功能语法：
  4 alter database datafile '对应的数据文件路径信息' autoextend off;

新增表空间

  1  --oracle增加表空间数据文件大小
  2 --
  3 --1.增加数据文件
  4 
  5 ALTER TABLESPACE ljjgbs_east ADD DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' SIZE 50M;
  6 
  7 --2.新增数据文件，并允许自动增长
  8 
  9 ALTER TABLESPACE ljjgbs_east ADD DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 10 
 11 --3.允许存在的数据文件自动增长
 12 
 13 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' AUTOEXTEND ON NEXT 5M maxsize unlimited;
 14 
 15 --4.手动增加存在的数据文件的大小
 16 
 17 ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/ljjgbs/ljjgbs_east_data.dbf' RESIZE 20000M;
 18

创建表空间

  1 create tablespace GFPORTAL
  2 datafile 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\PDBORCL\GFPORTAL.DBF' size 100M --生成数据文件并定义文件大小
  3 autoextend on next 100M maxsize unlimited logging    --设置自动扩展
  4 extent management local autoallocate
  5 segment space management auto;
  6 
  7 
  8 --创建 多个表空间文件
  9 
 10 create tablespace sync_plus_1   datafile
 11   '/u01/app/oracle/oradata/orcl/sync_plus_1_01.dbf' size 500M  autoextend on next 100M
 12  , '/u01/app/oracle/oradata/orcl/sync_plus_1_02.dbf' size 500M  autoextend on next 100M
 13  , '/u01/app/oracle/oradata/orcl/sync_plus_1_03.dbf' size 500M  autoextend on next 100M
 14  , '/u01/app/oracle/oradata/orcl/sync_plus_1_04.dbf' size 500M  autoextend on next 100M
 15  , '/u01/app/oracle/oradata/orcl/sync_plus_1_05.dbf' size 500M  autoextend on next 100M
 16  , '/u01/app/oracle/oradata/orcl/sync_plus_1_06.dbf' size 500M  autoextend on next 100M
 17  , '/u01/app/oracle/oradata/orcl/sync_plus_1_07.dbf' size 500M  autoextend on next 100M
 18  , '/u01/app/oracle/oradata/orcl/sync_plus_1_08.dbf' size 500M  autoextend on next 100M maxsize unlimited logging extent management local autoallocate segment space management auto
 19 
 20

增加多个表空间

  1 --- 执行该语句给  TEST_SYNC  用户授权，此时  TEST_SYNC  用户就可以登录了。 
  2 
  3 ----给表空间增加数据文件
  4 ALTER TABLESPACE sync_plus_1 ADD DATAFILE  'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_01.dbf' size 500M  autoextend on next 100M
  5  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_02.dbf' size 500M  autoextend on next 100M
  6  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_03.dbf' size 500M  autoextend on next 100M
  7  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_04.dbf' size 500M  autoextend on next 100M
  8  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_05.dbf' size 500M  autoextend on next 100M
  9  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_06.dbf' size 500M  autoextend on next 100M
 10  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_07.dbf' size 500M  autoextend on next 100M
 11  , 'D:\SOFT\DEVELOP\DATABASE\ORACLE\ORADATA\ORCL\SYNC_PLUS_1_08.dbf' size 500M  autoextend on next 100M maxsize unlimited ;
 12 
 13 
 14

删除表空间

  1 ---删除表空间：删除数据文件语法：
  2  ALTER TABLESPACE 表空间名 DROP DATAFILE 数据文件名;
  3 ---删除表空间：所有的数据文件
  4 
  5

24: 因：审计表占用了大量资源导致————》system表空间爆满解决方法

注：本段内容来自于：《 system表空间爆满解决方法》

  1 --问题描述：
  2 --对数据库做检查，发现system表空间持续占满99%。使用如下语句查看：
  3 SQL> select b.tablespace_name "表空间",b.bytes/1024/1024 "大小M",(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率" from dba_free_space a,dba_data_files b where a.file_id=b.file_id and b.tablespace_name='SYSTEM' group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;
  4 
  5 表空间            大小M        已使用M     利用率
  6 ------------- ----------     ----------     ----------
  7 SYSTEM             6770          6505         96.08%
  8 
  9 --从dba_segments中找出占用SYSTEM表空间中排名前10位的大对象：
 10 SQL> col segment_name for a15;
 11 SQL> SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;
 12 
 13 SEGMENT_NAME     MB
 14 --------------------    ----------
 15 AUD$                         6016
 16 IDL_UB1$                    280
 17 SOURCE$                     80
 18 IDL_UB2$                     33
 19 C_TOID_VERSION#      24
 20 C_OBJ#_INTCOL#        18
 21 I_SOURCE1                   16
 22 ARGUMENT$               13
 23 C_OBJ#                         13
 24 JAVA$MC$                   12
 25 --发现是AUD$审计表占用资源量大。为了避免对整体性能造成影响，决定把AUD$迁移到其他表空间
 26 --解决步骤：
 27 --1，新建aud_space表空间和aud_index索引表空间
 28 --2，执行迁移命令，将AUD$表相关移到审计表空间中：
 29 SQL> alter table aud$ move tablespace aud_space;
 30 
 31 SQL> alter table audit$ move tablespace aud_space;
 32 
 33 SQL> alter index i_audit rebuild online tablespace aud_index;
 34 
 35 SQL> alter table audit_actions move tablespace aud_space;
 36 
 37 SQL> alter index i_audit_actions rebuild online tablespace aud_index;
 38 --3，再此查看SYSTEM表空间使用状态：
 39 SQL> select b.tablespace_name "表空间",b.bytes/1024/1024 "大小M",(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 "已使用M",substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "利用率" from dba_free_space a,dba_data_files b where a.file_id=b.file_id and b.tablespace_name='SYSTEM' group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;
 40 表空间            大小M        已使用M     利用率
 41 ------------- ----------     ----------     ----------
 42 SYSTEM             6770       792.3125     11.70
 43 可见SYSTEM表空间已经降下来了。
 44 --4，为了安全起见，AUD$表数据目前3千多万，数据量大，后期考虑truncate此表，清空数据。
 45

注：本段内容来自：《 system表空间用满解决方法》

如果system表空间不是自动扩展，空间用满甚至会出现数据库无法登陆。使用任何用户登录均报出异常。

系统表空间正常情况下只存放了数据字典之类的东西，所以占用的空间一般在500M以下。如果你的系统表空间占用比较多的空间，可能有以下几方面的原因：
1）没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间
2）开启了审计,请检查此表的大小AUD$
你可以运行以下查询来检查一下系统表空间哪些表比较大：
  1   SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
  2                 where tablespace_name='SYSTEM' group by segment_name)
  3                 where sx>100 order by sx desc;
查看该表纪录数
  1  select count(*) sum from AUD$;
解决方法大概可分为以下几种:
一、为system表空间另外新增一个数据文件。
  1 alter tablespace system add datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 1024M;
二、更改system表空间的数据文件SYSTEM.dbf分配空间。
  1 1.alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' autoextend on;
  2 2.alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 1024M;
  3 
三、truncate掉AUD$表并关闭审计功能(我是使用的这种,效果立竿见影,不过如果不关闭此功能, 需要定期清理此表)：
  1 SQL> alter system set audit_trail=none scope=spfile;
  2 
  3              SQL>shutdown immediate;
  4 
  5              SQL>startup;
  6 
四、将AUD$默认表空间由system移出。

25：oracle删除(释放)数据文件/表空间流程

注：本段内容来自：《 oracle删除(释放)数据文件/表空间流程》

oracle删除(释放)数据文件/表空间流程

生产环境：数据库里空间不足，niptest 表空间251G，只使用了17G

再alter database datafile '...../niptest1' resize 10G; 的时候说超出了范围要求

由于表变动比较频繁，高水位值比较大

(高水位 HWM “High Water Mark “：oracle中block有没有使用的分界线，它会随着数据的insert而上升，但它并不会随数据的delete而下降，因此全表扫描的时间并不因数据的delete而减少，相反可能由于块清除反而全表扫描时间增加）

删除表空间步骤：

1) 批量将niptest表空间中的表move 到USERS表空间，再删除表空间niptest

首先看下此表空间内的表 move到其他表空间防止数据丢失
  1 select * from dba_tables where tablespace_name='NIPTEST';
  2 select * from dba_extents where tablespace_name='NIPTEST';
  3 select * from dba_segments where tablespace_name='NIPTEST';
  4 SELECT 'alter table '||owner||'.'||table_name||' move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; ---批量把表移动到其他表空间
****** move （降低高水位）

优点：可以移动表到其他表空间，在执行命令时不需要执行alter table table_name enable row movement

缺点：表move 会导致表中的索引失效，要rebuild；同时表会产生行级锁......；在此如果表中有LOB字段时要用一下命令来实现表空间移动：alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name;也可以单独move lob，index要rebuild

****** shrink space

优点：降低高水位时索引不会失效

缺点：不能将表移动到其他表空间；高水位降低效果没有move明显；同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁.......，shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo 如果表中索引很少可以建议使用move降低高水位

2) 移动完表发现主键和索引还是在源表空间
  1 SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS';   -->查看原表空间的主键和索引
  2 alter index XX rebuild tablespace ;
---> 批量执行索引重建
  1 SELECT 'alter index  '||owner||'.'||segment_name||' rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST';  --- 批量将主键索引重建到其他表空间
  2 select * from dba_segments where tablespace_name='NIPTEST';---弄完上面的操作，这里还有数据，不要在意，那是回收站的，无需修改直接删表空间即清空
(3) 表都移动完了之后先把数据文件offline drop再删除数据文件
  1 alter database datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop;
---> 查看下数据文件状态
  1 select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST'; -----offline
--> 再删除表空间
  1 drop tablespace niptest; --> 删除表空间，但不删除其文件
  2 drop tablespace niptest including contents; -->删除表空间同时删除表空间的数据对象
  3 drop tablespace niptest including contents and datafiles;
--> 删除表空间时删除数据对象及其OS系统文件一起删除，，以便释放空间,前提表空间不能是数据库默认表空间
否则会报错：ORA-12919: Can not drop the default permanent tablespace

(4) 怎么查看下数据库的默认表空间是什么：
  1 select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
如果您删除的表空间是数据库默认表空间要用一下命令来
  1 --更换数据库默认表空间：
  2 alter database default tablespace users;
  3  --再执行：
  4  drop tablespace niptest including contents and datafiles;
[root@kfdb49 kfdb]# df -hl --> 看下OS系统空间情况 --- 表空间释放

------创建表空间的语句：
  1 create tablespace niptest datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local;
有的人会想着再创建一个niptest 为10G的表空间，避免后期imp时源表的表空间是niptest

：其实不需要，如果库中没有niptest表空间，就算imp源表的表空间是niptest，也会导入到用户的默认表空间，有niptest表空间的话则会导入到niptest表空间(如果后续不想拥有niptest表空间就要斩草除根的将niptest删除之后不要创建)

回收unlimited tablespace 给予额外权限niptest表空间权限会正常导入，否则报错

额外权限和用户默认表空间是一个的话可以正常导入，否则知道表结构到用户默认表空间

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

posted @ 2017-12-10 21:25 一品堂.技术学习笔记阅读(13461) 评论(0) 编辑收藏举报

刷新页面返回顶部

✧*꧁一品堂.技术学习笔记꧂*✧.

Oracle DBA 必须掌握的 查询脚本：

Oracle DBA 必须掌握的 查询脚本：

0：启动与关闭

1：通过v$parameter数据字段来查询oracle标准数据块的大小

2：通过 dict 查看数据库中数据字典的信息

3 : 通过 v$fixed_view_definition 查看数据库中内部系统表的信息

4：通过查询 dba_data_files 数据来了解Oracle系统的数据文件信息

5：查看 临时文件信息： dba_temp_files 、v$tempfile;

6：获取控制文件信息： v$controlfile 数据字典

7：查看日志文件信息： v$logfile;

8： 查看oacle 系统是否采用归档模式： v$database

9：查询归档日志文件的所在路劲信息： show parameter log_archive_desc

10：查询视图v$parameter 进行确定参数的默认值是否修改过

11：修改系统参数：

12：创建 一个密码文件 其sys 口令为：oracle

13:查询 警告文件位置

14：查看 跟踪文件的位置：

15：查看 并修改共享池大小

16：查看并修改大池的大小；

17：查看Java 池信息

18：显示当前用户进程PGA 信息：

19:查看当前实例数据库进程

`20： 所有常用字典表

21：如何查询oracle数据库中的各种角色

22：oracle 增加控制文件的方法：

23:Oracle 查看表空间的大小及使用情况sql语句

查看表空间使用情况：

oracle 表空间不足解决办法大全

查看system和sysaux表空间是否是自动扩展的。

开启或者关闭 ：表空间 是否 自动扩展

新增 表空间

创建表空间

增加多个表空间

删除表空间

24: 因：审计 表占用了大量资源 导致————》system表空间爆满解决方法

25：oracle删除(释放)数据文件/表空间流程

oracle删除(释放)数据文件/表空间流程

1) 批量将niptest表空间中的表move 到USERS表空间，再删除表空间niptest

2) 移动完表发现 主键和索引还是在源表空间

(3) 表都移动完了 之后 先把数据文件offline drop再删除数据文件

(4) 怎么查看下数据库的默认表空间是什么：

公告

✧꧁一品堂.技术学习笔记꧂✧.

Oracle DBA 必须掌握的查询脚本：

Oracle DBA 必须掌握的查询脚本：

2：通过　dict 查看数据库中数据字典的信息

5：查看临时文件信息： dba_temp_files 、v$tempfile;

8：查看oacle 系统是否采用归档模式： v$database

12：创建一个密码文件其sys 口令为：oracle

13:查询警告文件位置

14：查看跟踪文件的位置：

15：查看并修改共享池大小

`20：所有常用字典表

开启或者关闭：表空间是否自动扩展

新增表空间

24: 因：审计表占用了大量资源导致————》system表空间爆满解决方法

2) 移动完表发现主键和索引还是在源表空间

(3) 表都移动完了之后先把数据文件offline drop再删除数据文件