Oracle: How to Find the Table Size

There are different ways to find table size in oracle, views associated to check size of tables are:

• DBA_EXTENTS
• USER_EXTENTS
• DBA_SEGMENTS

Method 1:

To find size of tables using view DBA_SEGMENTS:

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 MB, TABLESPACE_NAME

  2  FROM DBA_SEGMENTS

  3  WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='CUSTOMERS';

SEGMENT_NAME    SEGMENT_TY         MB TABLESPACE_NAME

--------------- ---------- ---------- ------------------------------

CUSTOMERS       TABLE           .0625 SYSTEM

Method 2:

To find size of tables using view DBA_EXTENTS:

SQL>  SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 MB, TABLESPACE_NAME

  2   FROM DBA_EXTENTS

  3   WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='CUSTOMERS';

SEGMENT_NAME    SEGMENT_TY         MB TABLESPACE_NAME

--------------- ---------- ---------- -------------------

CUSTOMERS       TABLE           .0625 SYSTEM

As a user you can also use user_extents.

READ MORE ...

Oracle: Find Total Size of The Database

The size of the oracle database is the total size of the data files, redo log files and temp files that make up the tablespaces of the database. These details are found in the dba_extents view. Type the following lines at the SQL prompt:

Method 1: To find used space within the database.

SQL>select sum(bytes/1024/1024/1024) as SIZE from   dba_extents;

Method 2: To find overall database size:

The biggest portion of a database's size comes under the datafiles.
To get size of all datafiles in MB:

SQL>select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;


Combining all together we will get overall size: 

SQL> select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Check Oracle database faq:
http://www.orafaq.com/ 

READ MORE ...

Tere Hoke Rahenge Lyrics - Raja Natwarlal | Arijit Singh

Tere Hoke Rahenge Lyrics from movie Raja Natwarlal: The song Tere Hoke Rehengay is sung by Arijit Singh and music by uvan Shankar Raja.

Song: Tere Hoke Rehengay
Movie: Raja Natwarlal (2014)
Singer: Arijit Singh
Music: Yuvan Shankar Raja
Lyrics: Irshad Kamil
Star Cast: Emraan Hashmi, Humaima Malick
Music Label: Junglee Music

Tere Hoke Rahenge Lyrics

Kal the miley, phir kyun lage aise
Tumse miley... arsaa hua jaise
Ab tu bataa.. jo ho pataa..
Tere bina lamha lamha jiyenge kaise?

Teri baahon ka ghera
Badaa mehfooz lage hai
Badi bekhauf jagah hai ye.. oo ooo..
Inme hi rehna chaahein, teri panaahein

Jab tak hai jeena chahenge, oo ooo..
Tere hoke rahenge, oo ooo..
Dil zid pe adaa hai, oo ooo..
Tere hoke rahenge, oo ooo..
Tera shauq chadha hai, oo.. oo ooo..

Aankhon mein sapno ko rakh le mere
Inko naa jag tod de
Phir meri kismat ko jaise ho dil
Waisa hi tu mod de
Tu hi toh hai hausla
Chaahat ka tu hai sila
Jeete jee na jee sakein
Kahin ab jo tu naa mila..

Oo ooo.. Tere hoke rehengay
Oo ooo.. Dil zidd pe adaa hai
Oo ooo.. Tere ho ke rahenge..
Oo ooo.. Tera shauq chadha hai
Oo oo oo ooo..

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

 कल थे मिले
फिर क्यों लगे ऐसे
तुमसे मिले अरसा हुआ जैसे
अब तू बता जो हो पता
तेरे बिना लम्हा लम्हा जिएंगे कैसे

तेरी बाहों का घेरा
बड़ा मेहफ़ूज़ लगे है
बड़ी बेख़ौफ़ जगह है ये… ओ…
इनमे ही रहना चाहें
तेरी पनाहें
जब तक है जीना चाहेंगे
ओ… ओ…
तेरे होके रहेंगे ओ… ओ…
दिल ज़िद पे अड़ा है ओ… ओ…
तेरे होके रहेंगे ओ… ओ… 
तेरा शौक़ चढ़ा
ओ… ओ…

आँखों में सपनों को रख ले मेरे
इनको ना जग तोड़ दे
फिर मेरी क़िस्मत को जैसे हो दिल
वैसा ही तू मोड़ दे
तू ही तो है हौसला
चाहत का तू है सिला
जीते जी ना जी सकें
कहीं अब जो तू ना मिला

