| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
If it is not specified, the commands must be executed connected as SYS. About the solutions: - could be more than one. Here is one solution that often takes into account of the performance tuning - they could change because of the Oracle version (eg. Oracle 8i is quite different than Oracle 12c!) - it is not warranted to be error-free in all the Oracle environments December - db parameters
Level (*) : 3 Topic (*) : DBA In this moment in your db a session with SID = 1976 is running. You think that this session is running with some Oracle initialization parameters value different than the default value. How can you find out if this is true? And, in that session, which are the Oracle parameters with value different than the default values?
November - Info Connection
Level (*) : 2 Topic (*) : Prog/DBA Thru a simple query on DUAL, you have to find the name of the instance and if you are connected thru a user with DBA privileges. Find out this query. Solution
You could use the function SYS_CONTEXT :
SELECT
SYS_CONTEXT('USERENV', 'INSTANCE_NAME') instance,
SYS_CONTEXT('USERENV', 'ISDBA') amidba
FROM dual;
October - Clean up a little part of the library cache
Level (*) : 3 Topic (*) : DBA In a RAC, a query Q1 with sql_id=25ucwzczhs4la get always the same bad plan hash value. You do some modification on the db and you want to be sure that the Oracle optimizer will do again the HARD PARSE of Q1. Of course, you don't need to clean up (flush) the entire shared pool but you only want to purge the library cache part regarding that query. How can you do it? If the query has been launched only on the instance 1, can you do that purge being connected to the db thru the instance 2?
September - Execution Plan
Level (*) : 3 Topic (*) : Prog/DBA A query Q1 has sql_id 0bffz5s6gflor and plan hash value 635035975. This Q1 query is running in an Oracle RAC in this moment and you can't analyze it thru AWR/ADDM, you should need to do an AWR snapshot. Instead of using the AWR snapshot, you have to find a query in order to keep the execution plan of this Q1. E.g. the result of your query should be something like this: ID OPERATION OBJECT CARDINALITY KB TEMP COST TIME PStart PStop QBLOCK --- -------------------------- ------ ----------- ---- ---- ----- ---- ------ ----- ------ 0 SELECT - Plan 635035975 5668 1 PX COORDINATOR SEL$1 2 PX SEND QC (RANDOM) :TQ107 20063 467 5668 3 3 HASH GROUP BY 20063 467 76 5668 3 4 PX RECEIVE 20063 467 5667 3 5 PX SEND HASH :TQ106 20063 467 5667 3 6 BUFFER SORT 20063 467 5668 3 7 NESTED LOOPS 20063 467 5667 3 8 HASH JOIN 20063 369 5667 3 etc... 15 PARTITION LIST SINGLE 2857 12 1 3 3 16 INDEX RANGE SCAN FACT1 2857 12 1 KEY KEY SEL$1
August - Init parameters on RAC
Level (*) : 2 Topic (*) : DBA You are working on a RAC db composed by 2 instances. You want to check which Oracle initialization parameters are different between these instances. So you must write a query in order to get this information. The query result must be something like this: NAME VALUE_I1 VALUE_I2 --------------------- -------------------------------- -------------------------------- background_dump_dest /opt/ora/diag/rdbms/db/db1/trace /opt/ora/diag/rdbms/db/db2/trace cluster_interconnects 192.168.10.1:192.168.10.2 192.168.10.3:192.168.10.4 core_dump_dest /opt/ora/diag/rdbms/db/db1/cdump /opt/ora/diag/rdbms/db/db2/cdump instance_name db1 db2 instance_number 1 2 Solution
Reading the GV$SYSTEM_PARAMETER views, you could use a query like this:
select i1.name,
i1.value value_i1,
i2.value value_i2
from GV$SYSTEM_PARAMETER i1,
GV$SYSTEM_PARAMETER i2
where i1.inst_id = (select min(inst_id) from GV$SYSTEM_PARAMETER) and
i2.inst_id = (select max(inst_id) from GV$SYSTEM_PARAMETER) and
i1.num = i2.num and
i1.value != i2.value
order by 1;
Usually in a RAC, two instance have at least 8-10 parameters with different values.
July - Tablespace size History
Level (*) : 2 Topic (*) : DBA You are in Oracle 11.2 and you want to know some stats about the size of tablespace TB1. You want these stats referred to the last 2 weeks and you want to see them aggregated by day. For example you want a result like this: DAY TSNAME SIZE_GB MAXSIZE_GB USEDSIZE_GB --------- ------- ---------- ------------- ----------- 09-JUL-15 TB1 3543 3700 3438 10-JUL-15 TB1 3543 3700 3438 11-JUL-15 TB1 3543 3700 3431 12-JUL-15 TB1 3543 3700 3453 13-JUL-15 TB1 3548 3860 3458 14-JUL-15 TB1 3548 3860 3476 15-JUL-15 TB1 3548 3860 3480 16-JUL-15 TB1 3548 3860 3417 17-JUL-15 TB1 3548 3860 3410 18-JUL-15 TB1 3548 3860 3410 19-JUL-15 TB1 3548 3860 3410 20-JUL-15 TB1 3548 3860 3439 21-JUL-15 TB1 3726 3860 3584 22-JUL-15 TB1 3726 3860 3589 23-JUL-15 TB1 3726 3860 3554Find out the query in order to get these information. Solution
You could use a query like this (in bold font what you should change according to your
tablespace and period to analyze) :
select trunc(begin_interval_time) day,tsname,
round(max(tablespace_size*8192)/1024/1024/1024) size_gb,
round(max(tablespace_maxsize*8192)/1024/1024/1024) maxsize_gb,
round(max(tablespace_usedsize*8192)/1024/1024/1024) usedsize_gb
from (
select
s.snap_id, s.dbid, s.instance_number, s.startup_time, s.begin_interval_time,
s.end_interval_time, tablespace_size, tablespace_maxsize, tablespace_usedsize,
tsname, contents, status, segment_space_management, extent_management, is_backup
from dba_hist_tbspc_space_usage u,
dba_hist_snapshot s ,
dba_hist_tablespace_stat t
where u.snap_id=s.snap_id and u.dbid=s.dbid and u.snap_id=t.snap_id and t.dbid=s.dbid
and u.tablespace_id=t.ts# and s.instance_number=t.instance_number
and tsname = 'TB1'
and begin_interval_time > sysdate-14
order by s.snap_id desc
)
group by trunc(begin_interval_time), tsname
order by trunc(begin_interval_time), tsname desc;
June - SQL*Plus Settings
Level (*) : 3 Topic (*) : DBA In your pc, you have an Oracle client installed and in your desktop you create 2 files called dblor.cmd and dblassi.cmd . These files are useful to launch the SQL*Plus connections to the DBs called DBLOR and DBLASSI. When you launch these files, in SQL*Plus you don't want a prompt like this: SQL >But you want always prompted the time and the db where you are connected. Example: 18:03:23 dblor >And you want other settings like: set lin 180 pages 120 alter session set nls_language='AMERICAN';In order to do automatically these settings, you have to create or modify another file. Explain these solutions: - in the case that you are "well granted" on the PC - in the case where you don't have good privileges on the PC
May - Dates in SQL*Plus
Level (*) : 1 Topic (*) : Prog/DBA You are connected on the db thru SQL*Plus, you want to know what's the time (and the date), so you launch : SQL> select sysdate from dual; SYSDATE --------- 02-GIU-15But you see only the date and not the time. To solve easily this issue, you have 2 solutions: 1. modification of the query 2. modification of the session So, you please describe better both solutions. Solution
1. You could modify the query using the function TO_CHAR
SQL> SELECT to_char(sysdate,'yyyymmdd hh24.mi') date_time
FROM dual;
DATE_TIME
--------------
20150602 21.37
2. You could modify the NLS_DATE_FORMAT on the session where you are connected using the
ALTER SESSION command:
SQL> alter session set nls_date_format = 'yyyymmdd hh24.mi';
Session modified.
SQL> SELECT sysdate
FROM dual;
SYSDATE
--------------
20150602 21.37
April - Bind variables
Level (*) : 3 Topic (*) : Prog/DBA You are in an Oracle 11g RAC and you have a query with bind variables running with SQL_ID 0kkhhb2w93cx1. You want to know some information about its session and the values of its bind variables. For example, you want to know something like :
INST_ID SID Plan_Hash_V SQL_ID CHILD_ADDRESS NAME LAST_CAPTURED Value
------- ---- ----------- ------------- ---------------- ----- -------------- -----
2 285 2170058777 0kkhhb2w93cx1 0000000692F75980 :1 23/04 14.00 1
2 285 2170058777 0kkhhb2w93cx1 0000000692F75980 :2 23/04 14.00 2
2 285 2170058777 0kkhhb2w93cx1 0000000692F75980 :3 23/04 14.00 44506
Find the query in order to get these info.
March - Subquery Behaviour
Level (*) : 3 Topic (*) : Prog You create a simple table called A:
create table a (aa char);
insert into a values ('Z');
commit;
You try this query:
SQL > select aa from dual;
select aa from dual
*
ERROR at line 1:
ORA-00904: "AA": invalid identifier
Of course, you receive an error because the column AA is not in the table DUAL.But if you put the above query in a subquery, you don't receive any error : SQL > select * from a where aa in (select aa from dual); A - ZHow is it possible? Why Oracle doesn't give you an error? How you should write the query in order to receive an error (during the SQL parsing) when a column in the subquery doesn't exists?
February - Your environment
Level (*) : 2 Topic (*) : DBA/Progr You are connected on the db through a not "DBA" user (SYS, SYSTEM or someone else "strong"). You have to find 2 queries to know: - the instance_name - the db global name Solution
Instance_name : you could use the sys_context function :
select sys_context ('USERENV', 'INSTANCE_NAME')
from dual;
Database Global name : you could use the view global_name :
select *
from global_name;
January - EXCHANGE error
Level (*) : 3 Topic (*) : DBA/Progr You want to exchange the table T2 with the subpartition SP_20141218_MI (inside the table T1) but you get an error:
ALTER TABLE t1
EXCHANGE SUBPARTITION sp_20141218_mi
WITH TABLE t2
INCLUDING INDEXES WITHOUT VALIDATION;
Report errors:
Error SQL: ORA-14278: column type or size mismatch in EXCHANGE SUBPARTITION
14278. 00000 - "column type or size mismatch in EXCHANGE SUBPARTITION"
*Cause: The corresponding columns in the tables specified in the
ALTER TABLE EXCHANGE SUBPARTITION are of different type or size
*Action: Ensure that the two tables have the same number of columns
with the same type and size.
So you check all the columns in both tables but you don't find any mismatch.There is no difference also between their indexes. Then what could be the reason? And what you can do to avoid this error?
(*) Level: 1: easy solution 2: medium solution 3: hard (or long) solution Topic: DBA: quiz usually suitable for DBAs Progr: quiz usually suitable for PL/SQL or SQL Programmers DBA/Progr: quiz suitable for DBAs and for Programmers
| |||||||||||||||||||||||||||||||||||||||||||||
