oracle grant all privileges to user

View all tables in Oracle: Select * From TAB/dba_tables/dba_objects/CAT; View the table created by the user: Select table_name from user_tables; // The table of the current user Select table_name from all_tables; // tables of all users Select table_name from dba_tables; // i

It is possible that many customers who use Oracle will experience situations where they want to export all of their tables, and this article provides a way to help you solve the problem easily. First log on to the Oracle database with the user under Sqlplus, and then paste the following content into Sqlplus: set feed

Oracle deletes all objects under a user and performs with cautionIn PL/SQL, click "New", "Command Window", Paste and execute directly--Delete an object under a userSet heading off;Set feedback off;Spool D:/dropobj.sql;Prompt--drop ConstraintSelect ' ALTER TABLE ' | | table_name| | ' drop constraint ' | | constraint_name| | '; ' from user_constraints where constra

How does Oracle search for fields with a value in all tables under the current user?Create or replace procedure My_pro_searchkeyword isV_sql VARCHAR2 (4000);V_tb_column VARCHAR2 (4000);V_CNT number (18,0);Cursor cur is select ' Select ' | | ' "' | | t1.table_name| | ' "." ' | | T1. column_name| | ' ' | | ', ' | | ' as COL_NAME, NVL (COUNT (t. "' | | T1. column_na

Label:  DECLARETYPE name_list isTABLE ofVARCHAR2( +);TYPE type_list isTABLE ofVARCHAR2( -);Tab_name name_list:=name_list ();Tab_type type_list:=type_list ();Sql_strVARCHAR2( -);BEGINSQL_STR:='Select Uo.object_name,uo.object_type from user_objects uo where Uo.object_type not in ("'INDEX"',"'LOB"') Order BY uo.object_type Desc';EXECUTEIMMEDIATE sql_strBULKCOLLECT intoTab_name,tab_type; forIinchTab_name.First: Tab_name. Last LOOPSQL_STR:='DROP'||Tab_type (i)||''||Tab_name (i);EXECUTEIMMEDIATE Sql

