Recovery: clear log file - not existing

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:346817516082

08:00:42 38 4294967295 SYS@orcl> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) ═х єфрхЄё эрщЄш єърчрээ√щ Їрщы.


Elapsed: 00:00:00.10
08:00:50 38 4294967295 SYS@orcl> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


Elapsed: 00:00:00.37
08:12:44 38 4294967295 SYS@orcl> recover database until cancel;
Media recovery complete.
08:13:01 38 4294967295 SYS@orcl> alter database open resetlogs;

Database altered.

Performance: Block contention

select class, count, time/100 s from v$waitstat order by s desc;

General: Estimate index size

variable a number
variable u number
exec dbms_space.create_index_cost('create index t1_idx on t_1(object_name, object_type, owner)', :u, :a);

PL/SQL procedure successfully completed.

A
------------------------------------
7,340,032.0

U
------------------------------------
5,282,424.0

General: Resource manager simple plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'simple_plan1',
CONSUMER_GROUP1 => 'mygroup1', GROUP1_CPU => 3,
CONSUMER_GROUP2 => 'mygroup2', GROUP2_CPU => 1);
END;
/

exec dbms_resource_manager.create_pending_area;

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'test', 'mygroup2');
END;
/

exec dbms_resource_manager.submit_pending_area;

18:27:16 38 447 TEST@orcl> select initial_rsrc_consumer_group from dba_users where username = 'TEST';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
MYGROUP2

alter system set resouce_manager_plan=simple_plan1;

Performance: Statspack CPU time

http://www.jlcomp.demon.co.uk/statspack_01.html

Dev: nls_sort: possible values and why index not used for order by

select * from v$nls_valid_values where parameter='SORT';
--
-- Territory
--
select to_char(sysdate, 'd') from dual;

T
-
3

alter session set nls_territory=RUSSIA;
Session altered.
select to_char(sysdate, 'd') from dual;

T
-
2
--
-- Be careful, for varchar columns optimizer will not use index for sorting
--
13:45:19 SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------------------------------------
RUSSIAN
13:46:54 SQL> select * from id order by id;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1459189690

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| | 3196 (1)| 00:00:39 |
| 1 | SORT ORDER BY | | 10145 | 14M| 31M| 3196 (1)| 00:00:39 |
| 2 | TABLE ACCESS FULL| ID | 10145 | 14M| | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
13:47:24 SQL> alter session set nls_sort=binary;

Session altered.

Elapsed: 00:00:00.01
13:47:31 SQL> select * from id order by id;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4052251165

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| 19 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | SYS_C006460 | 10145 | 14M| 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--
-- You can create linguistic index but in this case table will be accessed too
--
14:49:47 SQL> alter session set nls_sort=russian;

Session altered.

Elapsed: 00:00:00.01
14:50:49 SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------------------------------------
RUSSIAN

1 row selected.

Elapsed: 00:00:00.01
14:51:16 SQL> set autotrace traceonly exp
14:51:31 SQL> select * from id order by id;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3088927961

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10145 | 14M| 954 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 10145 | 14M| 954 (0)| 00:00:12 |
| 2 | INDEX FULL SCAN | ID_IDX | 10145 | | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Dev: Find string in table

--
-- Sometimes it is needed to find column/rowid in table with value from screen of web --application etc. That procedure helps to do that, example:
--09:09:43 SQL> create table test as select * from dba_objects;
--...
--09:10:53 SQL> /
--Enter value for table_name: test
--Enter value for string: DBA_OBJECTS
--GENERATED
--OBJECT_NAME
--rowid: AAAMvZAABAAAPk+AAj in SYS.TEST <<-- finding only first occurence
-- Should not be used as is for partitioned tables
--
set serveroutput on
declare
procedure find_string_in_table(val varchar2)
is
v_where Varchar2(32767);
type rc is ref cursor;
c rc;
v_rowid rowid;
begin
for r in (
select
t.owner||'.'||t.table_name table_name, t.column_name
from
dba_tab_cols t
where t.table_name = upper(trim('&table_name'))
and t.data_type like '%CHAR%'
order by t.column_name) loop
dbms_output.put_line(r.column_name);
v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';

open c for 'select rowid from ' || r.table_name || ' ' || v_where;

fetch c into v_rowid;
loop
fetch c into v_rowid;
exit when c%notfound;
dbms_output.put_line(' rowid: ' || v_rowid || ' in ' || r.table_name);
return;
end loop;
end loop;
end;
begin
find_string_in_table('&string');
end;
/