oracle基础操作

oracle基础操作语法：

1、查询会话

SQL>  select count(*) from v$session;

2、增大连接数

SQL>  alter system set processes=5000 scope =spfile;

3、增大会话数

SQL>  alter system set sessions=7552 scope=spfile;

4、查询 参数：

SQL>  show parameter processes;

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

修改job参数：

SQL>  alter system set job_queue_processes=1000;

5、查询用户序列

 SQL>  select * from user_sequences；

6、查询JOB失败次数

SQL>  select job,what,failures,broken from user_jobs;

7、查询所有的job：（包括系统和用户的JOB）

SQL>  select b.name username, d.obj#, a.name job_name, d.failure_count
  from sys.obj$ a, sys.user$ b, sys.obj$ c, sys.scheduler$_job d
 where d.obj# = a.obj#
   AND a.owner# = b.user#
   AND d.class_oid = c.obj#(+);

8、查询正在运行的job：

SQL>  SELECT SID,JOB FROM DBA_JOBS_RUNNING; 

9、查询 undo 与system 的 dbf文件

SQL>    select file_id,file_name from dba_data_files where tablespace_name in ('SYSTEM',(select value from v$parameter where name='undo_tablespace'));

10、查询系统，用户的表空间位置

 SQL>  select t1.name,t2.name   from v$tablespace t1,v$datafile t2  where t1.ts# = t2.ts#;

11、oracle 创建 分区

SQL>  alter table a2_cdrindex_info_f partition P_2023030716 values less than (TO_DATE(' 2023-03-07 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace PLSSPACE_DEFAULT;
  

12、 删除分区

SQL>  alter table A2_CDRINDEX_INFO_F drop partition P_202302823 update  global  INDEXES;

13、查看所有分区

SQL>  select * from user_part_tables ;

14、查看对应表—所有分区

SQL>  SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'A2_CDRINDEX_INFO_F';

15、查看表分区的 分区键查询（根据哪个字段分区）

SQL>  SELECT * FROM all_PART_KEY_COLUMNS where name='A2_CDRINDEX_INFO_F';

根据分区查询

SQL>  select count(1) FROM A2_CDRINDEX_INFO_F partition(P_2023030717);

查询表对应用户

SQL>  select owner from dba_tables where table_name='OFFICEALL_STATISTIC';

oracle备份基础操作

查看数据库的字符编码：

方式一
SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
SQL>

方式二：
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

修改oracle数据库的字符编码：

SQL> shutdown abort 
ORACLE instance shut down.
 
SQL> startup mount;
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             503320360 bytes
Database Buffers          247463936 bytes
Redo Buffers                6901760 bytes
Database mounted.
 
SQL> alter system enable restricted session; 
System altered.

SQL> alter system set job_queue_processes=0; 
System altered.

SQL> alter system set aq_tm_processes=0;
System altered.

SQL> alter database open;
Database altered.

SQL>  ALTER DATABASE CHARACTER SET AL32UTF8;
 ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL>  ALTER DATABASE character set INTERNAL_USE AL32UTF8;
Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  759943168 bytes
Fixed Size                  2257112 bytes
Variable Size             503320360 bytes
Database Buffers          247463936 bytes
Redo Buffers                6901760 bytes
Database mounted.
Database opened.

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> 

 
# 关闭设置的打开

SQL>  alter system set job_queue_processes=1000;

System altered.

SQL> alter system set aq_tm_processes=1;  

System altered.

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> 

导出dmp示例：

[oracle@oracle ~]$  exp file = data.dmp owner=plsuser buffer=999999

[oracle@oracle ~]$ exp file = data.dmp owner=bsld_sc buffer=999999

Export: Release 11.2.0.4.0 - Production on 星期五 3月 17 14:45:37 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BSLD_SC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BSLD_SC 
About to export BSLD_SC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BSLD_SC's tables via Conventional Path ...
. . exporting table       A2_CDRINDEX_INFO_XF_WARN          0 rows exported
. . exporting table            QRTZ_FIRED_TRIGGERS          0 rows exported
. . exporting table               QRTZ_JOB_DETAILS          3 rows exported
. . exporting table        TASK_WORKORDER_SZMHCZSB       1454 rows exported
. . exporting table      TASK_WORKORDER_SZMHTLYJFK          2 rows exported
. . exporting table      TASK_WORKORDER_SZMHTLYJSB          7 rows exported
. . exporting table              TASK_WORKORDER_XF          5 rows exported
. . exporting table        TASK_WORKORDER_YJCXJGFK          4 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

imp plsuser/pls333333 file = data.dmp log=data.log fromuser=plsuser  touser=plsuser buffer=999999

导入错误的话，重新删除用户，再重新创建用户，移步此链接：

SQL> drop user bsld_sc cascade;

User dropped.