Create or replace procedure Drop_all ascursor Cur_obj is SelectUO. object_name, UO. Object_type fromuser_objects UOwhereUo. object_name notinch('Drop_all') and UO. Object_type notinch('LOB'); /*cursor cur_tablespace is select UT. Tablespace_name from User_tablespaces ut where UT. Tablespace_name not in (' SYSTEM ', ' sysaux ', ' UNDOTBS1 ', ' TEMP ', ' USERS ');*/v_obj_name user_objects. object_name%type; V_obj_type user_objects. Object_type%type;/*v_tablespaces_name user_tablespaces. Tablesp

Oracle deletes all objects (tables, views, triggers, stored procedures, and functions) of the current user) Incluincludeclaretype name_list is table of VARCHAR2 (40); TYPE type_list is table of VARCHAR2 (20); Tab_name name_list: = name_list (); Tab_type type_list: = type_list (); SQL _str VARCHAR2 (500); BEGINsql_str: = 'select uo. object_name, uo. object_type f

How Does Oracle search for fields containing a certain value in all tables of the current user? Create or replace procedure MY_Pro_SearchKeyWord isV_ SQL VARCHAR2 (4000 );V_tb_column VARCHAR2 (4000 );V_cnt NUMBER (18, 0 );Cursor cur is SELECT 'select' | '"' | t1.table _ name | '". "'| t1.Column _ Name |'" '| ''' |' as col_name, NVL (COUNT (t. "'| t1.Column _ Name

Label:1. System permissions1 SELECT *2 fromDba_sys_privs3 WHEREGRANTEE= 'lcam_sys_1216'4 UNION All5 SELECT *6 fromDba_sys_privs7 WHEREGRANTEEinch8(SELECTGranted_role fromDba_role_privsWHEREGRANTEE= 'lcam_sys_1216');System Permissions2. Object permissions1 SELECT *2 fromDba_tab_privs3 WHEREGRANTEE= 'lcam_sys_1216'4 UNION All5 SELECT *6 fromDba_tab_privs7 WHEREGRANTEEinch8(SELECTGranted_role fromDba_role_privsWHEREGRANTEE= 'lcam_sys_1216');Object PermissionsTurn: http://blog.sina.com.cn/s/bl

When deleting the data table often encountered foreign key constraints cannot be deleted, we can delete the database table through the following steps, it is recommended to delete the library before the database backup, develop good habits. 1. Delete foreign keys--Query the foreign key for all tables of the user, owner condition is userSELECT * from user_constraints c where c.constraint_type = ' R ' and c.

Oracle deletes all objects (tables, views, triggers, stored procedures, and functions) of the current user.Zookeeper DECLARE TYPE name_list is table of VARCHAR2 (40 ); TYPE type_list is table of VARCHAR2 (20 ); Tab_name name_list: = name_list (); Tab_type type_list: = type_list (); SQL _str VARCHAR2 (500 ); BEGIN SQL _str: = 'select uo. object_name, uo. object_type from user_objects uo where uo.

Some people said that they would first delete a user and then create the user. I want to delete all tables under this user, but do not delete others. 1. query all tables under this user: Executed under this

Label:Prerequisite: Under the cmd commandExport command: EXP username/password @ database owner= user name file= file storage path (for example: F:\ABCD.DMP)TEST: Exp zm/[email protected] OWNER=ZM file=f\abcd.dmpImport Command: IMP username/password @ database fromuser= user name touser= user name File=d:\cu.dmp ignore=yIMP: Command typecu/[email protected]: Impo

--. SQL script-- The only note is that f: \ dropobj. SQL is the operated. SQL;-- Your computer does not have a drive F. Replace it with drive D or E.-- Used to delete all objects of the current user-- Use for drop all objects in current user;Set heading off;Set feedback off;Spool f: \ dropobj. SQL;Prompt -- delete Cons

1, Select 'drop table' | table_name | ';'From all_tablesWhere owner = 'username to be deleted (uppercase )'; 2, Delete all tables Take user test as an Example For example: DeclareCursor cur1 is select table_name from dba_tables where owner = 'test ';BeginFor cur2 in cur1 LoopExecute immediate 'drop table test. '| cur2.table _ name;End loop;End; 3. Delete all obje

Select ' Drop table ' | | table_name | | ' "; ' | | Chr (13) | | Chr (from User_tables); --delete tablesSelect ' Drop View ' | | view_name| | ' "; ' | | Chr (13) | | Chr (from User_views); --delete viewsSelect ' Drop sequence ' | | sequence_name| | ' "; ' | | Chr (13) | | Chr (from User_sequences;--delete seqs)Select ' Drop function ' | | object_name| | ' "; ' | | Chr (13) | | Chr (Ten) from user_objects where object_type= ' FUNCTION ';--delete functionsSelect ' drop procedure ' | | object_name|

constraint constraint_name 禁用外键约束的命令为：alter table table_name disable constraint constraint_name然后再用SQL查出数据库中所以外键的约束名：select ‘alter table ‘||table_name||‘ enable constraint ‘||constraint_name||‘;‘ from user_constraints where constraint_type=‘R‘select ‘alter table ‘||table_name||‘ disable constraint ‘||constraint_name||‘;‘ from user_constraints where constraint_type=‘R‘14、ORACLE禁用/启用外键和触发器 --启用脚本SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select ‘AL

Application: Add a new primary key to the data table to speed up the data table access speed, and is the fastest index shaping. This program is suitable for running when you import an Oracle database without a primary key being removed.Code Description: All table primary key field names are set to ID, if the ID field already exists, then determine whether it is shaping, if not rename the field to [table nam

Tags: null SQL structure des ber rod comma connect appIf the Oracle server is loaded with multiple DB instances, enter the username/password @ database name at the user name. If the database server is not on this computer, you also need to add the address of the database server: username/password @ip address/database name. [Email protected] ~]$ sqlplus scott/[email Protected]/prod Sql*plus:release 11.2.0.3.

#!/bin/bash # Desc: This script is used to export all table structures for a primary User: indication, field name, field type # # DECLARE variables Sid_profile= "/home/oracle/xxxprofile" table_owner= "XXX" Table_list_file= "${table_owner}_tables.list" Get_table_structures_sql= "Get_${table_owner}_table_structures.sql" Table_structures_file= "${table_owner}