query to find database size completely

The below query will give the size of the Oracle Database completely.

select round((a.data_size+b.temp_size+c.redo_size)/1024/1024/1024,2) "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

script for tablespace useage

Below is the script to get data about all the tablespaces in a database.

SELECT Total.name "Tablespace Name",
total_space "Total Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
nvl(Free_space, 0) "Free Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

oracle kernel paramters for solaris10

steps for oracle kernel paramters setup for solaris 10.

1. Login as root

2. To add the project run the following command
# projadd user.oracle

3. To view the current values of the resource control, enter the following commands

. # id -p // to verify the project id
uid=0(root) gid=0(root) projid=1 (user.root)

4. # prctl -n project.max-shm-memory -i project user.root

5. # prctl -n project.max-sem-ids -i project user.root


If you must change any of the current values, then:
a. To modify the value of max-shm-memory to 6 GB:

# prctl -n project.max-shm-memory -v 6gb -r -i project user.root


d. To modify the value of max-sem-ids to 256:

# prctl -n project.max-sem-ids -v 256 -r -i project user.root


For changing the values for user.oracle run the following commands
#projmod -a -K "project.max-shm-memory=(priv,6,deny)" user.oracle
#projmod -a -K "project.max-sem=(priv,256,deny)" user.oracle

-------------------------------------------------------------------------------------------------
The Solaris 10 Operating System introduces the following new resource controls to replace the old /etc/system tunables:Code: Old Old New New
Resource control tunable default Max value default
---------------------- ------------- ------- --------- ----------
process.max-msg-qbytes msginfo_msgmnb 4096 ULONG_MAX 65536
process.max-msg-messages msginfo_msgtql 40 UINT_MAX 8192
process.max-sem-ops seminfo_semopm 10 INT_MAX 512
process.max-sem-nsems seminfo_semmsl 25 SHRT_MAX 512
project.max-shm-memory shminfo_shmmax 0x800000 UINT64_MAX 1/4 physmem
project.max-shm-ids shminfo_shmmni 100 2**24 128
project.max-msg-ids msginfo_msgmni 50 2**24 128
project.max-sem-ids seminfo 10 2**24 128

Data Pump.....New Feature in Oracle 10g

Data Pump:
Data Pump is a server side application therefore the dump files will be generated on the server.It is about 5-6 times faster than export/import.

Steps for configuration of Data Pump:

1.Login as sysdba

2.Create directory at OS and database level with the following commands
mkdir d:\datapump
SQL>CREATE DIRECTORY test_dir AS 'd:\datapump';

3.Grant read and write access to the user.
SQL>GRANT READ, WRITE ON DIRECTORY dmpdir TO ;

4.Ensure that the user have the EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges. To see a list of all roles assigned to you within your security domain, issue the following command
Connect to
SQL> SELECT * FROM SESSION_ROLES;

Now the required setting for Data Pump has been done.

The following are some of the different options that we can use in Data Pump.

Table Exports/Imports:

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports:

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports:

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Suspending and Resuming Export Jobs (Attaching and Re-Attaching to the Jobs):

You can suspend running export jobs and later on resume these jobs or kill these jobs using Data Pump Export. You can start a job in one client machine and then, if because of some work, you can suspend it. Afterwards when your work has been finished you can continue the job from the same client, where you stopped the job, or you can restart the job from another client machine.
For Example, suppose a DBA starts a full database export by typing the following command at one client machine CLNT1 by typing the following command

$expdp scott/tiger@mydb FULL=y DIRECTORY=data_pump_dir DUMPFILE=full.dmp LOGFILE=myfullexp.log JOB_NAME=myfullJob

After some time, the DBA wants to stop this job temporarily. Then he presses CTRL+C to enter into interactive mode. Then he will get the Export> prompt where he can type interactive commands
Now he wants to stop this export job so he will type the following command
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([y]/n): y
The job is placed in a stopped state and exits the client.

After finishing his other work, the DBA wants to resume the export job and the client machine from where he actually started the job is locked because, the user has locked his/her cabin. So now the DBA will go to another client machine and he reattach to the job by typing the following command
$expdp hr/hr@mydb ATTACH=myfulljob

