Oracle Schema Export,Import technique and related sql

1)      To Export Schema:
    a) Go to   \OracleSetup\NETWORK\ADMIN and open “tnsnames.ora”.
    b) copy “ORCL =

                 (DESCRIPTION =

                   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                       (CONNECT_DATA =

                            (SERVER = DEDICATED)

                           (SERVICE_NAME = orcl)

                       )

           )”
 and paste under the same page. And Change the” Host” and “SERVICE_NAME” from where you want to export it and give new “ORCL” name. Then save it.

# There is another good way to create new Service Name “ex-ORCL” . As bellow-

Go to- Oracle - OraDb10g_home1 => Configuration and Migration Tools => Net Manager => Select Service Naming and click on Plus (+) Sign=>Write Net Service Name(Any Name u want 2 give)=>Select TCP/IP (Internet Protocol)=>Write Host name (Ex-“localhost”/IP address for Another PC) and Port Number (If self PC then self ORCALE PC’s Port Number Or If another PC then another  PC’s ORCALE Port Number)=>Write Service Name (ex-ORCL/XE)=>Test=>If shown Successful then=>Finish.

c) Open a new Notepad and write” exp userid=e_rajuk/e_rajuk@maruffile=F:\e_rajuk14.dmp” and save as “.bat” file. Here e_rajuk=User Name,e_rajuk=Password, maruf=ORCL.
d) Then double Click on that “.bat” file. Then you will get your “.dmp” file as a database.

2)      To Import  Schema:

a) Run => write “imp” and press enter =>user name +password=>drag and drop database (.dmp file) and press enter.

b) Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export server uses UTF8 NCHAR character set (possible ncharset conversion)

List contents of import file only (yes/no): no >(press enter)

Ignore create error due to object existence (yes/no): no > yes (write it and press enter)

Import grants (yes/no): yes > yes (write it and press enter)

Import table data (yes/no): yes > yes (write it and press enter)

Import entire export file (yes/no): no > yes (write it and press enter)

. importing MY_DB's objects into MY_DB
. . importing table                 "ADP_PTA_INFO"      28325 rows imported
. . importing table                         "AGES"         14 rows imported
. . importing table      "AGEWISE_STUDENT_SUMMARY"     409812 rows imported
. . importing table "AGEWISE_STUDENT_SUMMARY_CONT"      60132 rows imported
. . importing table              "APPLICATION_MPO"          0 rows imported
. . importing table                   "AUDIT_INTO"      36447 rows imported
. . importing table                  "AUDIT_TRAIL"          0 rows imported
. . importing table         "BOARD_AGRI_DIPLOMA09"        111 rows imported
. . importing table "BOARD_ALLVOCATIONAL_RESULT2009"       4294 rows imported
. . importing table       "BOARD_ALL_RESULT_07_11"
Import terminated successfully with warnings.
sQL> exit;

3)      To Create User :a) If there is no dba privilege oriented user then go to all program => Oracle - OraDb10g_home1=>Application Development=>SQL Plus
        b) Write only “sys/sys as sysdba” or  “sys/sys as system” or   “sys/sys as systemprc” in the user      name field and press enter.
        c) write ”create user username identified by password;” =>Enter
            For giving previously created user  write “grant dba to username identified by password” or “grant dba to username” =>Enter
      d)  If there is a dba privilege oriented user then go to Run=> write “sqlplus” and press enter => that dba privilege user’s user name +password press enter=>write ”create user username identified by password;” =>Enter
        For giving previously created user  write “grant dba to username identified by password” or “grant dba to username” =>Enter
# to switch over sqlplus to imp just write “$imp”  and press enter.

4)      To Drop a user :then go to Run=> write “sqlplus” and press enter => that dba privilege user’s user name +password press enter=>write ”drop user username cascade;”

5)      Write” http://localhost:5560/isqlplus” for the browsing the database.

6)      To see all user name =” select username from dba_users;”Or,
“SELECT username FROM all_users ORDER BY username;” Or, “select distinct username from dba_users;”
To find the current databse:
SELECT * FROM global_name;

7)      To see all Tables name of a schema=”select * from tab;”Or” select tname from tab(It only show the tab name)”

8)      To see the Tables all column name=”SELECT column_name,data_type,data_length,data_precision,nullable FROM all_tab_cols where table_name ='HOSPITAL_SERVICE';” Just change the table_name  and see all column of expected table.

How to Change and see the default http port of ORACLE XE:

1)      Go to “Run”

2)      Write “sqlplus /nolog” and press enter

3)      Do as below-

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 10:40:44 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect
Enter user-name: system
Enter password: <enter password if will not be visible>
Connected.
SQL> Exec DBMS_XDB.SETHTTPPORT(8087); [Assuming you want to have HTTP going to this port]

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
            , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port

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

     8087          0

SQL> quit