Posted by
test
|
Posted on
12:11 PM
Category:
Oracle
,
Toad
Here is the steps to do it.
1) Install the oracle instant client. Unzip the files to a directory, and then copy the path of this directory to the clipboard.
2) Add this directory to the PATH environmental variable.
- To do this, right click on My Computer,
- Go to the Advanced tab,
- Click 'Environment Variables'.
- Under System variables, find 'Path'.
- Select it, and click edit.
- CTRL-V to paste the directory into the string,
- OK.
- Before: %SystemRoot%\system32;%SystemRoot%;C:\Program Files\ATI Technologies\ATI Control Panel;
- After: %SystemRoot%\system32;%SystemRoot%;C:\Program Files\ATI Technologies\ATI Control Panel;C:\instantclient;
3) In the same window, under 'User variables for ...', Click the New button. Call the variable 'TNS_ADMIN'. ctrl-v to paste the directory path into the 'variable value' field. Say OK, and close out the 'My Computer' properties windows.
5) Create a file called 'TNSNAMES.ORA' in your instant client installation directory. In this file designate the connection parameters for your database.
Sample TNSNAMES.ORA
CODE
DB_SYSTEM=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT =
1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
)
)
5) Reset your computer, start Toad, and a new connection. You should see DB_SYSTEM as an option under the database field. Set your username and password, click on connect
Toad is nice tool but i don't like this dependency of Oracle Thin client but I think that is copyright matter with Oracle but it would be nice to see Toad come with Oracle client embedded as one click install .
Happy Querying. :-)
Posted by
test
|
Posted on
1:31 PM
Category:
Oracle
,
sqlplus
How to execute an SQL script file in SQLPlus?
To execute a script file in SQLPlus, type @ and then the file name.
SQL > @[file]
if your file was called XXX.sql, you'd type the following command at the SQL prompt:
SQL > @script.sql
The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.)Check the current directory using pw command on linux based systems.
If you need to execute a script file that is not in the current directory, you would type:
SQL > @[path][file]
Example:
SQL > @/oracle/temp/XXX.sql
This command would run a script file called script.sql that was located in the /oracle/temp directory.
Posted by
test
|
Posted on
7:13 PM
Category:
Documentum
,
Oracle
,
Query
,
Schema
How to drop all the tables , views from the Oracle DB schema for recreating the repository?
1-
Create one sql file and name it -createdroptables.sql and write below given queries into this file
SET SERVEROUTPUT ON;
SPOOL C:\droptables.LOG;
SELECT * FROM (SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' FROM user_tables UNION
SELECT 'DROP VIEW '||VIEW_NAME||';' FROM user_views UNION
SELECT 'DROP SEQUENCE '|| SEQUENCE_NAME||';' FROM user_sequences UNION
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM user_synonyms UNION
SELECT 'DROP FUNCTION ' || OBJECT_NAME ||';' FROM user_procedures UNION
SELECT 'PURGE RECYCLEBIN;' FROM dual) ORDER BY 1 ASC;
SPOOL OFF
2 Run this sql file on sqlplus console
SQL> @C:\createdroptables.sql
3 Rename the droptables.log file to droptables.sql which is created in c:\ drive
4 Run this sql file on sqlplus console
SQL> @C:\createdroptables.sql
5 Check the tables in schema by running below given query
select table_name from user_tables;
6 You should get no table name ie its now empty schema.
Good Luck recreating repository.
