Spool All Privileges Of A Particular User
Nov 21, 2012I am trying cleaner and simple way to spool out all privileges of a user "ABC" in a way that it generates grant statement for them.
View 6 Replies
ADVERTISEMENT
Forms :: User Privileges - Assign Permissions To One User To Add / Delete / Edit Data?
May 28, 2011how can i assign permissions to one user to add,delete,edit data and other user should be able to perform all functions or selected functions
View 12 Replies View RelatedSQL & PL/SQL :: What Are The Privileges The User Is Having On A Object
Oct 2, 2010How can we find out what are the privileges the user is having on a object.
For example I want to find what are the privileges the "uesr1" is having on "stud_table".
SQL & PL/SQL :: No Privileges On Tablespace User
Jul 25, 2013> conn sys as sysdba;
enter password:TIGER
>create user ram identified by ram;
>grant create session to ram;
>conn scott/tiger
>create table ram.emp2
as select * from emp;
>ora-error:- 01520 NO PRIVILEGES ON TABLESPACE 'USERS'
this is the query and i want to create emp2 table for user ram while i am connected to scott,
ealier i was able to do this but now oracle shows error 01950,
then i connected to sys again and ran this command >alter user scott quota unlimited on scott;
but it also not worked
Security :: History Of Privileges Changes Done For A DB User
Jan 1, 2013As how do I query, as when was the last time or rather list of privileges changes done for a db user and what was the changes made?
View 1 Replies View RelatedSecurity :: Grant Privileges To A User?
Dec 10, 2010I am a java developer and would like to install data locally in my computer for testing purpose.
I login as sysdba in sqlplus, created a user.
In the installation documentation it said :
This user should have at least the following privileges.
CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE USER, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION CREATE TRIGGER, AND CREATE TYPE.
If you choose, you can grant DBA role to this user.
Note: Coeus Schema owner should have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA.
1)How to grant all this prvileges to that user ?
2)How this user will have CREATE USER and ALTER USER right explicitly granted to it, not through a role like DBA ?
Create Another User User2 With Same Privileges Which User1 Has
Jan 24, 2012I have created a user "user1" who has dba role.i wanted to create a another user "user2" with same privileges which user1 has. so i granted dba role to user2 but i dont want user2 to have "drop" privilege on any objects except his own objects.
View 1 Replies View RelatedTablespace - Privileges To Newly Created User
Apr 13, 2012i have created a new tablespace and assigned it to new user. What are the minimum no. of grants i should give, so that the user can made any kind of changes to all the objects within the tablespace and cant access other tablespace.
View 3 Replies View RelatedForms :: User Privileges On Application Level?
Nov 15, 2011Is there any way of allowing the some menu items to access by the users, not the whole menu. The problem is that in the system where I am working is that the users are on application level not on database level. If the users are on db level then by using roles and grants one can set privileges for each db user. Is there any way to block user from accessing some part of menu or from the forms itself?
View 1 Replies View RelatedServer Administration :: Insufficient Privileges To Other User
May 21, 2011WHEN I CONNECT TO SCOTT OR ANY KIND OF USER (EXCLUDE SYS)I CONNECT EASILY BUT PROBLEM WITH SCOTT OR OTHER THEY CANNOT BE
SHUTDOWN THE DATABASE AT THAT TIME THE ERROR ARISES ARE
ORA-01031: insufficient privileges SO OUR DATABASE CANT BE SHUT IT DOWN.
74 Privileges From Different Users On Different Tables To One Common User?
May 20, 2013i have to give 74 privileges from different users on different tables to one common user,how can i do this?
View 7 Replies View RelatedSecurity :: Grant Privileges To Public User
Jul 9, 2011grant insert,update,delete,select on staging_tb1 to public;
What is public here? i know something about public user like it is users exist in the database .
SQL & PL/SQL :: File Spool Using UTL_FILE
Apr 27, 2012How to spool the Japanese characters in table using UTL_FILE. I tried with utl_file.fopen it's general,it's spooling. but i am not sure it this right way or not. in this case we need to change any character.
We can't see this characters in TOAD. Only possible in PLSQL developer
create table test1(name varchar2(1000),fname varchar2(1000))
insert into test1 values ('名を入力してください。','姓を入力してください。')
commit;
PL/SQL :: Spool File With CTE Query?
Jul 10, 2013I am using something like below , the query runs fine in oracle SQL developer.But I would like to generate a text file by running the script, so I added the Spool command, and those settings,
I tried to run it in SQL developer or SQLplus, they both fail.In SQL developer: I got: ORA-00933: SQL command not properly ended00933. 00000 - "SQL command not properly ended"*Cause: *Action:Error at Line: 16 Column: 1,542In SQLPlus, but I got an error: SP2-0734: unknown command beginning "CteRace As..." - rest of line ignored.it seems it doesn't understand the common expression table. -- SQL code below:set heading offset feedback offset newpage noneset echo offset termout
[code]...
Format Output File By Command Spool
Jan 20, 2011I use sqplus in oracle to output the command output to text file .
I use below set environment variable.
SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
In ouput file , it look like
SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;
any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"
SQL & PL/SQL :: How To Export Package Body Using Spool Command
Jan 8, 2013I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:
WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;
[code]....
WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......
In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.
Client Tools :: Spool Only Time And Stats?
May 9, 2010m spooling the results of some performance tests to a text file, the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?
my script looks like:
=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT
SPOOL f:/testresults.txt
[code]...
Client Tools :: Spool Output And Rollback
Oct 26, 2011I need to do the following via the sql plus command prompt :
1. Execute a large ".pls" script file which does various inserts and updates to a table.
2. Spool the output of "select * from updatedtable;" to a text file to see the changes made.
3. Rollback all the updates and inserts in the script.
Client Tools :: Create Sub Folder Using SPOOL
Mar 5, 2010I want to ask if we could create a sub folder using the SPOOL command. I tried the following but it gave "Cannot create Spool file" error.
SPOOL C:TESTABCABC.sql
where TEST is already present in the C drive whereas I want SPOOL command to create a sub-directory named 'ABC' and then create the spool file 'ABC.sql' isnside it.
Is this possible ? My OS version is MS XP.
Client Tools :: Add Application Name In A Spool File?
Jul 26, 2011To add application name in a spool file, I am using the below way.
sqlplus username/pwd@tns @xyz.sql APP0115
SQL> define appname="'&1'"
Enter value for 1: APP0115
SQL> prompt &appname
'APP0115'
SQL> spool &appname._html_jobs.csv;
SP2-0768: Illegal SPOOL command
Usage: SPOOL { <file> | OFF | OUT }
where <file> is file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]
But I am getting the above error in the spool file clause because of single quote printing infront of the spool file. But the method of defining a character is "'&1'". So I cannot avoid this single quote in the define clause.
'APP0115'
print the appname like APP0115 instead 'APP0115'.then only I can use this in the spool file clause?
Globalization :: Spool Brazilian Characters To A File
Jun 6, 2013Oracle version Oracle Database 11g Release 11.2.0.1.0 - 64bit Production running on CentOS Linux release 6.0 (Final), kernel 2.6.32-71.29.1.el6.x86_64.
I am having a hard time spooling a file and displaying special Brazilian characters, even though I can see them correctly in SQLDeveloper:
LEOPOLDO COUTO DE MAGALHÃES JÚNIOR
Spool:
LEOPOLDO COUTO DE MAGALH?ES JUNIOR
I've tried changing the NLS_LANG at the session level, but that cannot be done. I don't want to change the default language of my DB, but really need these characters to display correctly in a file.
Client Tools :: Spool In UTF8 Not Always Works
Oct 25, 2011I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.
i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.
I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.
Client Tools :: Spool Output In PDF Format?
Jan 3, 2011I am looking forward for getting the output of audit records in a non-editable format as in pdf or something of that sort. Is it possible from Oracle 10g Rel-2?Can i spool output as PDF format?
View 6 Replies View RelatedPL/SQL :: Spool File Output From Unix To Windows
May 22, 2013I'm running a sql file in Unix (using SQLPLUS command) and i want the file output to be sent to local windows directory. I specified the windows directory name in my Spool command (Spool C:/<Directory Name> ) , But it is not working
Our Unix server is a FTP server but i don't want to FTP the spool file from unix to Windows as the spool file is Huge and it takes hours for the transfer to complete (and we have to run the script multiple times).
Is there a way i can have the spool file created in Local windows directory when i run my sql script in Unix?
PL/SQL :: Spool Data From Tables Into Flat Files
Oct 23, 2012I am trying to spool data from tables into flat files. I am using the following scripts to accomplish it
1. A cmd file (windows) that makes a call to a sql file
2. The SQL file which generates another query file at the run time, depending upon the table name passed to it
3. The run time query file , that executes the final query and spools the data into a txt file | delimited
For e.g. :
Actual command passed C:Spool_utilityspool_utility TABLE_NAME
E.g. of the spool utility file :
@echo off
SET dbuser=XX@YY
SET dbpw=xxxx
echo %date% - %time% - Start > %1%log.txt
echo START
sqlplus -s %dbuser%/%dbpw% @spool_utility.sql %1>%1.txt
echo %date% - %time% - Done >> %1%log.txt
echo DONE
E.g. of the spool_utility.sql
set echo off
SET newpage 0
SET feedback off
SET linesize 32767
set pagesize 0
[code]........
The above file generates a table_name.sql file with the actual table name at run time and gets executed and the output is written to the table_name.txt file.
This works perfectly fine. But the issue is when someone passes some wrong table name or if there is a actual run time error while executing the query , the error with details itself itself gets written to the end spool file.
For e.g. : if i do this just to generate an error and execute it from command line, the query generates an error and writes the error to the spool file , but at the command prompt where I executed the command I do not see any error and the process seems to have run perfectly well
set xxx on xxx off as above
spool &1.sql;
Prompt Select * from &1 where rownum><10---this will cause the issue
spool off
set termout ON
@ &1
EXIT
Eg of spool file generated :
from table_name WHERE rownum><=10 *
ERROR at line 62:
ORA-00936: missing expression
My question is, is there any way i can capture this runtime error and return this error to my calling sql script spool_utility.sql and then propagate it to the calling command file and do some tasks for eg removing the spool file and writing the actual error to a log file . Basically any way to know at my OS calling level that the entire spooling operation was unsuccessful.
PL/SQL :: Data To Existing File Using Spool Command?
Jul 19, 2012I am calling a stored procedure from a shell script. This stored procedure is having a CLOB object as an OUT parameter. How to write the data in CLOB object a existing file which is there in my client system. Below is the shell and sql scripts.
Shell script
( echo "hello" ) > /root/file.txt
sqlplus -s $user/$pass@$tns @/root/proc.sql /root/file.txt << EOF
EOFSQL script
set pages 0
set trimspool off
[Code]...
spool offThis code writes contents of the OUT variable to file.txt, but my existing data ('hello') is lost.
I figured the it...Use append in the spool command...
Client Tools :: Dynamically Spool Data In PL/SQL Block?
Nov 22, 2012I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table A:-
C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5
Table B:-
Table_Name Column_Name
--------- ----------
A C1
A C2
A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
Client Tools :: Spool Without SQL Statement And Commands In PLSQL?
Jan 3, 2011I have a PLSQL script.
sqlplus
$DB_ACCT << EOSQL
set heading off
set termout off
set pagesize 0
[code]....
My output in FILE.txt looks like this
SQL>
SQL> Select col_with_a||col_with_b||col_with_c from
2 alphabet_table;
abc
[code]....
I tried several options by putting set echo off, etc.What do I include in the sql script to get the output as :
abc
abc
abc
without the SQL prompt and the statement, and the spool off command?
Client Tools :: Creating Spool File Dynamically
Aug 20, 2010how to create a file in a folder based on todays date. i need to know how to define a variable in sqlplus and assign a value to it.Here is the code below. The code gets executed without creating a spool file.
DEFINE _DATE = replace('C:\_sysdate_EU001.csv', '_sysdate_', TO_CHAR(SYSDATE, 'DD-MON-YYYY'))
spool _DATE
set serveroutput on size 100000
select * from dual;
spool off
PL/SQL :: Arabic Letters In Sqlplus Spool Output In CSV Format
Jul 4, 2013I have 11.2.0.3 Db in HP UX 11.31, I have to spool a table output in csv format which contains arabic letters in few columns. my spool output is not showing the arabic letters properly.
[oratest@]$ echo $NLS_LANGAmerican_America.
AL32UTF8 V$NLS_PARAMETERS NLS_LANGUAGE
AMERICANNLS_TERRITORY
AMERICANLS_CURRENCY
$NLS_ISO_CURRENCY
AMERICANLS_NUMERIC_CHARACTERS
.,NLS_CALENDAR
[Code]....