oracle 常用笔记_dastemp-CSDN博客

本文链接：https://blog.csdn.net/sustwct/article/details/6119798

sqlplus "/ as sysdba"

摸清数据库情况

查看oracle数据库的名字、创建日期

SQL> select name, created, log_mode, open_mode from v$database;

NAME CREATED LOG_MODE OPEN_MODE
--------- ------------ ------------ ----------
REALTY 30-JAN-07 ARCHIVELOG READ WRITE

查看主机名，实例名，数据库版本

SQL> col host_name for a15
SQL> select host_name,instance_name,version from v$instance;

HOST_NAME INSTANCE_NAME VERSION
--------------- ---------------- -----------------
GisDb realty 10.2.0.1.0

set linesize 200;

查看数据库版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

查看控制文件

SQL> set linesize 400;
SQL> col name for a70;
SQL> select * from v$controlfile;

STATUS NAME                                                                   IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ---------------------------------------------------------------------- --- ---------- --------------
        /opt/FangChanData/oracle10g/oradata/realty/control01.ctl               NO       16384            654
        /opt/FangChanData/oracle10g/oradata/realty/control02.ctl               NO       16384            654
        /opt/FangChanData/oracle10g/oradata/realty/control03.ctl               NO       16384            654

查看重做日志配置的信息

SQL> select group#, members, bytes, status, archived from v$log;

    GROUP#    MEMBERS      BYTES STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          2   52428800 INACTIVE         YES
         2          2   52428800 CURRENT          NO
         3          2   52428800 INACTIVE         YES
         4          2   52428800 INACTIVE         YES
         5          2   52428800 INACTIVE         YES
         6          2   52428800 INACTIVE         YES
         7          2   52428800 INACTIVE         YES
         8          2   52428800 INACTIVE         YES

查看重做日志的位置

SQL> column member format a70;
SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
         3 STALE   ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo03.log                  NO
         2         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo02.log                  NO
         1         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo01.log                  NO
         4         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo04.log                  NO
         5         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo05.log                  NO
         6         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo06.log                  NO
         7         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo07.log                  NO
         8         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo08.log                  NO
         1         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo01b.log                 NO
         2         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo02b.log                 NO
         3 STALE   ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo03b.log                 NO

    GROUP# STATUS TYPE    MEMBER                                                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
         4         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo04b.log                 NO
         5         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo05b.log                 NO
         6         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo06b.log                 NO
         7         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo07b.log                 NO
         8         ONLINE /opt/FangChanData/oracle10g/oradata/realty/redo08b.log                 NO

查看归档情况

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1980
Next log sequence to archive   1987
Current log sequence           1987

查看表空间及表空间状态

SQL> select tablespace_name, block_size, status, contents, logging from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS LOGGING
------------------------------ ---------- --------- --------- ---------
SYSTEM                               8192 ONLINE    PERMANENT LOGGING
UNDOTBS1                             8192 ONLINE    UNDO      LOGGING
SYSAUX                               8192 ONLINE    PERMANENT LOGGING
TEMP                                 8192 ONLINE    TEMPORARY NOLOGGING
USERS                                8192 ONLINE    PERMANENT LOGGING
EXAMPLE                              8192 ONLINE    PERMANENT NOLOGGING
DASDATA                              8192 ONLINE    PERMANENT LOGGING
DASINDEXES                           8192 ONLINE    PERMANENT LOGGING
DASINDEXES_ARCHIVES                  8192 ONLINE    PERMANENT LOGGING
DASTEMP                              8192 ONLINE    TEMPORARY NOLOGGING
JYCQ                                 8192 ONLINE    PERMANENT LOGGING

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS LOGGING
------------------------------ ---------- --------- --------- ---------
JYCQDATA                             8192 ONLINE    PERMANENT LOGGING
JYCQDATALS                           8192 ONLINE    PERMANENT LOGGING
JYCQDATAZS                           8192 ONLINE    PERMANENT LOGGING
JYCQFORMDATA                         8192 ONLINE    PERMANENT LOGGING
JYCQFORMTEMP                         8192 ONLINE    TEMPORARY NOLOGGING
JYCQINDEX                            8192 ONLINE    PERMANENT LOGGING
JYCQINDEXES                          8192 ONLINE    PERMANENT LOGGING
JYCQTEMP                             8192 ONLINE    TEMPORARY NOLOGGING
SCCDATA                              8192 ONLINE    PERMANENT LOGGING
SCCFORMTEMP                          8192 ONLINE    TEMPORARY NOLOGGING
SCCFORM                              8192 ONLINE    PERMANENT LOGGING

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS LOGGING
------------------------------ ---------- --------- --------- ---------
SCCINDEXES                           8192 ONLINE    PERMANENT LOGGING
SCCTEMP                              8192 ONLINE    TEMPORARY NOLOGGING
GISDATA                              8192 ONLINE    PERMANENT LOGGING
GISTEMP                              8192 ONLINE    TEMPORARY NOLOGGING
SMLANDFORM                           8192 ONLINE    PERMANENT LOGGING
WFMDATA                              8192 ONLINE    PERMANENT LOGGING
WFMDEF                               8192 ONLINE    PERMANENT LOGGING
WFMINST                              8192 ONLINE    PERMANENT LOGGING
WFMINDEXES                           8192 ONLINE    PERMANENT LOGGING
WFMTEMP                              8192 ONLINE    TEMPORARY NOLOGGING
CHGSTEMP                             8192 ONLINE    TEMPORARY NOLOGGING

