Search This Blog

Total Pageviews

Friday 20 January 2012

Oracle 11g Enabling ARCHIVELOG Mode

ARCHIVELOG Mode on


 


 



SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 5
Current log sequence 7


SQL> !mkdir /u01/app/oracle/Archice/vihaan
mkdir: cannot create directory `/u01/app/oracle/Archice/vihaan': No such file or directory

SQL> !mkdir -p /u01/app/oracle/Archice/vihaan

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' ;

System altered.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/Archice/vihaan
Oldest online log sequence 5
Current log sequence 7



SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> startup mount;
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 608176900 bytes
Database Buffers 327155712 bytes
Redo Buffers 4919296 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open ;

Database altered.

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.



SQL> !ls -ltr /u01/app/oracle/Archice/vihaan
total 11284

-rw-r----- 1 oracle oinstall 1024 Jan 20 15:35 1_8_769768273.dbf
-rw-r----- 1 oracle oinstall 29083648 Jan 20 15:35 1_7_769768273.dbf

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/Archice/vihaan
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9



SQL> show parameter dest


log_archive_dest string /u01/app/oracle/Archice/vihaan

Oracle 11g REGEXP_COUNT

 


 


 




REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
^
--------------------------------------------------------------------|
-- match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters



Total how many 123 in this string '123123123123' !!!!!!!! (in total string )



SQL> SELECT REGEXP_COUNT('123123123123', '123') COUNT FROM DUAL;

COUNT
----------
4


Total how many 123 in this string '123123123123' from 4th place


|<-----------------|
SELECT REGEXP_COUNT('123123123123', '123', 4) COUNT FROM DUAL;
^
|--- this is 4th place , from this place how many 123

COUNT
----------
3


Total how many 123 in this string '123123123123' from 5th place

|<---------------|
SQL> SELECT REGEXP_COUNT('123123123123', '123',5) COUNT FROM DUAL;
^
|--- this is 5th place , from this place how many 123


COUNT
----------
2



source string at the third character for case 'i' specifies case-insensitive matching.


SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;

COUNT
----------
3




SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL; ---------'(12)3' ='123'

REGEXP_COUNT
------------
5




CREATE TABLE test (testcol VARCHAR2(50));

INSERT INTO test VALUES ('abcde');
INSERT INTO test VALUES ('12345');
INSERT INTO test VALUES ('1a4A5');
INSERT INTO test VALUES ('12a45');
INSERT INTO test VALUES ('12aBC');
INSERT INTO test VALUES ('12abc');
INSERT INTO test VALUES ('12ab5');
INSERT INTO test VALUES ('12aa5');
INSERT INTO test VALUES ('12AB5');
INSERT INTO test VALUES ('ABCDE');
INSERT INTO test VALUES ('123-5');
INSERT INTO test VALUES ('12.45');
INSERT INTO test VALUES ('1a4b5');
INSERT INTO test VALUES ('1 3 5');
INSERT INTO test VALUES ('1 45');
INSERT INTO test VALUES ('1 5');
INSERT INTO test VALUES ('a b c d');
INSERT INTO test VALUES ('a b c d e');
INSERT INTO test VALUES ('a e');
INSERT INTO test VALUES ('Steven');
INSERT INTO test VALUES ('Stephen');
INSERT INTO test VALUES ('111.222.3333');
INSERT INTO test VALUES ('222.333.4444');
INSERT INTO test VALUES ('333.444.5555');
INSERT INTO test VALUES ('abcdefabcdefabcxyz');
commit ;




set pagesize 100
select * from test ;



TESTCOL
--------------------------------------------------
abcde
12345
1a4A5
12a45
12aBC
12abc
12ab5
12aa5
12AB5
ABCDE
123-5
12.45
1a4b5
1 3 5
1 45
1 5
a b c d
a b c d e
a e
Steven
Stephen
111.222.3333
222.333.4444
333.444.5555
abcdefabcdefabcxyz

25 rows selected.



search for 2a start from 1 and i for ignore case

SELECT REGEXP_COUNT(testcol, '2a', 1, 'i') RESULT FROM test;



RESULT
----------
0
0
0
1
1
1
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

25 rows selected.



search for "e" start from 1 and i for ignore case

SQL> SELECT REGEXP_COUNT(testcol, 'e', 1, 'i') RESULT FROM test;

RESULT
----------
1
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
1
2
2
0
0
0
2

25 rows selected.


Oracle DBA

anuj blog Archive