Dev's Weblog

Reading time: 2 - 4 minutes

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files

Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space

Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2

Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth

Step : 8 Check everything goes fine.
select * from db_growth;

Check Result

Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;

Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;

Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth

Reading time: < 1 minute

By default, if you try to modify your hosts file in Vista, it will not let you save it.A It tells you that you don't have permission.A To successfully modify the hostsAfile, run notepad.exe as an administrator and open the file.

1)A Browse to Start -> All Programs -> Accessories
2)A Right click "Notepad" and select "Run as administrator"
3)A Click "Continue" on the UAC prompt
4)A Click File -> Open
5)A Browse to "C:\Windows\System32\Drivers\etc"
6)A Change the file filter drop down box from "Text Documents (*.txt)" to "All Files (*.*)"
7)A Select "hosts" and click "Open"
8)A Make the needed changes and close Notepad.A Save when prompted.

Reading time: 2 - 4 minutes

The SQL*Plus interface has several interesting 11g release innovations, including the new error logging feature and the incorporation of default SQL*Plus settings in the SQL*Plus executable itself instead of in the traditional glogin.sql file.

SQL*Plus Error Logging

When you're troubleshooting code errors, it's common to use the show errors command in SQL*Plus to identify the errors in a PL/SQL statement. Other than this, there was no way to check code errors, and the errors were not stored for later examination. In Oracle Database 11g,there's a new SQL*Plus command called set errorlogging, which stores all errors resulting from the execution of any SQL, PL/SQL, and even SQL*Plus commands in a special error logging table.

By default, the set errorlogging command causes any query errors to be written to the default table SPERRORLOG. You can specify your own table name for the error logging table, instead of using this default table name. For each error, the error logging feature logs the following bits of information:

• The username

• The time when the error occurred

• The name of the script that contains the query, if a script was used

• A user-defined identifier

• The ORA, PLS, or SP2 error message

• The query statement that caused the error

By default, error logging is turned off, as you can see from the following query:

SQL> show errorlogging
errorlogging is OFF

You can turn error logging on with the set errorlogging command, as shown here:

SQL> set errorlogging on;

If you issue the show errorlogging command again to ensure that error logging has been successfully turned on, you'll see something interesting:

SQL> connect hr/hr
SQL> show errorlogging
errorlogging is ON TABLE HR.SPERRORLOG

Not only does the database turn error logging on, but it also creates a new table called hr.sperrorlog to hold the error messages. The prefix to the error table is the same as the name of the schema owner who sets error logging on. In this case, we logged in as the user hr, so the error log is created in the hr schema.

The following example shows how to query the error logging table, sperrorlog, to retrieve the error messages and the SQL statements or PL/SQL code that generated those error messages:

SQL> select username,statement,message from sperrorlog;
USERNAME STATEMENTAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A A A A AAAAAA MESSAGE
-------- --------------------------AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA --------------------------
HRAAAAAA create table employees as select * from employees ORA-00955: name is already used by an existing object

The first message indicates that the table creation statement failed because there is already a table with an identical name. The second message shows that the error was because of the presence of an invalid column name in a select statement.

Reading time: 2 - 2 minutes

Parameters to change :

==============

DB_NAME
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
CONTROL_FILES
DB_RECOVERY_FILE_DEST
DB_BLOCK_SIZE

For example :

initdup.ora
aaaa

*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/database/dup/control01.ctl','/u01/database/dup/control02.ctl','/u01/database/dup/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dup'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=419430400
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_FILE_name_CONVERT=('/u01/database/dev/','/u01/database/dup')
LOG_FILE_NAME_CONVERT=('/u01/database/dev/','/u01/database/dup')

2. Create password file for auxillary instance.

$orapwd file=orapwdup entries=10

3. Make sure that the auxiliary instance can be connect using ORACLE NET. For this configure the tnsnames.ora accordingly. Confirm the connection to both the target and the auxiliary instance using sqlplus.

4. Start the auxiliary instance in NOMOUNT state

SQL > starup nomount pfile='$ORACLE_HOME/dbs/initdup.ora

5. Start RMAN and connect to the database.

$ rman

RMAN> TARGET sys / sys@<targets database>;

RMAN> connect AUXILIARY sys/sys@<Auxiliary db>;

RMAN> connect CATALOG rman/rman@<Catalog db>;

6. Run the DUPLICATE command.

RMAN>DUPLICATE TARGET DATABASE TO 'DUP' FROM ACTIVE DATABASE DB_FILE_name_CONVERT=('/u01/database/dev/','/u01/database/dup');

Reading time: < 1 minute

In 11G , you can create pfile or spfile from memory of running instance.

SQL> create pfile from memory;
File created.

You can also use the memory clause to create an spfile, as shown here:

SQL> create spfile from memory;
File created.

You need to have instance up and running to be able to create pfile or spfile from memory.