查看数据文件的文件号、文件名、状态及字节数

SQL> col file_name for a40;
SQL> select file_id,file_name,tablespace_name,status,bytes from dba_data_files;

   FILE_ID FILE_NAME                                TABLESPACE_NAME                STATUS         BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------
         4 /opt/FangChanData/oracle10g/oradata/real USERS                          AVAILABLE    5242880
           ty/users01.dbf

3 /opt/FangChanData/oracle10g/oradata/real SYSAUX AVAILABLE 1258291200
ty/sysaux01.dbf

2 /opt/FangChanData/oracle10g/oradata/real UNDOTBS1 AVAILABLE 482344960
ty/undotbs01.dbf

1 /opt/FangChanData/oracle10g/oradata/real SYSTEM AVAILABLE 1073741824
ty/system01.dbf

FILE_ID FILE_NAME TABLESPACE_NAME STATUS BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------

5 /opt/FangChanData/oracle10g/oradata/real EXAMPLE AVAILABLE 104857600
ty/example01.dbf

6 /opt/FangChanData/oracle10g/oradata/XAFC DASDATA AVAILABLE 1.0737E+10
_DATA/DAS/DASDATA.DBF

7 /opt/FangChanData/oracle10g/oradata/XAFC DASINDEXES AVAILABLE 41943040
_DATA/DAS/DASINDEXES.DBF

8 /opt/FangChanData/oracle10g/oradata/XAFC DASINDEXES_ARCHIVES AVAILABLE 51380224

FILE_ID FILE_NAME TABLESPACE_NAME STATUS BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------
_DATA/DAS/DASINDEXES_ARCHIVES.DBF

9 /opt/FangChanData/oracle10g/oradata/XAFC JYCQ AVAILABLE 41943040
_DATA/JYCQ/JYCQ.DBF

10 /opt/FangChanData/oracle10g/oradata/XAFC JYCQDATA AVAILABLE 2.0972E+10
_DATA/JYCQ/JYCQDATA.DBF

11 /opt/FangChanData/oracle10g/oradata/XAFC JYCQDATALS AVAILABLE 52428800
_DATA/JYCQ/JYCQDATALS.DBF

   FILE_ID FILE_NAME                                TABLESPACE_NAME                STATUS         BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------
        12 /opt/FangChanData/oracle10g/oradata/XAFC JYCQDATAZS                     AVAILABLE 2147483648
           _DATA/JYCQ/JYCQDATAZS.DBF

13 /opt/FangChanData/oracle10g/oradata/XAFC JYCQFORMDATA AVAILABLE 545259520
_DATA/JYCQ/JYCQFORMDATA.DBF

14 /opt/FangChanData/oracle10g/oradata/XAFC JYCQINDEX AVAILABLE 2516582400
_DATA/JYCQ/JYCQINDEX.DBF

15 /opt/FangChanData/oracle10g/oradata/XAFC JYCQINDEXES AVAILABLE 41943040
_DATA/JYCQ/JYCQINDEXES.DBF

FILE_ID FILE_NAME TABLESPACE_NAME STATUS BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------

16 /opt/FangChanData/oracle10g/oradata/XAFC SCCDATA AVAILABLE 1073741824
_DATA/SCC/SCCDATA.DBF

17 /opt/FangChanData/oracle10g/oradata/XAFC SCCFORM AVAILABLE 41943040
_DATA/SCC/SCCFORM.DBF

18 /opt/FangChanData/oracle10g/oradata/XAFC SCCINDEXES AVAILABLE 41943040
_DATA/SCC/SCCINDEXES.DBF

19 /opt/FangChanData/oracle10g/oradata/XAFC GISDATA AVAILABLE 1073741824

FILE_ID FILE_NAME TABLESPACE_NAME STATUS BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------
_DATA/GIS/GISDATA.DBF

20 /opt/FangChanData/oracle10g/oradata/XAFC SMLANDFORM AVAILABLE 104857600
_DATA/GIS/SMLANDFORM.DBF

