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 ...


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 STUDENTINFO(Full_Name)...


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

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


4

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


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

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

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

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

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 ...


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

Introduction to the Oracle Database Instance A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database. Every running Oracle database is associated with at ...


2

If you change file's permissions during copy operation, tar will throw "file changes as we read it".


2

Bug 11872813 - V$PGA_TARGET_ADVICE VIEW IS EMPTY. After setting PGA_AGGREGATE_TARGET > approximately 12G the V$PGA_TARGET_ADVICE view is empty. This issue will be fixed in version 12.1 and a fix is expected to be included in patch set 11.2.0.4. Apply Patch 11872813


2

Try creating a temporary table to hold all of the IDs you want to search on then join in that temporary table. create table tmpIds(id int not null) insert into tmpIds (59156) insert into tmpIds (57045) etc... SELECT COUNT(*) FROM "VERSION" INNER JOIN tmpIds ON "VERSION"."VERSION_ID" = tmpIds.id drop table tmpIds


2

The problem is most likely in you not having sufficient privileges to create the index in question -- nothing to do with EXPLAIN PLAN itself. To quote the manual, "To create an index in another schema, you must have the CREATE ANY INDEX system privilege".


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

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;


2

According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan. [oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon But in the description of listener on standby database, SID_NAME is defined as '...


2

Thats is not what you should execute. sma is the procedure, c4 is simply a cursor in it. You do not execute the cursor local to a procedure, you can not even access it. Execute the below: set serveroutput on begin sma; end; /


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

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

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

There is no procedure exist in the database rather we can create one to list out all the un-indexed foreign key constraints. Actually if you google on it you will find plenty of scripts. Well I have the following query to get the result. SQL> select uc.table_name, uc.constraint_name from user_constraints uc where uc.constraint_type='R' and exists (select ...


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 DUMPFILE=select_row.dmp ...


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.



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