After the job status is displayed, he can issue the CONTINUE_CLIENT command to resume logging mode and restart the myfulljob job.
Export> CONTINUE_CLIENT
A message is displayed that the job has been reopened, and processing status is output to the client.

Note: After reattaching to the Job a DBA can also kill the job by typing KILL_JOB, if he doesn’t want to continue with the export job.

There is one impdp parameter "SQLFILE", if you use this in impdp command it will not import the data into the database instead of that it will create one SQL file for the data that has to be imported.

copy command in oracle!!!

Suppose you have to make exact replica of a table from one database to another.....

How will you do it?
The solution that comes into mind is through exp-imp or data pump (Oracle 10g feature), this can difficult sometimes as you have to move the .dmp file from one database to another or do some other settings like tns entries, instead of this there is one better solution for the same "copy command"

Lets understand it through an example
Suppose you want to make exact replica of "emp" table from database A to database B.

Steps:
1. Login on database B.

2. Create structure of emp table in database B same as in database A.

3. Now execute the below copy command
copy from username/password@A insert emp using select * from emp;

Now you have exact replica of table emp in database B also.

Please let me know if you have any queries.

How to change datatype of a column if the data in that column exists?

The above question many of you might have faced that how to change the datatype of the column if the data in the column already exists.

Here is the solution.
1. Suppose for example the below is the table
desc emp
id number
name varchar2(50)

Now you want to change the datatype of id to varchar2(20)

2. Add a dump column say xyz to the table emp
alter table emp
add xyz varchar2(20)

3. Copy the data from the id column to the xyz column
update emp
set xyz=id;
Crosscheck the data and commit the transaction.
commit;

4. Update all the rows of the id solution with NULL Values.
update emp
set id=NULL;
Crosscheck and commit the transaction.
commit;

5. Now the id column is having no data so change its datatype.
alter table emp
modify id varchar2(20);

6. Now copy the data back from xyz column to id column.
update emp
set id=xyz;
Crosscheck the data and commit the transaction.
commit;

7. Now drop the column xyz as its purpose is over.
alter table emp
drop column xyz;

Hope this helps.....

100 keyboard shortcuts!!!

Keyboard shorcuts are of great help.....Below is the list of some of the shortcuts....

