Tag Info

Hot answers tagged

4

Your table's segment is only 2 MB. The rest of the data is stored in the LOB segments which are physically separate from the table segment. SELECT column_name, segment_name FROM dba_lobs WHERE owner = 'MYUSER' AND table_name = 'MYTABLE' should show you that there are two LOB segments associated with the table. You can then query dba_segments to ...


3

I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there. If you ...


2

Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time. This is the same as using CONSISTENT=Y in the legacy exp utility. Examples here. If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a ...


2

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data. Example below. Test table: create table yourperftest ( pk number, col1 number, col2 number, col3 number ); Test data: insert into yourperftest values (1,1,1,1); insert into yourperftest values (2,2,2,2); insert into yourperftest values ...


2

It's possible to add a_type to b, and have check constraint on b.a_type. Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other ...


1

Following sample may be useful to build query which you want to write. USE tempdb GO CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY ) GO INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4') GO CREATE VIEW v_V1 AS SELECT 'id1' id, 2 value UNION SELECT 'id1' id, 1 UNION SELECT 'id1' id, 3 UNION SELECT 'id2' id, 1 GO CREATE VIEW v_V2 AS SELECT 'id3' ...


1

It turns out the user had been granted other privileges by a different account as well as the one we use, so we were unable to see those privileges in ALL_TAB_PRIVS. We had to look in DBA_TAB_PRIVS, and sure enough, there were the other permissions. Thanks to all for chiming in!


1

Assuming your DB target name as seen in EM is ORCL11.WORLD and you want to set it's Monitoring Configuration's Oracle Home to /oracle/product/11.2.0.4, the syntax would be the following: emcli modify_target -type='oracle_database' -name='ORCL11.WORLD' -properties='OracleHome:/oracle/product/11.2.0.4' Probably would be similar approach for Listeners, ASM, ...


1

You should look here at Managed Tablespace Alerts, notably this bit where you can monitor by two criteria with warning and critical thresholds. By percent full For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued. By free space remaining (in kilobytes (KB)) For ...


1

The date looks fine: SQL> CREATE TABLE testdate (d date); Table created SQL> INSERT INTO testdate 2 VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd')); 1 row inserted SQL> SELECT DUMP(d), d FROM testdate; DUMP(D) D ...


1

If you don't want a user to create a database link, don't grant them the CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK privileges. My guess is that you've granted the developers a role that you didn't realize had that privilege-- the CONNECT role in at least some versions of Oracle includes CREATE DATABASE LINK. I think that's been removed in more ...


1

Thanks for your help. I changed my query like this and it's working finde. 😊 SELECT DATES.DAT, RET.RETURN_AMOUNT, ORD.ORDER_AMOUNT FROM ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT FROM all_objects WHERE ROWNUM <= ...


1

You could join to the 2 tables separately (and GROUP BY) and then join them together: WITH dates (dat) AS ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat FROM all_objects WHERE ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy') - TO_DATE('02.05.2014','dd.mm.yyyy') + 1 ) , ret ...


1

Strictly speaking, you cannot overflow the redo logs, because they are endlessly reused in a circular manner. When in the ARCHIVELOG mode, however, a log file cannot be reused until it's archived, so there can be a situation where you would have to wait for archiving if you fill redo logs faster than they are archived. In the NOARCHIVELOG mode there is no ...


1

If you want even more automation, then download SQLcl, then you can do fun things like ctas current_table new_table and it generates necessary code for you. e.g., SQL> ctas my_table new_table CREATE TABLE OWNER."NEW_TABLE" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", ...


1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1: create table x.table1 (id number, name varchar2(100), primary key(id)); You want to recreate it as Y.TABLE2, you could do the following: set serveroutput on declare metadata_handle number; ...


1

The error points to a syntax problem. When you want to update multiple columns at once, you list all of the columns to be updated first, followed by all of the values to use (in the same order that the columns are listed). For example: UPDATE CONTACT C SET (C.PHONE, C.FAX) = ( SELECT P.PHONE, P.FAX FROM PERSON P WHERE P.PERSON_ID = C.PERSON_ID ) ...


1

You can use the data dictionary to check whether the column exists before trying to drop it DECLARE l_cnt integer; BEGIN SELECT COUNT(*) INTO l_cnt FROM dba_tab_columns WHERE owner = <<table owner>> AND table_name = 'MY_PROPERTY_LOCK' AND column_name = 'PROP'; IF( l_cnt = 1 ) THEN EXECUTE IMMEDIATE 'ALTER TABLE ...


1

When you install Oracle there are a set of registry entries under hkey_local_machine, software, oracle. If you don't have the registry, and don't want the Oracle home, then you can just delete the directories. There is an Oracle directory under program files, which stores your Oracle Inventory, and your Oracle Home where ever you put that.


1

I haven't done a lot of PS so not sure what the error is really saying but... it looks like you are trying to fill a local table with the output from the select *? If that is true, I am wondering if PS created a temp table with a number/int column type for rownum but the rownum value in row 840 or 841 is something other than a number/int. Or something ...


1

I have a big Index Organized Table (IOT) with 74 columns Why do you think that this table is a candidate for an IOT? IMHO, IOT's are meant to be narrow tables that don't change often. I have done testing with narrow tables and have found IOT's to do inserts at a slower rate than a traditional table with indexes. You may want to start by recreating this ...


1

You should be able to use a method like this - Duplicate from RMAN Backup: http://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2.php#backup_based_duplication Or look at the RMAN docs for "Backup-based duplication without a target connection": http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta020.htm#RCMRF126 They go through ...


1

If you know the sid name, you can copy a pfile over and edit it to change the values to match the database name. Make sure that you set B_CREATE_FILE_DEST to where you want the data files. Then do a startup nomount on the database. In RMAN do "CATALOG START WITH '/full_path_to_your_backup';" You can then use DB_FILE_NAME_CONVERT to convert the file names ...


1

Ok, I didn't realise that creating such an export file consists of two steps. First, an ordinary dump file must be created, then this dump file is used to create an sql file. Since I didn't have such a dump file, the script as posted would not work. Here are the two steps, so that an sql file can be created declare datapump_job number; job_state ...


1

Assuming no duplicates in the 2nd table, it's straightforward. ANSI syntax: select col1, col2, sum(value) from table_1 natural join table_2 group by col1, col2; The "old" way: select t1.col1, t1.col2, sum(value) from table_1 t1, table_2 t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 group by t1.col1, t1.col2; Could have used WHERE EXISTS too, I ...


1

When in doubt ask Tom... backup up and recovery generating extra redo Which is good reason to use RMAN for backups rather than scripted hot backups using begin backup/end backup.


1

In fact I too came here to build a sample DB for scott schema in Oracle_v11g. I couldn't find DBCA as told above, but on further search, I found a file called scott.sql. It had lines to create the needed DB. So I connected to Oracle as system and ran it - all the tables were created. Now I couldn't connect using Scott as Id. Changed the password in System ...


1

Please see the following to understand the issue So even though you're connected with user SYSTEM to the pdb, when you issue connect without specifying the pdb, it'll try to connect to the container and the user is not defined there.



Only top voted, non community-wiki answers of a minimum length are eligible