Oracle基本操作

作者: ArthurIsUsed | 来源:发表于2020-05-19 16:42 被阅读0次

Oracle SQL基本操作
oracle基本操作
Oracle基本操作
Oracle基本操作
Oracle数据库基本操作
Oracle基本操作命令
Oracle的SQL基本操作
03_oracle基本操作
PLSQL Developer基本
sql*plus入门

Oracle基本概念

Database
A database is a set files, located on disk, that store data. These files can exist independently of a database instance.
Database instance
A instances is a set of memory structures that manage database files. The instance consists of a share memory area, called the system global area(SGA) and a set of background processes. An instance can exist indepandently of database files.

SYS是Oracle数据库中权限最高的帐号，具有create database的权限，而system没有这个权限，sys的角色是sysdba，system的角色是sysoper。
SYS用户具有DBA权限，并且拥有SYS模式，只能通过SYSDBA登陆数据库,是Oracle数据库中权限最高的帐号.
SYSTEM具有DBA权限。但没有SYSDBA权限。平常一般用该帐号管理数据库就可以了。
sys拥有数据字典(dictionay),或者说dictionay属于sys schema

查看Oracle启动状态： su - oracle--> sqlplus status--> running is ok
stop & start Oracle
◇： su - oracle
◇： sqlplus / as sysdba
◇： shutdown immediate;
◇： startup;
计算整个库容量大小

SQL> select tablespace_name , sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;

TABLESPACE_NAME    MB
----------------- ----------
KMDATA              6986.625
UNDOTBS1                 150
SYSAUX                  4630
USERS                      5
SYSTEM                  1440

SQL> select sum(bytes)/1024/1024/1024 as GB from dba_data_files;
GB
----------
12.9019775

因为有多个实例，不设置SID，数据库不知道要连哪个实例，如果单实例这不用set
◇： 新建的用户：起码连接和创建表的权限要赋予

# set ORACLE_SID=KMEXP
# sqlplus / as sysdba
# SQL> show parameter db_name
NAME     TYPE      VALUE
-------  -------   ------
db_name  string    kmb2b

SQL> create user kmbi identified by nw12345;
用户已创建。
SQL> grant connect, resource to kmbi;
授权成功。
SQL> revoke connect, resource from kmbi;
撤销成功

查看表空间大小

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

表空间名         表空间大小(M)  已使用空间(M)   使用比    空闲空间(M)   最大块(M)
------------  -------------  -------------  -------  -----------  ---------
SYSTEM                 2420        2369.56   97.92         50.44         50
SYSAUX                16640        15846.5   95.23         793.5        767
USERS                 26.25          24.25   92.38             2       1.25
KMDATA            163715.56      132505.31   80.94      31210.25       3968
UNDOTBS1                150          42.25   28.17        107.75         91
DATASPACE              2000           4.44    0.22       1995.56    1992.56
INDEXSPACE             2000           2.69    0.13       1997.31    1994.88

查看表空间是否自动扩展

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

开启自动扩展，但是每个文件最大是32G
alter database datafile '/u01/app/oracle/data/kmb2b/kmdata01.dbf' autoextend on next 50m maxsize 500m;
新加一个文件
ALTER TABLESPACE "KMDATA" ADD DATAFILE '/u01/app/oracle/data/kmb2b/kmdata04.dbf' SIZE 32767M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
设置列宽
◇： col table_name for A40
◇： set line 200
导出序列的脚本