CTRL+C (Copy)
CTRL+X (Cut)
CTRL+V (Paste)
CTRL+Z (Undo)
DELETE (Delete)
SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
CTRL while dragging an item (Copy the selected item)
CTRL+SHIFT while dragging an item (Create a shortcut to the selected item) F2 key (Rename the selected item)
CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
CTRL+A (Select all)
F3 key (Search for a file or a folder)
ALT+ENTER (View the properties for the selected item)
ALT+F4 (Close the active item, or quit the active program)
ALT+ENTER (Display the properties of the selected object)
ALT+SPACEBAR (Open the shortcut menu for the active window)
CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
ALT+TAB (Switch between the open items)
ALT+ESC (Cycle through items in the order that they had been opened)
F6 key (Cycle through the screen elements in a window or on the desktop)
F4 key (Display the Address bar list in My Computer or Windows Explorer)
SHIFT+F10 (Display the shortcut menu for the selected item)
ALT+SPACEBAR (Display the System menu for the active window)
CTRL+ESC (Display the Start menu)
ALT+Underlined letter in a menu name (Display the corresponding menu)
Underlined letter in a command name on an open menu (Perform the corresponding command) F10 key (Activate the menu bar in the active program)
RIGHT ARROW (Open the next menu to the right, or open a submenu)
LEFT ARROW (Open the next menu to the left, or close a submenu)
F5 key (Update the active window)
BACKSPACE (View the folder one level up in My Computer or Windows Explorer)
ESC (Cancel the current task)
SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing) Dialog Box Keyboard Shortcuts
CTRL+TAB (Move forward through the tabs)
CTRL+SHIFT+TAB (Move backward through the tabs)
TAB (Move forward through the options)
SHIFT+TAB (Move backward through the options)
ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
ENTER (Perform the command for the active option or button)
SPACEBAR (Select or clear the check box if the active option is a check box)
Arrow keys (Select a button if the active option is a group of option buttons)
F1 key (Display Help)
F4 key (Display the items in the active list)
BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box) m*cro$oft Natural Keyboard Shortcuts Windows Logo (Display or hide the Start menu) Windows Logo+BREAK (Display the System Properties dialog box)
Windows Logo+D (Display the desktop)
Windows Logo+M (Minimize all of the windows)
Windows Logo+SHIFT+M (Restore the minimized windows)
Windows Logo+E (Open My Computer)
Windows Logo+F (Search for a file or a folder)
CTRL+Windows Logo+F (Search for computers)
Windows Logo+F1 (Display Windows Help)
Windows Logo+ L (Lock the keyboard)
Windows Logo+R (Open the Run dialog box)
Windows Logo+U (Open Utility Manager)
Accessibility Keyboard Shortcuts
Right SHIFT for eight seconds (Switch FilterKeys either on or off)
Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
SHIFT five times (Switch the StickyKeys either on or off)
NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
Windows Logo +U (Open Utility Manager)
Windows Explorer Keyboard Shortcuts END (Display the bottom of the active window)
HOME (Display the top of the active window)
NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder) NUM LOCK+Plus sign (+) (Display the contents of the selected folder)
NUM LOCK+Minus sign (-) (Collapse the selected folder)
LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder) RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder) Shortcut Keys for Character Map
After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:
RIGHT ARROW (Move to the right or to the beginning of the next line)
LEFT ARROW (Move to the left or to the end of the previous line)
UP ARROW (Move up one row)
DOWN ARROW (Move down one row)
PAGE UP (Move up one screen at a time)
PAGE DOWN (Move down one screen at a time)
HOME (Move to the beginning of the line)
END (Move to the end of the line)
CTRL+HOME (Move to the first character)
CTRL+END (Move to the last character)
SPACEBAR (Switch between Enlarged and Normal mode when a character is selected) m*cro$oft Management Console (MMC)
Main Window Keyboard Shortcuts
CTRL+O (Open a saved console)
CTRL+N (Open a new console)
CTRL+S (Save the open console)
CTRL+M (Add or remove a console item)
CTRL+W (Open a new window)
F5 key (Update the content of all console windows)
ALT+SPACEBAR (Display the MMC window menu)
ALT+F4 (Close the console)
ALT+A (Display the Action menu)
ALT+V (Display the View menu)
ALT+F (Display the File menu)
ALT+O (Display the Favorites menu)
MMC Console Window Keyboard Shortcuts
CTRL+P (Print the current page or active pane)
ALT+Minus sign (-) (Display the window menu for the active console window)
SHIFT+F10 (Display the Action shortcut menu for the selected item)
F1 key (Open the Help topic, if any, for the selected item)
F5 key (Update the content of all console windows)
CTRL+F10 (Maximize the active console window)
CTRL+F5 (Restore the active console window)
ALT+ENTER (Display the Properties dialog box, if any, for the selected item)
F2 key (Rename the selected item)
CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)
Remote Desktop Connection Navigation
CTRL+ALT+END (Open the m*cro$oft Windows NT Security dialog box)
ALT+PAGE UP (Switch between programs from left to right)
ALT+PAGE DOWN (Switch between programs from right to left)
ALT+INSERT (Cycle through the programs in most recently used order)
ALT+HOME (Display the Start menu)
CTRL+ALT+BREAK (Switch the client computer between a window and a full screen) ALT+DELETE (Display the Windows menu)
CTRL+ALT+Minus sign (-) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
CTRL+ALT+Plus sign (+) (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.)
m*cro$oft Internet Explorer Navigation
CTRL+B (Open the Organize Favorites dialog box)
CTRL+E (Open the Search bar)
CTRL+F (Start the Find utility)
CTRL+H (Open the History bar)
CTRL+I (Open the Favorites bar)
CTRL+L (Open the Open dialog box)
CTRL+N (Start another instance of the browser with the same Web address)
CTRL+O (Open the Open dialog box, the same as CTRL+L)
CTRL+P (Open the Print dialog box)
CTRL+R (Update the current Web page)
CTRL+W (Close the current window)

Hope this helps in you daya to day works.....