Hot answers tagged

8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


6

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


5

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ...


5

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table. Why do you need to duplicate these columns in the child table? EDIT: Locking at you child table you say that: create table bit_2015_sep_cit4114_fyp_G_ ( ... , Full_Name VARCHAR2 (50) NOT NULL REFERENCES ...


4

Based on your description of this behaviour and your other comment, I guess your database runs on Windows. The command you used should suffice and delete the datafiles. Except on Windows. On Windows, the datafiles are not always deleted, it is a known limitation. Before dropping tablespaces or datafiles, take them offline: alter tablespace ... offline; ...


4

First of all, NOT IN is not the same as NOT EXISTS. create table customer (customerid number); create table purchasebill (customerid number, retailoutletid number); create table retailoutlet (retailoutletid number); insert into customer values (1); insert into customer values (2); insert into purchasebill values (1, 1); insert into purchasebill values ...


4

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


4

UPDATE In order to start the instance without interfering with an existing running instance (e.g ORCL), before starting anything: sqlplus / as sysdba create pfile='/tmp/initORCL2.ora' from spfile='?/dbs/spfileORCL.ora'; exit Edit /tmp/initORCL2.ora, and modify (add if doesn't exist) the instance_name: *.instance_name='ORCL2' Prevent listener ...


3

You can simply do it in PL/SQL in a loop: begin for c in (select distinct profile from dba_profiles) loop execute immediate 'alter profile ' || c.profile || ' limit password_life_time unlimited'; end loop; end; /


3

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


3

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


3

All the comments above are appropriate. If you provide the details on the data types and any not null constraints of the columns in question, then we can better determine whether your function-based approach is appropriate. I would also gather statistics on the indexes and look at the unique values, etc. You can compress your baseline index too, if say ...


3

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "Table_Name", a.constraint_name "Constraint_Name", a.columns "Foreign_Key_Column_1", ...


3

You can just easily loop through the partitions in a PL/SQL loop, and add the subpartitions: begin for p in (select partition_name from user_tab_partitions where table_name = 'PART_TEST') loop execute immediate 'alter table part_test modify partition ' || p.partition_name || ' add subpartition ' || p.partition_name || 'AXCS values (''AXCS'')'; ...


3

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details. The source in your query is not providing that kind of information. It is grouping ...


3

As the documentation describes: The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion. LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert ...


3

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table. UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you ...


3

Columns referenced by a foreign key constraint should have a PK or unique constraint in the referenced table. This is not true for Full_Name, Nationality, Fingerprint_Template. To be honest, those FK constraints are unnecessary, remove them, and keep the FK constraint on Student_ID. Also: "23/10/2015" VARCHAR2 (15) not null, This is not a valid column ...


2

The error occurred because you are referencing a column which is not unique. The good answers are already given by Lennart and Balazs Papp. I want to add why we need unique column in the parent table. As you said you want keep duplicate values in the column used for foreign key which is not possible while table creation. But you can create reference to the ...


2

The syntax you used was invalid. This is how you select the first 10 rows in pre-12c versions. select * from ( select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and ...


2

It's not clear what you mean by "extended period of time" or why you associate deleting rows with "taking down" the system. If it's acceptable (as a matter of policy) to delete rows while the system is in production use, provided the deletions don't negatively affect performance, then the trick is to nibble away at the data. Take some unit of the data -- ...


2

The nice clean solution is to: install Oracle 11.2.0.3 on a new machine with the same machine name depending on your network putting the new one a different sub-net or in a fire walled zone could prevent network issues create an instance with the same name set the parameters and table spaces to be the same as the original import the data test, test, test ...


2

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing: alter session set NLS_LENGTH_SEMANTICS='CHAR'; Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener: >export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 >export ORACLE_SID=<YOURINSTANCESID> ...


2

Sure you can, you can duplicate a database using RMAN backups. For example: shut down DB2 delete all datafiles, redo logs, controlfiles of DB2 start DB2 in NOMOUNT with RMAN, connect to DB1 as target, DB2 as auxiliary: rman target sys/password@DB1 auxiliary / duplicate the database until the required time, and rename files on the fly (if required), for ...


2

Can we use subquery in datapump? Yes we can. The following example shows how to do it. I have created a parameter file to get this job done. In my test case I have two tables namely tbl1 and tbl2 under HR schema. You may change the name of the schema as your requirements. [oracle@testsrv Desktop]$ cat query.par Directory=user_dir ...


2

According to SQLfifdle the code you posted appears to be valid for MySQL 5.6 You can use SQLfiddle to check the validity of your code against multiple SQL vendors.


2

Lets see what the Oracle supplied oerr utility says about you errors. [oracle@testsrv Desktop]$ oerr ora 19804 ORA-19804 cannot reclaim %s bytes disk space from %s limit Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit. Action: There are five possible solutions: 1) Take ...


2

First get rowid of the row and object id of the object using DBMS_ROWID. SQL> insert into t3 values('S00102981655537O',sysdate,'IN-RJ'); 1 row created. SQL> select dbms_rowid.rowid_object(rowid) from t3 where doc_id='S00102981655537O'; DBMS_ROWID.ROWID_OBJECT(ROWID) ------------------------------ 93178 SQL> select subobject_name ...


2

you can not do in dynamically like this. partition name has to be known and the time when statement is compiled. But you can use PARTITION FOR clause: UPDATE TABLE T PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') ...


2

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement: create table blah2 ( ctascolumn1 not null, ctascolumn2 null ) as select col1, col2 from blah;



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