21 /opt/FangChanData/oracle10g/oradata/XAFC WFMDATA AVAILABLE 41943040
_DATA/WFM/WFMDATA.DBF

23 /opt/FangChanData/oracle10g/oradata/XAFC WFMINST AVAILABLE 1073741824
_DATA/WFM/WFMINST.DBF

   FILE_ID FILE_NAME                                TABLESPACE_NAME                STATUS         BYTES
---------- ---------------------------------------- ------------------------------ --------- ----------
        24 /opt/FangChanData/oracle10g/oradata/XAFC WFMINDEXES                     AVAILABLE   41943040
           _DATA/WFM/WFMINDEXES.DBF

22 /opt/FangChanData/oracle10g/oradata/XAFC WFMDEF AVAILABLE 3221225472
_DATA/WFM/WFMDEF.DBF

查看数据库用户及创建时间

SQL> select username, created from dba_users;

USERNAME                       CREATED
------------------------------ ------------
SYSTEM                         30-JUN-05
SYS                            30-JUN-05
MGMT_VIEW                      30-JUN-05
OUTLN                          30-JUN-05
OLAPSYS                        30-JUN-05
ORDPLUGINS                     30-JUN-05
XDB                            30-JUN-05
SYSMAN                         30-JUN-05
DMSYS                          30-JUN-05
EXFSYS                         30-JUN-05
ORDSYS                         30-JUN-05

USERNAME                       CREATED
------------------------------ ------------
SI_INFORMTN_SCHEMA             30-JUN-05
ANONYMOUS                      30-JUN-05
CTXSYS                         30-JUN-05
WMSYS                          30-JUN-05
DBSNMP                         30-JUN-05
MDSYS                          30-JUN-05
MDDATA                         01-APR-07
HR                             30-JAN-07
DIP                            30-JUN-05
SH                             30-JAN-07
IX                             30-JAN-07

USERNAME                       CREATED
------------------------------ ------------
TSMSYS                         30-JUN-05
OE                             30-JAN-07
PM                             30-JAN-07
BI                             30-JAN-07
SCOTT                          30-JUN-05
DASADMIN                       01-APR-07
JYCQ                           20-AUG-09
DATAINTERACTIVE_FOR_AHSL       21-SEP-09
CHGS                           06-FEB-07
JYCQFORM                       01-APR-07
SCCADMIN                       02-APR-07

USERNAME                       CREATED
------------------------------ ------------
SCCFORM                        01-APR-07
SMHZFCRELIEFMAP                01-APR-07
SMHZFCGIS                      01-APR-07
SMHZFCLANDFORM                 01-APR-07
FCGISADMIN                     01-APR-07
SMLANDFORM                     01-APR-07
WFMADMIN                       01-APR-07
JN                             07-FEB-07
AAAAAA                         13-JUN-07
CHENHY                         07-FEB-07
TEST                           06-APR-07

USERNAME                       CREATED
------------------------------ ------------
LYY                            07-FEB-07
YANGR                          23-JUL-10
WLM                            01-APR-07
DIYA                           06-APR-07
WANGPING                       06-FEB-07
CSRY                           01-APR-07
WYGL                           13-JUN-07
SS                             28-JUL-10
LXL                            07-FEB-07
SONGYL                         25-MAY-10
LEB                            29-OCT-08

USERNAME                       CREATED
------------------------------ ------------
LT                             06-MAR-07
ZSC                            06-FEB-07
ZX                             16-OCT-07
LIERB                          29-OCT-08
ZYD                            07-FEB-07
BAICY                          19-MAR-09
ZY                             07-FEB-07
ZRJ                            23-JUL-10
MAHF                           14-MAR-09
LANGY                          07-FEB-07
ADMINMAN                       08-AUG-07

USERNAME                       CREATED
------------------------------ ------------
PANYI                          17-OCT-07
TTT                            09-MAR-07
SJKY                           07-FEB-07
SCAN2                          01-APR-07
WSH                            07-FEB-07
SCAN                           03-APR-07
WL                             07-FEB-07
ZHAOYD                         19-APR-07
NIUXD                          29-OCT-08
SCAN3                          02-APR-07
YR                             03-NOV-09

查看所有表的表名；

select table_name ,tablespace_name from dba_tables where tablespace_name = '表空间名';
需要DBA权限 
select table_name ,tablespace_name from user_tables where tablespace_name = '表空间名';
只查当前用户的不需DBA权限

查看拥有此字段的所有表；

select * from user_tab_columns a where a.COLUMN_NAME='';

从表中删除特定的值

SELECT TOPICS from CHANGESPROCESS WHERE TOPICS='test';

delete from CHANGESPROCESS WHERE TOPICS='test';

commit;

重命名表

alter table A rename to B ；

oracle 常用 笔记

oracle 常用笔记