select 'CREATE SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' MINVALUE '||to_char(MIN_VALUE)||' MAXVALUE '||to_char(MAX_VALUE)||' INCREMENT BY '||to_char(INCREMENT_BY)||' START WITH '||to_char(LAST_NUMBER+CACHE_SIZE*2)||' CACHE '||CACHE_SIZE||
case when ORDER_FLAG='N' then ' NOORDER ' else ' ORDER ' end||case when CYCLE_FLAG='N' then ' NOCYCLE;' else ' CYCLE;' end SQL
from dba_SEQUENCEs
where (SEQUENCE_OWNER = 'KMDATA')
and CACHE_SIZE<>0 and MAX_VALUE!=CACHE_SIZE
and SEQUENCE_NAME in (
'PKGSEQ_USERSTATUSCHANGE',
'SEQ_TELCOMMON',
'SEQ_TRANSID'
)
union all
select 'CREATE SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' MINVALUE '||to_char(MIN_VALUE)||' MAXVALUE '||to_char(MAX_VALUE)||' INCREMENT BY '||to_char(INCREMENT_BY)||' START WITH '||to_char(LAST_NUMBER+1)||' NOCACHE '||
case when ORDER_FLAG='N' then ' NOORDER ' else ' ORDER ' end||case when CYCLE_FLAG='N' then ' NOCYCLE;' else ' CYCLE;' end SQL
from dba_SEQUENCEs
where (SEQUENCE_OWNER = 'KMDATA')
and CACHE_SIZE=0 and MAX_VALUE!=CACHE_SIZE
and SEQUENCE_NAME in (
'PKGSEQ_USERSTATUSCHANGE',
'SEQ_TELCOMMON',
'SEQ_TRANSID'
)
order by 1;

创建表空间：
◇： create tablespace testdataspace datafile 'D:\ORACLE\ORCL\data_1.dbf' size 2000M;
◇： create tablespace testindexspace datafile 'D:\ORACLE\ORCL\idx_1.dbf' size 2000M;
创建用户并指定表空间
◇： create user studyname identified by studypass default tablespace testdataspace;
查询用户的默认表空间

SQL> select username,default_tablespace from dba_users;
USERNAME      DEFAULT_TABLESPACE
------------- -------------------
KMZC          DATASPACE

查看表空间物理文件的名称及容量大小

SQL> col TABLESPACE_NAME for A15;    
SQL> col FILE_NAME for A50;
SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space_MB from dba_data_files order by tablespace_name;  

TABLESPACE_NAME  FILE_ID     FILE_NAME                                   TOTAL_SPACE_MB
---------------  ----------  ------------------------------------------  ---------------
DATASPACE                 8  /u01/app/oracle/data/kmzc/data_1.dbf                   2000
INDEXSPACE                9  /u01/app/oracle/data/kmzc/idx_1.dbf                    2000
KMDATA                    6  /u01/app/oracle/data/kmb2b/kmdata02.dbf               32706
KMDATA                   10  /u01/app/oracle/data/kmb2b/kmdata04.dbf               32767
KMDATA                    7  /u01/app/oracle/data/kmb2b/kmdata03.dbf               32731
KMDATA                   11  /u01/app/oracle/data/kmb2b/kmdata05.dbf               32767
KMDATA                    5  /u01/app/oracle/data/kmb2b/kmdata01.dbf               32745
SYSAUX                    2  /u01/app/oracle/data/kmb2b/sysaux01.dbf               16640
SYSTEM                    1  /u01/app/oracle/data/kmb2b/system01.dbf                2420
UNDOTBS1                  3  /u01/app/oracle/data/kmb2b/undotbs01.dbf                150
USERS                     4  /u01/app/oracle/data/kmb2b/users01.dbf                   26

查看表空间的名称及大小

SQL> select
t.tablespace_name,
round(sum(bytes/(1024*1024)),0) used_size_mb
from
dba_tablespaces t,
dba_data_files d
where
t.tablespace_name = d.tablespace_name
group by t.tablespace_name;   

TABLESPACE_NAME   USED_SIZE_MB
---------------   ------------
KMDATA                  163716
INDEXSPACE                2000
UNDOTBS1                   150
SYSAUX                   16640
DATASPACE                 2000
USERS                       26
SYSTEM                    2420

修改连接数

查看最大连接数 show parameter processes;

SQL> show parameter processes;

NAME                       TYPE      VALUE
-------------------------- --------  ---------
aq_tm_processes            integer   1
db_writer_processes        integer   2
gcs_server_processes       integer   0
global_txn_processes       integer   1
job_queue_processes        integer   1000
log_archive_max_processes  integer   4
processes                  integer   100

SQL> alter set processes = 500 scope = spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup
SQL> show parameter processes;
NAME        TYPE       VALUE
----------  ---------  ------
processes   integer    500