I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.
CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.
CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.
CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?
how to change the default format of a number value using alter session statement?
I've a problem when I show a value like this "0.123456": the select statement returns ".123456". Is there any way to force a zero value before the character separator?
I was trying to execute below query inside a sql script which is called from a shell script on linux environment. EXECUTE IMMEDIATE 'alter session set events ''10176 trace name context forever'' ';
This thing works at our test environment. But giving error "SP2-0670: Internal number conversion failed" when tried on live(production) environment. What could be the reason? I found another way of writing such queries on net as follows, will it be useful? EXECUTE IMMEDIATE q'|alter session set events '10176 trace name context forever'|
I'm using Oracle SQL Developer and querying on date fields. I use this "alter session" statement to set my default date time format to get my datetime values. My question is, is there a way I can incorporate the following statement into my "select" query so that Crystal can read it? I use the statement below before my select statement to query datetime differences. Problem is, Crystal doesn't like the statement below.
Using TO_CHAR( ) and formatting my date fields that way doesn't work when I have to take the difference between date fields to the minute/second level. That's why I use the alter session command below, but I cannot seem to incorporate it with my select statement as a Crystal data source.
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
Is it possible to alter a session within Oracle forms as I get the following error when trying to compile the following block.
BEGIN EXECUTE IMMEDIATE ( 'alter session set nls_date_format = 'DD-MON-YYYY'''); END;
Error: Encountered the symbol "IMMEDIATE" when expecting one of the following
The basic requirement is to validate a date field on the form against the current system date, but as the form is in format DD-MON-YYYY and the Database on M/D/YYYY I am struggling to compare dates successfully.
We have a Data Source with min_pool_size (10) and max_pool_size (20). A Data Source is by default a connection pool. By starting a transaction we are retrieving a connection from the pool (i.e., opening it to retrieve data, perform queries, inserts and updates). Our application server is JBoss. An application workflow uses many transactions to build a product. The same connection is not used by the application for the entire workflow; but, it uses and returns them to the connection pool. We do not use Java syntax like "rs.close():"... this is performed by iBATIS.
On the Linux side when we execute a "ps" command (ps -elf|grep -i ora) we see all the Oracle processes. A further refinement of that command (ps -elf|grep -i local=no)shows a list of the "waiting" connections in the connection pool. The DB may be queried with the following syntax:
SELECT schemaname, sid, serial# FROM gv$session where schemaname = 'APP_USER' order by SID;
A list of connected sid and serial numbers is returned, identifying which connections are in use. From here we are able to force the connection to trace by executing the following:
exec dbms_monitor.session_trace_enable(249, 6595, TRUE, FALSE); ! 249 and 6595 being SID and Serial# from query above
There should be no need to execute the inverse, since the connection is returned to the pool when the transaction is committed or rolled back.
We are trying to trace in order to use the Quest Benchmark Factory. Their instructions request the following syntax be applied to each session:
alter session set events '10046 trace name context forever, level 4';
and again, the inverse should not be necessary.
alter session set events '10046 trace name context off'
When it became too cumbersome to alter each session as it appeared, we issued an "alter system" to monitor (trace) everything. The trace files filled the disk, and four hours of testing was stopped two hours in. Doing a system level trace is probably not a good idea.
My first inclination was to create a post-logon trigger to set trace in the session; however, these connections, coming from an JBoss connection pool, do not logon each time, and I presume that they are not all the "same session".
We opened a support ticket with Quest last Friday and do not have an answer yet. This was the third ticket with them, the first to get Benchmark Factory installed (the original installer did not work). The second ticket was to setting up a shared directory on Linux with a folder on Windows, a setup configuration required by their tool. The third ticket to address this issue.
They needed to contact "the developers" to answer the last two questions. Their latest suggestion is to fix ticket two so we "won't need to trace" anything. How do I set trace in these connections?
Requirement: I need to create a Function to allow users to change their own password when they are logging in to an application. Also, I would prefer to not use the ALTER command.
I can use trigger to monitor ALTER TABLESPACE ddl statement against a particular tablespace in a schema.
CREATE OR REPLACE TRIGGER HOT_MAIL_DDL_CHANGE AFTER ALTER TABLESPACE <tbs_name> ON SHANNURA.SCHEMA BEGIN INSERT INTO HOT_MAIL_DDL_AUDIT_LOG VALUES (SYSDATE, SYS_CONTEXT('USERENV', 'SESSION_USER'), ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME ); END; /
I think, line no.2 isn't a valid statement. Or can I use AUDIT instead - in fact, I want to monitor/audit only ALTER TABLESPACE ddl statement and that too for a particular tablespace only.
how can i grant a user permission to create tables and indexes in other user schema.Grant create table permits the user to create tables in his own schema which is part of Resource role.Grant any table will permit him to create table in any schema including system which i don't want.
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.
alter index ZZAP_selected_invoices_n1 rebuild online;
I want to create notification/ or triggered mail to my client and my dba group which will occur while session and user has been blocked. I want to create this procedure in 12c OEM.
if a user have alter table gant but could not alter .. what additional grant it need
SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)); alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3)) * ERROR at line 1: ORA-00942: table or view does not exist
I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER --------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB Name Null?Type ----------------------------------------------------- -------- ------------------------------------ Q_NAME VARCHAR2(30) MSGID NOT NULL RAW(16)
YUV > YUV > YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB); ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB) * ERROR at line 1: ORA-22858: invalid alteration of datatype
YUV > YUV > YUV > YUV > YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB); ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB) * ERROR at line 1: ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB YUV >
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR INSERT INTO EVENT_SUB... OR ALTER TABLE EVENT_SUB WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this: select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID' Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
