How to use SQL to generate SQL batch processing files?

Source: Internet
Author: User
Tags how to use sql
How to use SQL to generate SQL batch processing files?
    • Software environment:

        1. Windows NT4.0 + Oracle 8.0.4
        2. Oracle installation path: C: \ orant
    • Question:

        1. You need to perform the same SQL operation on each table of the database user. It is very troublesome to type SQL statements once or once.
  • Implementation Method:
    SQL> set heading off -- disable output Column Title SQL> set feedback off -- disable display of Count feedback information in the last row to list definitions of all synonyms under the current user, can be used to test the true existence of synonyms select 'desc' | tname from tab where tabtype = 'synonym '; select 'select' | tname | ''', count (*) from' | tname | '; 'From tab where tabtype = 'table'; grant the select permission for all qualified tables to publicselect 'Grant select on' | table_name | 'to public; 'From user_tables where conditions; delete various objects under the user select 'drop' | tabtype |' '| Tname from tab; Delete the qualified user select 'drop user' | username | 'cascade;' from all_users where user_id> 25; quickly compile all views-when you pour the database into a new server (re-build the database), you need to re-compile the view, ---- the connection from the tablespace view to other tablespaces may cause problems. You can use the language features of PL/SQL to quickly compile the table. SQL> spool on. sqlsql> select 'alter view' | tname | 'compile; 'from tab; SQL> spool off and execute on. SQL. SQL> @ on. SQL, of course, can also be quickly used to authorize and create synonyms, such as: SQL> select 'Grant select on' | tname | 'to username; 'from tab; SQL> select 'create synonym' | tname | 'for username. '| tname |'; 'from tab;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.