We have a vendor-supplied 11g database where records are split between two schemas -- an ACTIVE schema and an ARCHIVE schema. Each object has both a corresponding object in both of the ACTIVE and ARCHIVE schemas.
The vendor also has a third schema where each object is merely a UNION ALL of the associated ACTIVE and ARCHIVE schema objects. For the sake of example, I'll call that schema COMBO.Over the years, we've created queries and reports that reference both the COMBO and ARCHIVE schemas and that has worked just fine.
The vendor has now set up a secondary database for us that we can use when the primary database is offline for patching/upgrades/etc. The trouble is, this secondary database only has the ACTIVE schema and records. The vendor will not be writing any ARCHIVE records to it.
Primary DB: ACTIVE, ARCHIVE, and COMBO schemas
Secondary DB: only the ACTIVE schema.
is there a way to set up the missing ARCHIVE and COMBO schemas on the secondary DB such that we won't have to rewrite our SQL to accomodate the lack of an ARCHIVE schema when we move reports over to the backup database?
Of course, no records would need to be returned from the virtual ARCHIVE schema, but I'd love for the untouched SQL to run without error.
I have a table where I want user to fill in unique values for a field which is easy to do.
Problem is sometimes the values can be null so an ordinary unique constraint does not work because multiple null records. Is there a way of validating only non null values to ensure all data entered that is non null is unique?
I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).
Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3). Thinking like MySQL works I created CODEapp/oradata_smart_ora4 60.6K 400G 60.6K /oradata/SMART/ora4 specifically to store the database there.
The databases startups and mounts no problem. Note that this server will manage with millions/billions records throughout its lifetime.
1. Now that my Database is created, whats the next step? Create the Schema or Tablespace? 2. Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?
I need to create a structure DATABASE=>SCHEMA=>TABLE as
DB=>SC=>EMPLOYEE ...but after connecting database i could create table only user my user schema(own schema)only . I want to create a new schema called SC as public and need to create a table .
I have SQL Server database I would like to migrate into Oracle. The database supports a large application. It is around 10GB. I requested a new instance but was advised I would have to pay for that but if I asked for a new Schema it could go in our current company instance. I am fine with that since it wont cost more money if I just add a new schema to our Company Oracle instance. Just curious what is the advantage of getting a new instance compared to creating a new schema for 10 GB of data?I assume the advantage of creating a new instance is our Schema (in new instance) and work will have its own space/house and can grow in size without any issues?
I have a list of materialized views in schema A. I want to create a refresh group and then refresh it from Schema B (Dynamically-run time based on some criteria). What Grants are necessary on schema B in order for it to be able to create and refresh the groups on Materialized views in Schema A.
I know that one of the Options is to, GRANT ALTER ANY MATERIALIZED VIEW as a SYS user. But I do not have any SYS privileges.
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/******** schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size group by Condition, Size;
COUNT(*) CONDITION SIZE -------- ---------- -------- 3 MINT L 2 FAIR L 4 FAIR M 1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE -------- ---------- -------- ---------- 3 MINT L ISNULL 2 FAIR L NOTNULL 2 FAIR M NOTNULL 2 FAIR M ISNULL 1 MINT S ISNULL
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate); insert into test values (1, sysdate - 1); insert into test values (1, null); insert into test values (1, null); -- ----- insert into test values (2, sysdate); insert into test values (2, sysdate - 1); insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
SQL> Describe Stu_Table Name Null? Type ----------------------------------------- -------- ---------------------------- STU_ID VARCHAR2(2) STU_NAME VARCHAR2(10) STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null; ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null * ERROR at line 1: ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL; ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL * ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
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.
move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.
A user is using an ad hoc tool similar to SQL Developer called PeopleSoft Application Designer.
He creates a connection to the db, then issues an alter session set current_schema = 'restricted_schema'. The connected user does not have direct privileges on the "restricted_schema" which they call SYSADM.
After changing the schema context in that manner he creates objects in SYSADM. A schema trigger is then fired and grants privileges on the new objects created in SYSADM. Doing the same in either SQL Plus or SQL Developer does not fire the schema trigger.
I think SQL Plus and SQL Dev are working as they should. Altering the session like that does not change your identity - just the schema context. But, when you examine v_$session, the connection with this other tool looks exactly the same as one from SQL Plus or SQL Dev when changing the schema context in the session.
Instead of trying to figure out what this other tool is doing, is there any way for that schema trigger to fire when using this process from one of our tools?
Suppose that, I have two tables: emp, dept emp records the empid, emp_name, deptid dept records the deptid, dept_name
Here is a record, it's a president or some special position in company, so it's deptid is set to NULL. Here comes the question, how can I print all the emp_name with their deptartment name?
I know how to print all the emp_name with their department name if they have dept_id, but is that possible that I merge the record with dept_id NULL?
I have a standard schema named ABC and 600 more schema's over there in my database.They all has same table name and column name as on standard schema. But in some tables number of columns varying. So I need to compare all schemas with my standard schemas column name. I create below script but it is generating output in infinite loop.
SET SERVEROUTPUT ON DECLARE V_COLS VARCHAR2(20);
BEGIN FOR CUR_CCD IN(SELECT DISTINCT TABLE_NAME,OWNER FROM ALL_TABLES WHERE OWNER LIKE 'CCD_MAIN' [code]....
SELECT TO_DATE('21-NOV-2010') DAY, 0 RATE FROM DUAL UNION SELECT TO_DATE('22-NOV-2010') DAY, 10.5 RATE FROM DUAL UNION SELECT TO_DATE('23-NOV-2010') DAY, 0 RATE FROM DUAL UNION SELECT TO_DATE('24-NOV-2010') DAY, 0 RATE FROM DUAL UNION
I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. The problem is:
When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.
Here are the code to simulate this problem:
--Table create table TB_TEST ( ID NUMBER(10) not null, FLAG NUMBER(10) not null, TAG VARCHAR2(16) not null, TS_ATU_DTR TIMESTAMP(9) not null );
"old.TS_ATU_DTR=, " Isn't it right????? Why the other fields aren't null? I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it?
MY requirment is: I want the first three nullable attributes. For Eg: If I have 60 columns in table, I need to fetch the first three null data in a row.
