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备份基础操作

exp file = data.dmp owner=plsuser buffer=999999

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