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_countfrom sys.obj$ a, sys.user$ b, sys.obj$ c, sys.scheduler$_job dwhere 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 setSQL>  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.AL32UTF8SQL> 

导出dmp示例：

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

[oracle@oracle ~]$ exp file = data.dmp owner=bsld_sc buffer=999999Export: Release 11.2.0.4.0 - Production on 星期五 3月 17 14:45:37 2022Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Username: / as sysdbaConnected 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 setAbout 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