ओ… ओ… तेरे होके रहेंगे
ओ… ओ… दिल ज़िद पे अड़ा है
ओ… ओ… तेरे होके रहेंगे
ओ… ओ… तेरा शौक़ चढ़ा
ओ… ओ…

READ MORE ...

How to Make all Windows Version OS genuine using CMD

It is good prescription that you should always try to avoid pirated version of windows OS like WIN 7, 8, 8.1 etc., you must buy and use genuine operating system. Yes, there is no doubt that it cost much but after and all, it's all features and support will be accessible to you.

Few days back one of my friend was using downloaded windows 8 OS and found that some of it's features was not working well and a message was there on his laptop screen that make it genuine. So if you are not able to buy original product, here is a best method to make all version of windows operating system like original.

 Steps to make Windows OS (any version- 7/8/8.1) Genuine

Step1: Open Command Prompt (CMD) in Administrative mode:

• Go to  start menu
• Type "CMD"
• Right click on "CMD" and choose "Run as administrator.

Step 2: On "CMD" type SLMGR -REARM as shown below, wait for few seconds until it process the request.

Step3: After few seconds a message window will popup having message "Restart your computer" press OK.

Step4: After reboot you will see that there is no message regarding "make windows genuine error"or "Fix WGA" error.

NOTE: This activation is only for a months. So after one months you will have to do this again.

If you run the command SLMGR -REARM for more than 3 time you will get an error.
Error: 0xC004D307 The maximum allowed number of re-arms has been exceeded. You must re-install the OS before trying to re-arm again.

Simple steps to Reset Windows 7 Rearm:

• Just open the "command prompt" or run.
• Type "Regedit" on Command prompt and press enter.
• Windows Registry Window would open .
• Now search for Registry Key

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Windows NT/CurrentVersion/SoftwareProtectionPlatform
–>“SkipRearm”=dword:00000000

Just change 00000000 to 00000001.

After 3 attempts windows OS Rearm key would change to 00000000, So you need to change the value to enable Rearm once again. Using this method you can make windows legal for lifetime.

READ MORE ...

How to Enable/Disable Archive Log Mode in 10g/11g

By default when you enter CREATE DATABASE statement, it is in noarchive log mode, to change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause using administrator privileges (AS SYSDBA).

By default, Oracle database store archive logs data into the flash recovery area. If you want to set  new location for archive logs, you can set using the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2782M
SQL>




SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl01/arch01' scope = both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28
SQL> 

How to Enable Archive log Mode

Shut down the database instance and startup in mount stage:

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.


SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

How to Disable Archive log Mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

Shut down the oracle database instance and start in mount stage.

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28

You can also check official oracle docs for enabling/disabling archivelog mode here:

Controlling Archive

READ MORE ...

Oracle: How to Find the Database ID (DBID)

Every Oracle database has it's unique identifier called DBID which is necessary in most critical situations like recovery from loss of all control file using rman autobackup.

How to find Oracle DBID ?

Method 1:

You can easily find using V$database view:

SQL> select dbid from v$database;

      DBID

----------

3561501577

Method 2:

Find using RMAN:

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 18:06:14 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

READ MORE ...

How to Restore/Recover Lost of Control File Using Rman

Oracle database maintain three copies of Controlfile at three different locations. There are some situations arises when you loses all your controlfile. So in this case how to restore and recover controlfile using RMAN autobackup option.

One day when I tried to mount the Database I encountered with an error code:
ORA-00205: error in identifying control file, check alert log for more info

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
ORA-00205: error in identifying control file, check alert log for more info
=============================================================
How to restore control file using RMAN

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 15:45:17 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:46:27
RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN> restore controlfile;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:47:03
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

STEP 1: You need to set DBID before recovery

 SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 17:26:13 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

SQL> select dbid from v$database;

      DBID
----------

3561501508

But if your database is not mounted, you cannot find DBID. So set ID to any number you want.
ex: 

RMAN> set DBID=3561501508


executing command: SET DBID

STEP2: Restore control file from autobackup:

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140801
channel ORA_DISK_1: AUTOBACKUP found: c-3561501508-20140801-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-3561501508-20140801-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\CONTROL01.CTL
Finished restore at 01-AUG-14

STEP3: 

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

STEP4
RMAN> recover database;

Starting recover at 01-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG
archived log file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-AUG-14

RMAN> alter database open resetlogs;

database opened

READ MORE ...