Oracle 命令汇总

1.使用Sqlplus 连接数据库实例

sqlplus username[/password][@connect_identifier][as sysoper|sysdba]

2. P23

Oracle 数据库实例的启动过程分为3个步骤，启动实例，加载数据库，打开数据库

打开命令格式： startup [nomout|mount|open|force][resetrict][pfile=filename]

racle 数据库实例的启动过程分为3个步骤，关闭实例，卸载数据库，关闭数据库

关闭命令格式：shutdown [normal|transactional|immediate|abort]

3.v$parameter 数据字典查询oracle 标准数据块的大小（DB_BLOCK_SIZE)，Oracle 支持不同的数据块大小，数据块的大小是操作系统的整数倍

select name,value from v$parameter where name='DB_BLOCK_SIZE';

4.格式化列的展示

COLUMN
Syntax
COL[UMN] [{column|expr} [option ...]]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED

示例：https://blog.csdn.net/linfenliang/article/details/39374531

https://blog.csdn.net/swfcsunboy/article/details/1787695?utm_source=blogxgwz4

5.通过dict 查看数据库总数据字典的信息（system 表空间主要用于存放数据字典和系统内部表

select * from dict;

select * from v$fixed_view_definition；  --查看系统内部表的具体信息；

6.查询用户对象所在的表空间

SELECT OWNER         AS OWNER
      ,'TABLE'       AS SEGMENT_TYPE
      ,TABLE_NAME    AS SEGMENT_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME=&TABLESPACE_NAME
UNION ALL
SELECT OWNER         AS OWNER
      ,'INDEX'       AS SEGMENT_TYPE
      ,INDEX_NAME    AS SEGMETN_NAME
FROM DBA_INDEXES
WHERE TABLESPACE_NAME=&TABLESPACE_NAME
UNION ALL
SELECT OWNER         AS OWNER
      ,'LOBSEGMENT'  AS SGEMENT_TYPE
      ,SEGMENT_NAME  AS SEGMENT_NAME
FROM DBA_LOBS
WHERE TABLESPACE_NAME=&TABLESPACE_NAME;

 

SELECT  E.SEGMENT_TYPE          AS SEGMENT_TYPE
      , E.SEGMENT_NAME          AS SEGMENT_NAME
      , F.FILE_NAME             AS FILE_NAME
      , SUM(E.BYTES)/1024/1024  AS SEGMENT_SIZE
FROM DBA_EXTENTS E
INNER JOIN DBA_DATA_FILES F ON E.FILE_ID= F.FILE_ID
--WHERE F.FILE_NAME =&FILE_NAME
GROUP BY  E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME
ORDER BY 4 DESC;